Access Query on Time field

[diverdown]

Hopefully a simple question, but I am perplexed. I have an access database that is tracking the time in and time out of vehicles in a parking lot. What I need
to do is figure out how many cars were on the lot between each hourly group(i.e 6 am to 7am, 7am to 8am, etc), I can't seem to figure out the query, and any help would be much appreciated.

Thanks

Rob

 

[AArt]

Hour(Timefield) returns the hour as an integer. So for example in a query you could create a field:
InHour: Hour(Intime)

 

[DougP]

One query won't do it.
But VBA code is your best freind and I'm VBA Codes best freind.

So you need a loop
for a = 1 to [Howmany hours you are open]
' SQL statemtnt with variables

Next

Before you loop you open the record source (table wher data is stored.

Then you get the first hour and the next hour and
pass a SQL steament to the Table
Select * From Table where Time Between TimeIn and timeOut
and in each loop you increment the time up one hour.

Now in this loop you will get the MAX number of records for each hour
Print this out or save it to a temp table.

Get started on this and come back if you need more help.


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[diverdown]

Thanks guys, but I am too new at this.

I figured I would start off defining
t as an integer to equal a)or start time,
t + 1 as an integer to equal end time,
but have no clue on how to open the table
and insert into another table.

I created another table with time in, time out and count,
but am lost.

Thanks for you help

R

 

[diverdown]

Doug,

Please help, I am stuck with this vba stuff. I did a little
SQL select but can't get access to recognize the for a =1 to 24.