VBA Query

[diverdown]

I am not an access programmer or VBA, however I want to put this SQL statement
into a query, and do not know how to set up the VBA part to run with a query.
The initial query looks like this:

Select Date, timein, timeout, count(auto), count(taxi), count(walker), count(public_trans, Hour(timein) as Time
where Time between timein and timeout.

I want to run this for a = 1 to 24 ( number of hours in the day. So, I believe
I would Dim a as Integer. However how do I get this into a query?
Thanks

Rob

 

[tlbroadbent]

I'm struggling to understand your requirement. Your query is incomplete and narrative isn't totally clear for me. It seems like you want an hourly count for each transportation mode. Is that close to what you're asking?

I assume you have data stored in a table that you want to report on. It would be very helpful to know the structure of the table - columns and data types - and to know the format and content of the output you want to see.

Does this query come close to what you want? Or does it help point you in the right direction?

Select Format(date(), "mm/dd&quot As TDate,
Hour(timein) AS THour,
count(auto) As AutoCnt,
count(taxi) As TaxiCnt,
count(walker) As WalkCnt,
count(public_trans) PublCnt,
From tbl
Where format(timein,"mm/dd/yy&quot=format(date(),"mm/dd/yy&quot
Group By Format(date(), "mm/dd&quot, Hour(timein)

The end result should look like this... if run at 9:00PM.
[tt]
TDate THour AutoCnt TaxiCnt WalkCnt PublCnt
7/1 1 5 12 2 22
7/1 2 1 19 0 12
7/1 3 12 22 3 9
7/1 4 6 21 1 17
7/1 5 11 10 6 33
.
.
.
7/1 21 4 9 0 8[/tt]

I've made some major guesses on this one and may be totally off. If I've misinterpreted your question please disregard what I've posted. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[diverdown]

Sorry about the vagueness, but you guessed correctly.
The table format is:

ID Date Auto Walk Pub_trans Taxi Time_in Time_out
1 06/02/01 1 0 0 1 7:00 12:00
2 06/03/01 1 1 1 0 6:30 2:00
I would like the query to report the number of transportation modes
for each hour in the day, that is where I put the (for a = 1 to 24)
statement. The Hour(time_in) as hour field was to convert
the time_in to integer and then use the a=1 to 24 loop to
calculate the data for a full day. I guess I was way off on that.The format of the output you had is exactly what I had in mind. I would appreciate any other help.
Thanks

Rob

 

[diverdown]

Terry,
Thanks for your help. I ended up doing a query for each hour in the day and
then incorporating that into a report, kind of the long way around, but it worked.
And actually, I meant sum, not count,. I would still be interested to know how to put the select statement into VBA with a for loop, that would
select the sum of cars for each hour in the day, then I wouldn't have to do 24 different
queries..

Thanks Again,

Rob