Need some help in MS Access 2000 please!!! **UPDATED WITH SQL QUERY**

[trmiv]

I am designing a database for a project in my database class that is due tuesday. I'm pretty much done, but there are a few things that are driving me crazy about this thing that I can't get to work. Here are my tables right now: Bold is the table name, the fields are under that. * Means it is a key field
-------------
Clients
*ClientID
FirstName
LastName
Address
City
Zip
Phone
------------

------------
dPackages
*PackageID(auto generated)
MessengerID
ClientID
Weight
ShipDate
ArrivalDate
FromAddress
ToAddress
DistanceToDestination
------------

------------
Messengers
*MessengerID
FirstName
LastName
Phone
Address
City
Zip
HourlyRate
-----------

-----------
Payroll
*MessengerID
*DateWorked
HoursWorked
------------

OK, the relationships between the tables look like this

Clients---1:N----Packages-----N:1-----Messengers-----1:N------Payroll


Here is one problem, I need to keep track of the total number of packages each messenger delivers, I also need to keep track of how many packages each client sends.

So, to track how many packages each client sends, I set up a query that gathers info (name, clientID, etc), and in addition does a COUNT operation (in the area where you can also do SUM, AVG, FIRST, LAST, WHERE, etc) on the PackageID from the Packages table to determine the amount of unique packages for each client. Works perfectly.

Then to track how many packages each messenger delivers, I did the same thing (only with the messengers info), also did a COUNT operation on the PackageID from the packages table, problem is, this won't work. In the tables I just entered one package for each messenger to make it easy for now, but the Count says each messenger has two packages associated with them. So to test this out, I went and added a second package for one of the messengers, and the package count for that messenger went to four. Add a third package, it goes to six.

Why?????? Why is is multiplying the package count by two for the messengers? The same thing works perfectly for the clients, but not for the messengers. The weird thing is, if I just run a query to see what packages each messenger has delivered it works, I get all the package info that each messenger has delivered. But, if I ask Access to count how many packages each messenger has delivered, it can't manage that.

Anyone have any ideas? If you need me to explain my database again, let me know. I really need to get this solved.

 

[Accipiter]

Include your query in the post. This problem usually arises from putting the wrong argument in your count function or structuring the where clause improperly.

Edited to add script->
I'm not sure how well this will translate to Access since I don't have that at work but here goes.

select distinct messengerID,count(packageID) from Packages group by messengerID

If you give me your script I be more helpful. Good Luck

 

[trmiv]

I'm at work now and don't have the database with me. I'll post it when I get home, can you look at it then? Thanks for replying.

 

[trmiv]

OK, here is the SQL query. This was generated by Access not by me.

SELECT Messengers.MessengerID, Messengers.LastName, Messengers.FirstName, Sum(Payroll.Hours) AS SumOfHours, Messengers.HourlyRate, Count(dPackages.PackageID) AS CountOfPackageID
FROM (Messengers INNER JOIN dPackages ON Messengers.MessengerID = dPackages.MessengerID) INNER JOIN Payroll ON Messengers.MessengerID = Payroll.MessengerID
GROUP BY Messengers.MessengerID, Messengers.LastName, Messengers.FirstName, Messengers.HourlyRate;


Thanks for any help.

 

[trmiv]

I'm just going to do separate queries for the payroll and the packages. The SQL you gave me worked perfectly. Thanks