Query With Time Values

[EBox]

Hello:

I am trying to set up a query to look at counting number of customers in a department at any time (census). I have:

[Arrive_Time], which is the time the customer arrives in mm/dd/yy hh:mm format
[Archive_Time], which is the time the customer leaves in mm/dd/yy hh:mm format

What I would like to do is create a query that lets me see, by hour of the day (12am, 1am, 2am...11pm) how many customers are in the department. I also need to account for those customers that stay past the date line (i.e. arrive at 11pm and leave at 3am).

Would welcome anyone's help-

Thanks,
EBox

 

[PWise]

This is the way i would do it
Create a numbers table called digits with 1 field called digitid
going from 1 till .........
(my digits table goes to 1,000,0000)
Create A query QryDatehours

Select dateadd("h",digit-1,YourStartDate)Datehours
From Digits

Create this query
Select * from QryDatehours
left join customerarrives
on Datehours between arrives and leaves

 

[EBox]

Thanks. I'm a bit of a rookie, so after I created the digits table, and the first query, which shows each date from my start date, and every hour, on the hour.

I'm not quite sure how to create the second query, however. I can select the field from the first query, but what fields am I joining with what, and what do I put in the results fields?

Again, my arrival date/time field is Arrive_Time, and departure time is Archive_Time, all in a table called "everything".

Thanks.

 

[EBox]

Ok, so I played around with the SQL but I get a Data type mismatch error with this. Any thoughts? Would greatly appreciate it!

Thanks,
Eric

SELECT QryDatehours.*
FROM [everything tester] LEFT JOIN QryDatehours ON [everything tester].Arrive_Time = QryDatehours.Datehours
WHERE (((QryDatehours.Datehours) Between [everything tester].[Arrive_Time] And [everything tester].[Archive_Date]));

 

[PWise]

SELECT QryDatehours.*
FROM QryDatehours
LEFT JOIN [everything tester] on
QryDatehours.Datehours Between [everything tester].[Arrive_Time] And [everything tester].[Archive_Time];

 

[EBox]

I get the following error when I run that:

Between operator without And in query expression 'QryDatehours.Datehours Between [everything tester].[Arrive_Time]'.

Any thoughts?