Results 1 to 7 of 7
  1. #1
    JD9712 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3

    Question Query and Macro to Assign Values based on Time Sequence

    Hello,



    I have a table with the following fields:

    Trip ID (Short Text) - all unique values, for example TRIP1, TRIP2 etc.
    Trip Start Time (Date/Time) - dd/mm/yyyy hh:mm:ss format
    Trip End Time (Date/Time) - dd/mm/yyyy hh:mm:ss format
    Vehicle Type (Short Text) - For example CAR, TRUCK, BUS etc

    For instance:

    TripID Start Time End Time Veh Type
    TRIP1 01/01/2017 3:00:00AM 01/01/2007 6:00:00AM CAR
    TRIP2 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM CAR
    TRIP3 01/01/2017 7:00:00AM 01/01/2007 9:00:00AM CAR
    TRIP4 01/01/2017 2:00:00AM 01/01/2007 9:00:00AM BUS
    TRIP5 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM BUS
    TRIP6 01/01/2017 8:00:00AM 01/01/2007 9:00:00AM BUS
    TRIP7 01/01/2017 2:00:00AM 01/01/2007 3:00:00AM TRUCK
    TRIP8 01/01/2017 4:00:00AM 01/01/2007 5:00:00AM TRUCK
    TRIP9 01/01/2017 6:00:00AM 01/01/2007 7:00:00AM TRUCK

    (Normally I will have many thousands of trips but this serves our purpose here)

    What I need to be able to do, is assign each trip a unique vehicle identifier, based on the start and end times and vehicle type.

    So in the example above:

    TRIP1 could be assigned to "CAR1", TRIP2 begins before TRIP1 ends, so it would be assigned to "CAR2", BUT, because CAR1 has completed its first task by 6:00:00AM it is now available to perform TRIP3 which commences at 7:00:00AM.
    TRIP4 would be assigned to "BUS1", which is unable to perform any other BUS trips, thus TRIP5 would be assigned to "BUS2", which also is unable to perform any other BUS trips, so TRIP6 would be assigned to "BUS3".
    TRIP7 would be assigned to "TRUCK1", which finishes in time for TRIP8 to be performed by "TRUCK1", which finishes in time for TRIP9 to also be performed by "TRUCK1".

    So the final output would be:

    TripID Start Time End Time Veh Type Veh ID
    TRIP1 01/01/2017 3:00:00AM 01/01/2007 6:00:00AM CAR CAR1
    TRIP2 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM CAR CAR2
    TRIP3 01/01/2017 7:00:00AM 01/01/2007 9:00:00AM CAR CAR1
    TRIP4 01/01/2017 2:00:00AM 01/01/2007 9:00:00AM BUS BUS1
    TRIP5 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM BUS BUS2
    TRIP6 01/01/2017 8:00:00AM 01/01/2007 9:00:00AM BUS BUS3
    TRIP7 01/01/2017 2:00:00AM 01/01/2007 3:00:00AM TRUCK TRUCK1
    TRIP8 01/01/2017 4:00:00AM 01/01/2007 5:00:00AM TRUCK TRUCK1
    TRIP9 01/01/2017 6:00:00AM 01/01/2007 7:00:00AM TRUCK TRUCK1

    I have a way of doing this in excel that takes a long, long time, but is nonetheless accurate. I'm hoping there is a way that MS Access can perform this task relatively quickly as I need to devote this time to other things.

    Can anyone please help?

    Thanks so much in advance,
    JD

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I did something similar as part of a pretty involved process that produces a report that presents the data visually (car1 would be in one column, car2 the next, etc). The part you're talking about I did by looping through a recordset of a temp table I dumped the relevant reservations into. It has a field for the assigned number, which I concatenate with the car type for presentation (your Car1).

    Inside that loop is a For/Next loop. Inside that loop I do a DCount() against the temp table, looking for how many reservations clash with the current reservation based on time and the assigned number. If the count is 0, I update that reservation's assigned number with the loop counter (1,2,3). If it's not zero, it moves to the next and rechecks.

    I some additional stuff, like preventing a vehicle from being used more than 10 hours (because a driver shouldn't work over 10).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JD9712 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3
    Thanks very much for that response.

    I have managed to construct the queries I require but I am struggling with looping. I need a process to be repeated until there are no more potential records left, then a new process to be started thereafter. So far I can only see the option to repeat an action x number of times...

    help

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you would use something like


    while somevalue<>0 (or perhaps somevalue>1 or not recordset.eof)

    wend

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's my template code for a recordset loop, which I'd use to loop through the reservations:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
    
      Set db = CurrentDb()
      
      strSQL = "SELECT ..."
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    
      Do While Not rs.EOF
        'your code to work with records here
    
        rs.MoveNext
      Loop
    
    
      rs.Close
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    JD9712 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3
    Ok so I have figured out most of this. Thanks for your help so far guys.

    Basically what I am struggling with is as follows: I have a few macros that run to execute what I need to execute, getting them to loop around correctly is what I can't get to work.

    So i have the following:

    Macro A (standalone set up macro, must run first)

    Macro B - which contains Macro B1, Macro B2, and Macro B3.

    Hard part is, Macro B2 must run until Table B2 contains zero records.

    So what is needed is, Macro B1, then a loop of Macro B2 until Table B2 is empty, then Macro B3, THEN Macro B must start again, and keep looping until Table A is empty.

    Sometimes Macro B2 may take two repeats, sometimes it might take 102 repeats.

    Thoughts?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't use macro's so my only thought is to convert them to VBA

    Code:
    while not tableA.eof
        ... run macroB1 code
        ...while not table2.eof
             ...run macroB2 code
        ...wend
        ...run macroB3 code
    wend

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  2. Macro to run queries in sequence
    By Castillb in forum Macros
    Replies: 1
    Last Post: 06-30-2015, 10:48 AM
  3. Macro code: Automate Select Query Sequence
    By Ace2014 in forum Modules
    Replies: 2
    Last Post: 06-13-2014, 09:21 AM
  4. Add sequence number to time punch table
    By aflamin24 in forum Queries
    Replies: 1
    Last Post: 07-20-2012, 05:43 PM
  5. Replies: 1
    Last Post: 05-07-2012, 08:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums