Unanswered: Grouping and Counting Consecutive Dates
A table exists which describes employee travel
dates. If I can assume consecutive dates always
signify a single trip of any number of days (any
break in the sequence signifies the end of a trip)
how can I get (create table query?) from Table1
to Table2 ???:
Just to try to lend a hand, but why don't you simply create 2 fields, [BeginDate] and [EndDate] That could be done all in one table.
However, to just keep log of trips you can create these two tables as such:
tblPerson is the OneSide and tblTrips is the ManySide. Create your query to mirror your tables. This way you can reflect One person making many trips and can do a count of the Number of trips made, Number of trips made within a certain DateRange etc. Try setting up your tables like this for starters and you will find yourself a happier person later on.
You are right about changing the table layout, and your design
would work very nicely! But unfortunately I am dealing with a
legacy system which (for reasons I won't elaborate on here)
cannot be changed, so basically all I have to work with are
"names" and "dates" WITHOUT any 'start' dates, 'end' dates, or trip ID's
I am thinking there must be a macro or function in ACCESS to
recognize consecutive dates/values and count them as one,
but perhaps I am overly optimistic?
Legacy systems eh? If the rest of the database is designed like that you're in for a torid time.
If you create a query with this sql:
SELECT Name, Sum([NoOfTrips]) AS TotalTrips
FROM (SELECT Table1.Name, fNoOfTrips([name],[travel_dates]) AS NoOfTrips
ORDER BY Table1.Name, Table1.Travel_Dates) AS [TotalTripsSUB]
GROUP BY Name;
and then create a module with this VB:
Function fNoOfTrips(strName As String, datTrip As Variant) As Integer
Static strOldName As String
Static datOldTrip As Variant
If strOldName <> "" And Not IsNull(datOldTrip) Then
If strOldName = strName Then
If datOldTrip + 1 = datTrip Then
fNoOfTrips = 0
fNoOfTrips = 1
fNoOfTrips = 1
fNoOfTrips = 1
strOldName = strName
datOldTrip = datTrip
then you get the right number of trips according to your data.
Also, Access will play around with the SQL and make it invalid (or my version did when I closed and opened again). If so, split it into two separate queries.
The function doesn't have any handlers for null values so you'll need to put them in if the table contains nulls. Also, it all hangs on the sub query ordering by name then date, both ascending, should you decide to play around with it.
Thank you "Pootle Flump" for the function and SQL.
It works very nicely, I was at an impasse and my project
(legacy notwithstanding) can now continue!
I did have to copy the original table through a sort to a new
(sorted) table, and as you pointed out it all hangs on the
subquery doing it's sort. Perhaps in my version of Access
(2000 9.0.2720) running on Windows 98 (2nd ed. 4.10.2222 A)
TotalTripsSUB retrieves records first, then sorts them, at least
my results on an unsorted table reflect that.
In any case, you got me over the hump. Sorting first is no problem.
Thank you very much for taking the time. If there is a place to leave feedback for your feedback, let me know (I'm rather new to dbforums,
not sure of etiquette et...)
I am having the same scenerio where I have to count event occuring on consecutive dates as 1 instance
my table is
1 |Delhi |2015-01-05|50
2 |Delhi |2015-01-06|30
3 |Mumbai |2015-01-08|20
row 1 and 2 will be counted as 1 instance and 2 events on the basis of location
total instance 3
total events 4
total no_of_app 140