Stumped on a Count Query

Indigo

Registered User.
Local time
Today, 04:23
Joined
Nov 12, 2008
Messages
241
I am using Access 2010 and trying to create a query to count records by hour. The fields in the table include:

EnterDate, EnterTime, ExitTime, Type

I need to count the number of vehicles that enter and exit per hour on a given day and show by type.

My SQL is as follows:

Code:
SELECT VehicleLogDay.EnterDate, Hour([EnterTime]) AS InHour, Count(VehicleLogDay.EnterTime) AS TotalInPerHour, Hour([ExitTime]) AS OutHour, Count(VehicleLogDay.ExitTime) AS TotalOutPerHour, VehicleLogDay.Type
FROM VehicleLogDay
GROUP BY VehicleLogDay.EnterDate, Hour([EnterTime]), Hour([ExitTime]), VehicleLogDay.Type
HAVING (((VehicleLogDay.EnterDate)=[Forms]![frmMain]![DaysDate]))
ORDER BY Hour([EnterTime]), Hour([ExitTime]);

But I am not getting the results I want. Can anyone steer me in the right direction? Thank you!
 
Apologies, should have been more specific.

What I get is:

Date | hour in | total hour in | hour out | total hour out | Type
3-15 | 9 | 1 | 10 | 1 | Commercial
3-15 | 10 | 7 | 10 | 7 | Commercial
3-15 | 10 | 2 | 10 | 2 | Contractor
3-15 | 10 | 7 | 11 | 7 | Commercial
3-15 | 10 | 1 | 12 | 1 | Commercial
3-15 | 10 | 1 | 12 | 1 | Contractor

What I want is:

Date | hour in | total hour in | hour out | total hour out | Type
3-15 | 9 | 1 | 9 | | Commercial
3-15 | 10 | 3 | 10 | 2 | Contractor
3-15 | 10 | 17 | 10 | 9 | Commercial
3-15 | 11 | 25 | 11 | 18 | Contractor


Wondering if I should query all in and all out separately and then do a union or if a crosstab will work....?
 
There's no way to produce the expected results from the actual results you are getting. Forget totals, you just don't have the data to generate the expected results.

How do you expect to produce the last record ([hour in]=11)? There's no [hour in]=11 in the underlying data.
 
yeah, I didn't think so... I am trying to convince my user to use a report (essentially two subreports side by side) to get the desired results.
 
No, this has nothing to do with SQL or Access or technology at all. I'm talking logically. Your expected results are not possible within the realm of reality.

You expect a result with [hour in]=11, however, you have no underlying data with that value. You can't have an orchard full of apple trees and expect a baker to make a cherry pie.

If you can provide sample starting data and expected results, I am sure we can get to where you want to go. But your data so far is not logically consistent.
 
Plog, quite simply, I have time fields in my table, and in my query on my first post, I indicated "Hour([EnterTime]) as InHour which giives me the hour value = 11 or 10 or 9 as the vehicles are logged in. I'm not trying to make a cherry pie.

In a nutshell, the database is tracking vehicles entering and exiting through a security gate. Users log in vehicles entering and the form has a timer on it to " stamp" the time of day that commercial, contractor or company vehicles enter the facility. The form has a data sheet subform where the users can see all the vehicles that have entered and when they leave they can update the record with the date and time of departure.

I was asked to create a query that shows, by hour how many vehicles by type enter and exit. I can do this in two separate crosstab queries - one for vehicles in and the other for out, but I was hoping to be able to do it in one query.
 
First, you should store Date/Time values in one field--it's called a Date/Time field for a reason. That means EnterDate & EnterTime shouldn't both exist, just one which hold both pieces of data.

Second, if you can provide legitimate sample data, I can help you write a query. I would need 2 sets of data:

A. Starting sample data from relevant tables. Include table/field names and enough sample data to cover all cases.

B. Expected results based on A. Show me what data should be returned when you feed your query data from A.
 

Users who are viewing this thread

Back
Top Bottom