Hourly Totals query

[lagg]

Hi guys I hope someone can provide me with some guidance to this query I need to generate. I want to be able to generate an hourly total sales report from two database tables.

On Table has the hourly bands
uniqueid Start hour End Hour
1 00.00 01.00
2 01.00 02.00
etc up to 24.

The second table has line by line sales information/ data entry in the form of

Date Time Trans Total Type
9/3/07 15.02 5887 26.00 cash
9/3/07 15.04 5888 22.00 cash
9/3/07 16.20 5889 24.00 cash

I need to write a query that will return an hour by hour sales total like below, combining information from both tables. It should look like:

Hours Total
15.00 - 16.00 48.00
16.00 - 17.00 24.00

I hope the information here is enough for someone to help me out. It would be much appreciated. Any further clarifications I can respond to also

 

[dinger2121]

you could create a calculated column that uses left(time,2) to get the hour from the time column. Then you can just group by that and sum your total column.

 

[lagg]

Im not sure what you mean by "left(time,2)" is this a join? could you explain in a little more detail

 

[PWise]

no need for two table try

SELECT Format(Int([Time]]),"00.00") & "-" & Format(Int([Time]+1),"00.00") AS Expr2, Sum(Sales.amount) AS SumOfamount1
FROM sales 
GROUP BY Format(Int([time]),"00.00") & "-" & Format(Int([time]+1),"00.00");