query summing error

[David B]

I have a query that totals the time usage of instrument users. It feeds a
report for billing purposes. It's been working fine for over 2 years, but
gave two odd results yesterday.

One user used 15 hours. The table contains one record with his billing
information, and 15 in the Time field. When the query is run, it shows a
total of 30 hours. I've reentered the 15 in the field, reentered the whole
record, and checked the query. Nothing changes this behavior.

Another user has several records in the file, with 1 or 2 hours in each Time
field, for a total of 91 hours. The query returns a total of 101 hours.

The results are correct for the other 30 or so users in the table (422
records).

The query is:
SELECT DISTINCTROW DLookUp("Institution","NameLookupCOS","Advisor = '" &
[NameLookupCOS.Advisor] & "'") AS Institution, logCurrent.Advisor,
logCurrent.[Project Number], logCurrent.[Document ID], Sum(logCurrent.Time)
AS SumOfTime, Sum([Time]*(DLookUp("Rate","ratesCOS","COS = '" &
[ratesCOS.COS] & "'"))) AS Charge
FROM logCurrent INNER JOIN (NameLookupCOS INNER JOIN ratesCOS ON
NameLookupCOS.COS = ratesCOS.COS) ON logCurrent.Advisor =
NameLookupCOS.Advisor
GROUP BY DLookUp("Institution","NameLookupCOS","Advisor = '" &
[NameLookupCOS.Advisor] & "'"), logCurrent.Advisor, logCurrent.[Project
Number], logCurrent.[Document ID];

logCurrent contains the user information and the time for each analysis.
fields used by query are Advisor/Project Number/Document ID/Time
(Advisor is the user)

NameLookupCOS contains the users' names and their institution.
fields are Advisor/Institution/COS
There are three rate classes, and COS is Y, N, or X, depending on the
Institution.

ratesCOS contains the rates charged for each institution.
fields are COS/Rate

Advisor links logCurrent and NameLookupCOS, and COS links NameLookupCOS and
ratesCOS. I think this is probably a kludgey setup, but it's the only way I
got things to work as they should.

Thanks for any help.

 

[KARL DEWEY]

You just might have multiple Rate entries or some other join to give the
Cartesian output.

 

[David B]

Yep, I found two different entries in the rate table. Thanks.

David

You just might have multiple Rate entries or some other join to give the
Cartesian output.

--
KARL DEWEY
Build a little - Test a little

I have a query that totals the time usage of instrument users. It feeds a
report for billing purposes. It's been working fine for over 2 years, but
gave two odd results yesterday.

One user used 15 hours. The table contains one record with his billing
information, and 15 in the Time field. When the query is run, it shows a
total of 30 hours. I've reentered the 15 in the field, reentered the whole
record, and checked the query. Nothing changes this behavior.

Another user has several records in the file, with 1 or 2 hours in each Time
field, for a total of 91 hours. The query returns a total of 101 hours.

The results are correct for the other 30 or so users in the table (422
records).

The query is:
SELECT DISTINCTROW DLookUp("Institution","NameLookupCOS","Advisor = '" &
[NameLookupCOS.Advisor] & "'") AS Institution, logCurrent.Advisor,
logCurrent.[Project Number], logCurrent.[Document ID], Sum(logCurrent.Time)
AS SumOfTime, Sum([Time]*(DLookUp("Rate","ratesCOS","COS = '" &
[ratesCOS.COS] & "'"))) AS Charge
FROM logCurrent INNER JOIN (NameLookupCOS INNER JOIN ratesCOS ON
NameLookupCOS.COS = ratesCOS.COS) ON logCurrent.Advisor =
NameLookupCOS.Advisor
GROUP BY DLookUp("Institution","NameLookupCOS","Advisor = '" &
[NameLookupCOS.Advisor] & "'"), logCurrent.Advisor, logCurrent.[Project
Number], logCurrent.[Document ID];

logCurrent contains the user information and the time for each analysis.
fields used by query are Advisor/Project Number/Document ID/Time
(Advisor is the user)

NameLookupCOS contains the users' names and their institution.
fields are Advisor/Institution/COS
There are three rate classes, and COS is Y, N, or X, depending on the
Institution.

ratesCOS contains the rates charged for each institution.
fields are COS/Rate

Advisor links logCurrent and NameLookupCOS, and COS links NameLookupCOS and
ratesCOS. I think this is probably a kludgey setup, but it's the only way I
got things to work as they should.

Thanks for any help.