Counting Unique Records Within 24 hours

[missjen]

Q: There a PRODUCT table with a primary PRODUCT_ID key. There is a TRANSACTION table that records PRODUCT purchases. The TRANSACTION table contains duplicate PRODUCT_ID fields and and a TIMESTAMP date field that records the date of the transaction. How can I construct a query/expression to count how many of each PRODUCT_IDs were "transacted" over a 24 hour period?

 

[MichaelRed]

Sounds like a good canidate for a crosstab query on the joined tables. Use a a parameter for the date, with the 'short date' format of timestamp field.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[missjen]

Michael,

Thanks for you're feedback. However, I think I structured my question incorrectly. I only want to find ONE (the first occurence) transaction per PRODUCT_ID (1 unique ID) per 24 hour period (from 12:00 a.m. to 11:59 p.m), and exclude other instances of PRODUCT_ID transactions during this same time frame. It's actually a billing query. We charge one standard fee per day for unlimited transactions for each PRODUCT_ID. I need to find distinct PRODUCT_ID transactions that are not repeated during the same day.

A co-worker of mine stated that the standard SQL might look like something like this:

select distinct PRODUCT_ID,to_char(TIMESTAMP,'MM/DD/YYYY') from TRANSACTION

However, I'm not sure how this translates into Access SQL.

Thanks again for your response.

missjen

 

[MichaelRed]

My Table

EmpId StatTime StatCode StatDesc
103002 11/29/00 1:06:50 PM 2 OUT FOR BUSINESS
103002 11/29/00 1:08:34 PM 1 IN
356461 11/28/00 4:06:22 PM 1 IN
356461 11/28/00 4:13:46 PM 4 OUT FOR THE DAY
356461 11/29/00 12:58:25 PM 0
830000 11/28/00 4:01:32 PM 2 OUT FOR BUSINESS
830000 11/28/00 4:01:37 PM 4 OUT FOR THE DAY
830000 11/29/00 1:10:04 PM 4 OUT FOR THE DAY
873001 11/28/00 4:00:02 PM 1 IN
873009 11/28/00 4:01:20 PM 4 OUT FOR THE DAY
873102 11/28/00 4:02:19 PM 0 OUT FOR LUNCH
873220 11/28/00 4:01:55 PM 3 OUT FOR PERSONAL

MyQuery
SELECT DISTINCT tblEmpTime.EmpId
FROM tblEmpTime
WHERE (((tblEmpTime.StatTime) Between [StartDate] And DateAdd("d",1,[StartDate])));

Note: Based on the existing table, I used the data of 11/29/00 for the input [StartDate]. This was, of course, done with manual input of the start date, however programatic variations would generally be prefered.

My Results
EmpId
103002
356461
830000

Of course, if you need additional information, you would need to use the query results as the basis for another lookup.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over