sales between hour1 and hour2

  • Thread starter Thread starter fabrice
  • Start date Start date
F

fabrice

Hi,

i have a query where i get this information
Time/ product /quantity
7:01 product X 3

now i would like to make a query to see how many items i sell of product X
between 7 and 8, 8 and 9, ....
even better would be a list of products that have been sold from hour to
hour
 
Assuming the Time column is a date/time data type:

SELECT Format([Time],"hh") AS Hour, Product, SUM(Quantity) AS TotalSold
FROM Sales
GROUP BY Format([Time],"hh"), Product;

That would give you the sales per product per hour on all days. If you want
to distinguish between different days, however, you would be better entering
both the date and time in the Time column. If you enter just a time into a
date/time column you are actually entering a time on 30 December 1899, which
is day zero in Access. With both the date and time however you can either do
as above or you can aggregate the sales for each day:

SELECT Format([Time],"yyyy/mm/dd") AS Date, Format([Time],"hh") AS Hour,
Product, SUM(Quantity) AS TotalSold
FROM Sales
GROUP BY Format([Time],"yyyy/mm/dd"), Format([Time],"hh"), Product;

BTW I'd advise against using Time as a column name. It could be confused
with the built in Time function. Better to use something like SaleTime.

Ken Sheridan
Stafford, England
 
Back
Top