Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16

    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 ???:

    Table1:

    Name Travel_Dates
    ----- -----------
    John 1-1-04
    John 1-2-04
    John 1-3-04
    John 1-9-04
    John 1-10-04
    Mike 1-2-04
    Mike 1-8-04
    Jeff 1-9-04
    Jeff 1-10-04

    Table2:

    Name Number_of_trips
    ----- ----------------
    John 2
    Mike 2
    Jeff 1

    Thanks ahead of time.

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi richsparker,

    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:
    -------------------------------------------------
    Code:
    tblPersons            tblTrips
        PersonID                         TripID
        FirstName                        PersonID
        LastName                        StartDate
                                             EndDate
                                             Destination
    -------------------------------------------------

    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.

    need more help later let us know
    Bud
    Last edited by Bud; 05-07-04 at 07:55.

  3. #3
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16
    Bud,

    Thanks for the input...

    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?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    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
    FROM Table1
    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
    Else
    fNoOfTrips = 1
    End If
    Else
    fNoOfTrips = 1
    End If
    Else
    fNoOfTrips = 1
    End If

    strOldName = strName
    datOldTrip = datTrip

    End Function

    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.

    Good luck with your system!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16

    Thanks, works with one small mod (my mistake?)

    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...)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    I'm fairly new too so no idea of ettiquette but I always like to know whether or not it worked and we're none of us immune to praise! Also I realised that the SQL can be simplified:

    SELECT Table1.Name, fNoOfTrips([name],[travel_dates]) AS NoOfTrips
    FROM Table1
    WHERE fNoOfTrips([name],[travel_dates]) = 1
    ORDER BY Table1.Name, Table1.Travel_Dates

    I haven't tried this (just typed it into the browser) but it should work too, and is simplier. Curious thing having to sort your table... The above SQL might change this perhaps.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2015
    Posts
    1

    grouping and counting consecutive dates

    I am having the same scenerio where I have to count event occuring on consecutive dates as 1 instance
    my table is
    event_id|Location|Date|no_of_app
    1 |Delhi |2015-01-05|50
    2 |Delhi |2015-01-06|30
    3 |Mumbai |2015-01-08|20
    4 |Chennai|2015-01-06|40
    row 1 and 2 will be counted as 1 instance and 2 events on the basis of location

    required result:
    total instance 3
    total events 4
    total no_of_app 140


    Please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •