Query to count Times within a selection

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Howdy,

Bit stuck and would appreciate a pointer, I have a query
which collects information about train Luggage

Train ID Leave Time Bag To Train
Glas112 15:00 14:55
Glas112 15:00 14:20
Glas112 15:00 15:05

What I need is to get the results returned in a format
similar to below but needs to be in 15min time slots i.e
Leave Time < 0-15Mins , Leave Time <16-30Mins:

Train ID Bags Before Leave Bags After Leave
Glass112 2 1
 
Hi,


TRANSFORM COUNT(*)
SELECT TrainID
FROM myTable
GROUP BY TrainID
PIVOT Int( (LeaveTime-BagToTrain)/ #00:15:00# )


The fields would be -2, -1, 0, 1, 2 ... rather than the labels you proposed.
You can get those labels with an inner join involving a "translation"

Translations ' table name
Number Word
0 "Leave"
1 "15min-30min after leave"
-1 "15min before leave"
....



then, something like

TRANSFORM COUNT(*)
SELECT TrainID
FROM myTable INNER JOIN translations
ON Int( (myTable.LeaveTime-myTable.BagToTrain)/ #00:15:00# ) =
translations.Number
GROUP BY TrainID
PIVOT translations.Word



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top