Getting Sums on Records

[xhat]

Working in Access 2000.

A table records, among other things, the date a transaction was entered, and two currency fields. The goal is to Sum the two currency fields based on their common dates. So, for example, let's say I had three entries all occurring on 2/1/10:

Example A
1) field1: $5.00, field2: $15.00
2) field1: $10.00, field2: $0.00
3) field1: $5.00, field2: $10.00

I have written a query that will Sum field1 and field2 based on examination of the date the transactions were entered. I use... DateDiff('d',[session_date],Date()) and set the where condition to zero. So if today were 2/1/10, the output of the query would be as follows:

Example B
field1: $20.00 / field2: $25.00

All well and good, however, I'd like this query to work for every date in the table, not just today's date. If I simply run the query without a Where condition and Sum the two fields and set the session_date field to GroupBy, I don't get a sum of the sessions for a given date, but instead get the individual records for every date. In other words, if I ran the query today (2/18/2010) as I just described, the output I would get for the transactions dated 2/1/10 would look exactly like Example A, and not Example B, which is the goal.

This seems like it ought to be an easy issue to resolve (I haven't yet gone to VB coding to address the issue) using existing Access functions, but I'm stumped. Any ideas....?


"There is no spoon..." - anonymous enlightened child

 

[SkipVought]

GROUP By your DATE field.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xhat]

Skip,

Yeah, like I said in the body of the post, I've already done that and the results are not coming out as expected.

"There is no spoon..." - anonymous enlightened child

 

[smedvid]

Do you have more specific details of what your table looks like? As SkipVought stated, this sounds like a Group By on the Date field.

Sounds like your table is as follows:

Date_Field Field1 Field2
02/01/2010 $5.00 $15.00
02/01/2010 $10.00 $0.00
02/01/2010 $5.00 $10.00

So, SQL Should be as follows:
<code>
SELECT Table1.Date_Field, Sum(Table1.Field1) AS SumOfField1, Sum(Table1.Field2) AS SumOfField2
FROM Table1
GROUP BY Table1.Date_Field;
</code>



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[xhat]

smedvid,

Yes, that is precisely how the table looks (in addition to other fields that aren't being queried in this instance). And, like yourself and Skip both stated, I have run the query using GroupBy on the Date field. The output does not properly sum as it should. I still get multiple records for the same date.

Here is the SQL, that produces the result I want:

SELECT Sum(sessions.at_risk) AS SumOfat_risk, Sum(sessions.at_risk_return) AS SumOfat_risk_return, Sum(sessions.buy_in) AS SumOfbuy_in, Sum(sessions.tournament_fee) AS SumOftournament_fee, Sum(sessions.satellite_return) AS SumOfsatellite_return
FROM sessions
WHERE (((DateDiff('d',[session_date],Date()))=0));

And here is the SQL that produces the "wrong" output:

SELECT sessions.session_date, Sum(sessions.at_risk) AS SumOfat_risk, Sum(sessions.at_risk_return) AS SumOfat_risk_return, Sum(sessions.buy_in) AS SumOfbuy_in, Sum(sessions.tournament_fee) AS SumOftournament_fee, Sum(sessions.satellite_return) AS SumOfsatellite_return
FROM sessions
GROUP BY sessions.session_date;

As you can see, nothing has changed except for the date criteria.

"There is no spoon..." - anonymous enlightened child

 

[PHV]

What about this ?
SELECT CLng(session_date), Sum(at_risk) AS SumOfat_risk, Sum(at_risk_return) AS SumOfat_risk_return, Sum(buy_in) AS SumOfbuy_in, Sum(tournament_fee) AS SumOftournament_fee, Sum(satellite_return) AS SumOfsatellite_return
FROM sessions
GROUP BY CLng(session_date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smedvid]

Does sessions.session_date field also have a time stamp in the value? If so, perhaps truncate that information. Otherwise, I am at a loss as to why your simple query is not returning the expected results.

Could the DB be corrupt? Did you compact and repair the database lately...

As strange as these seems... some other thoughts.

Can this issue be reproduced on another PC with MS Access?

Do you have multiple version of MS Access installed? There is a specific order to install different versions.

Is the MDB on a network drive? If so, copy the file to local desktop to test out...

Perhaps change fields from Currency to Double... see if that has any impact?

htwh,


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[xhat]

PHV and smedvid,

It is a date formatting issue. CLng() produced some results that "appeared" correct from the standpoint that it was not returning more than one record with the same date, but the summing is incorrect. The issue, I'm suspecting now, is that the form where the values are entered uses the Now() function to populate the date field. I had set the format of this field to short date, but it appears as though using the Now() func is storing more than just a short date. I'll need to find a formatting scheme that works to treat all of the dates the same. Thanks both for the input/suggestions.


"There is no spoon..." - anonymous enlightened child

 

[PHV]

but the summing is incorrect
Any chance you could elaborate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xhat]

PHV,

I used CLng() on the dates, but because the dates had time stamps in them, the conversion of the dates to longs was generating different values for the same dates. Thus, the summing was coming out incorrectly. In other words, 3 of the 4 1/24/2010 dates, for example, were being converted to the same long number, but then the 4th would be converted to a different long. I solved the issue by going back and fixing the dates in the database to just be dates, and replaced the Now() func that was automatically populating the date field with the Date() func.

"There is no spoon..." - anonymous enlightened child