Merge Date

[AccessNewbie2007]

Hi all,

I have a table:
date | in | out | whouse | wblock


I have a simple query with a criteria for wblock that returns all the data sorted by date, I am trying to set a criteria w whouse to sum up the in and out based on date but it is not working

example:

01/01/2010 | 12 | 15 | w1 | wb1
01/01/2010 | 12 | 15 | w1 | wb2
02/01/2010 | 15 | 20 | w1 | wb1
02/01/2010 | 15 | 20 | w1 | wb2

I need to be able to set criteria = w1 and be able to output

01/01/2010 | 24 | 30
02/01/2010 | 30 | 40

Thanks in advance for the kind help

 

[DCrake]

You need a totals query

Group by date
Sum In
Sum Out
Where wHouse = w1

 

[AccessNewbie2007]

that's what I did but it is not merging the date

 

[namliam]

Then your date is a date/time not a date... Try using Int(yourdate) or Format(yourdate, "YYYY-MM-DD") to cut off the time

alternatively you left "group by" on fields you shouldnt have like WBlock for example

 

[ColinEssex]

Also, don't call the field "date" as it is a reserved word and Access will get confused.

Col

 

[AccessNewbie2007]

thanks, it was called 'date' so I replace it and I used Format(yourdate, "YYYY-MM-DD") and it is working fine thanks