Following is the part of data, in Access:
icode soldQty rackQty dt
---------------------------------------
14000 10 50 03/17/22 20:35
15000 1 45 03/17/22 15:35
16000 3 55 03/17/22 08:22
14000 30 48 03/18/22 14:05
15000 18 62 03/17/22 13:35
16000 3 47 03/17/22 15:23
14000 1 49 03/19/22 16:35
17000 1 49 03/17/22 15:13
14000 24 26 03/17/22 10:35
15000 10 33 03/17/22 20:37
There are more than 100 icodes, I am putting just 3 as an example. I want to generate a Weekly (or between some dates) report based on a certain time period. Say, a weekly report between 10:00 to 17:00 for 3 items, 14000, 15000 and 16000. The output, I am expecting is:
icode soldQty rackQty
14000 54 74
15000 19 107
16000 4 96
Using this query, I am able to get the sum.
select icode, sum(soldQty), sum(rackQty) from sales
group by icode having icode between 14000 and 16000
order by icode
I am confused where to put the where clause so that I can have a condition for the
timing (10:00 to 17:00) constraint?
Try this:
select icode, sum(soldQty), sum(rackQty)
from sales
where TimeValue(dt) between #10:00:00# and #17:00:00#
group by icode
having icode between 14000 and 16000
order by icode