What query to use???

[millrat]

Hi,
What type of query would I use to get the following result and how?

Eg table.

[Date] [Total]
01/02/06 100
01/02/06 75
02/02/06 100
02/02/06 50

Result needed

[Date] [SumOfTotal]
01/02/06 175
02/02/06 150

I've tried all I know but still cannot get it.
Help would be appreciated
Thanks
millrat

 

[IT4EVR]

Select [Date],Sum([Date])
FROM Table
GROUP BY [Date]

the key is the group by statement

 

[IT4EVR]

oops, do this

Select [Date],Sum([Total]) as [SumOfTotal]
FROM Table
GROUP BY [Date]

 

[millrat]

Hi IT4EVR,
Thanks for the reply.
I did as you suggested but still get many records for the same date. Basically I need to sum the [total] for each record where the [date] is the same. eg on a given day there may be 15 records for [total] (same date)
Cheers,
millrat

 

[IT4EVR]

is your date field in short date format? or long date format?

if it is in short date format, then my second post should work for you

 

[millrat]

Hi,

Yes in short date format

 

[millrat]

We could be onto something here. Even though in short date format, the query is giving a date and time..the times are different, this may explain it. I try to sort out the formatting and let you know
Cheers

 

[IT4EVR]

Sounds like you have somehow configured a default value of now() or a general date format.

To coerce a short date format do this:

SELECT Format(Table1.Date,"Short Date"), Sum(Table1.total) AS SumOftotal
FROM Table1
GROUP BY  Format(Table1.Date,"Short Date");

 

[millrat]

Problem solved, Thanks for the help