doubled values

[jozino01]

hi,
i have the following query:
SELECT REVENUE.DATE, REVENUE.ACCT_NUM, REVENUE.INV_NUM, Sum(REVENUE.M3) AS LOGS_VOLUME,
Sum(REVENUE.[B/S_M3]) AS BS_VOLUME,
Sum([REVENUE].[M3]+[REVENUE].[B/S_M3]) AS TOT_VOLUME,
Sum([REVENUE].[CAD]+[REVENUE].[B/S_VALUE]+[REVENUE].[B/G_VALUE]+[REVENUE].[LOGS_SCALING]+[REVENUE].[B/S_SCALING]) AS TOT_VALUE,
(Sum(([REVENUE].[M3]+[revenue].[b/s_m3])*([REVENUE].[RATE]))) AS TOT_COMMISSION
FROM REVENUE, CUR_YEAR
WHERE (((REVENUE.DATE)>=[CUR_YEAR].[DATE]))
GROUP BY REVENUE.DATE, REVENUE.ACCT_NUM, REVENUE.INV_NUM;

it gives me wrong, doubled, values for calculated fields
DATE ACCT_NUM INV_NUM LOGS_VOLUME BS_VOLUME TOT_VOLUME TOT_VALUE TOT_COMMISSION
7/07/05 02 F05-036 317.524 0 317.524 $12,939.10 635.048
5/17/05 02 F05-018 1819.2 0 1819.2 $274,244.40 3638.4
5/17/05 02 F05-018A 0 82.246 82.246 $8,559.74 164.492
5/25/05 02 F05-021 104.4 0 104.4 $2,792.70 208.8

i don't see any logical error or error in syntax, any idea please?

 

[lynchg]

Run your query without the Group By and Sums, see if you are getting duplicate records. If so, that could explain why all of your sums are double what you expect.

 

[jozino01]

I found out that if I replace
WHERE (((REVENUE.DATE)>=[CUR_YEAR].[DATE]))
with
WHERE (Right([date],2)=05)
it gives me correct values, but i am not sure why...

value [CUR_YEAR].[DATE] is 01/01/05

 

[PHV]

Is REVENUE.DATE a real DateTime field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

yes, it's DateTime field.

making "the mystery" even bigger is the fact that everythink worked for over 3 years. when i printed that report two weeks ago it was ok. i am not aware of any changes in ms access nor in computer setting (like change in date format, new office updates, etc.; it really puzzles me...

 

[PHV]

Have you tried a Repair/compact of the DB ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

yes, i ran Repair/Compact