Results 1 to 7 of 7
  1. #1
    SandcastleDad is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Location
    NJ
    Posts
    3

    Question Count Record Details for Multi Lines by Date and Time

    Greetings all - FIRST POST! Thank you for being here!

    I have a data set with over 300K records of order information. There are many orders but multiple lines of date for each order. I need to add a sequence number by [Order Number] in chronological order by [Date] and [Time] field.

    example of current data:

    Order Number Date Time
    3242553 7/10/2018 4:32:13 PM
    3242553 7/10/2018 4:34:03 PM
    3242553 7/10/2018 6:08:26 PM
    3242553 7/11/2018 8:10:26 AM
    3242536 7/12/2018 4:35:13 PM
    3242536 7/12/2018 5:32:19 PM
    3242536 7/12/2018 6:45:10 PM

    I need to add a sequence number by [Order Number] in chronological order by [Date] and [Time] field to look like this:

    Order Number Date Time Seq
    3242553 7/10/2018 4:32:13 PM 1
    3242553 7/10/2018 4:34:03 PM 2
    3242553 7/10/2018 6:08:26 PM 3
    3242553 7/11/2018 8:10:26 AM 4
    3242536 7/12/2018 4:35:13 PM 1
    3242536 7/12/2018 5:32:19 PM 2
    3242536 7/12/2018 6:45:10 PM 3

    I suspect this is not hard but I need help.



    it's always something,
    Mike D.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Are you going to store this ? I'd advise against it as if a record is deleted for any reason your sequence goes all pear shaped.

    If it's just for reporting purposes (a order line number) you can easily make it appear on a report or a form as a calculated report control.
    See here for an example of how http://allenbrowne.com/casu-10.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As Minty asks, what is the purpose since to store the value will almost certainly lead to problems down the line.

    the way to do it is a sub or aliased query - to apply to 300k rows will be very slow but if you only need to apply when looking at one order it will be fast. How many people want to look at 300k records before trying to find the one they want?

    Also is it possible you will have records with same order/same date? If so, then without some other differentiator both records will be assigned the same sequence number.

  4. #4
    SandcastleDad is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Location
    NJ
    Posts
    3
    Quote Originally Posted by Minty View Post
    Are you going to store this ? I'd advise against it as if a record is deleted for any reason your sequence goes all pear shaped.

    If it's just for reporting purposes (a order line number) you can easily make it appear on a report or a form as a calculated report control.
    See here for an example of how http://allenbrowne.com/casu-10.html
    There are many other fields in the data and the reason for the multi [Order Number] is that in another field there is a [Status] that becomes important.
    Basically when the picture is complete this data is an audit trail for each [Order Number]. As in any audit trail, one thing happens before another and adding this sequence number to the data makes is easy to select the components in a needed order.

    Yes, I am storing the sequence in the data, and yes, there are times when multi things are generated at the same [Date] and [Time] but other fields like [Record Type] and [Status] will be included in subsequent queries to help as needed.

    I was thinking that an update query could be used to create the [Seq] by order date and time but I don't know how.

    I hope that helps explain a little further.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Yes, I am storing the sequence in the data, and yes, there are times when multi things are generated at the same [Date] and [Time] but other fields like [Record Type] and [Status] will be included in subsequent queries to help as needed.
    so you are saying it doesn't matter if two records have the same sequence number?

    if you are using an update query you probably need to use the dcount function which will be even slower.

    As in any audit trail, one thing happens before another and adding this sequence number to the data makes is easy to select the components in a needed order.
    Since the order is dictated by the date/time, what is the benefit of a sequence number when you already have date/time?

    It is not clear from your example data - is date and time one field or two?

    assuming it is one field and you don't mind duplicate sequence numbers then you can try something like this
    Code:
    UPDATE myTable
    SET SeqNo=dcount("*","myTable","OrderNo=" & [OrderNo] & " AND DateTime<=#" & Format(DateTime,"mm/dd/yyyy") & "#")
    Be prepared - it could take some time.

  6. #6
    SandcastleDad is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2018
    Location
    NJ
    Posts
    3
    Quote Originally Posted by Ajax View Post
    so you are saying it doesn't matter if two records have the same sequence number?

    if you are using an update query you probably need to use the dcount function which will be even slower.

    Since the order is dictated by the date/time, what is the benefit of a sequence number when you already have date/time?

    It is not clear from your example data - is date and time one field or two?
    Correct, it does not matter if two records have the same number.

    Unfortunately, [Date] and [time] are 2 different fields. I guess I can combine into one if it is better?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    yes better to combine - but since they are numbers you can just add them together

    [Date]+[Time]

    Note if these are your real field names be aware they are reserved words and if used you run the risk of inexplicable errors and in the worst case, corruption. See these links
    http://www.databasedev.co.uk/corrupt-database.html
    https://support.office.com/en-us/art...7-da237c63eabe

    also see the second link of post #14 of this thread
    https://www.access-programmers.co.uk...ght=corruption

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

Similar Threads

  1. Replies: 7
    Last Post: 04-16-2018, 07:43 PM
  2. Replies: 7
    Last Post: 04-21-2017, 11:14 AM
  3. Replies: 6
    Last Post: 03-02-2016, 12:58 PM
  4. Replies: 1
    Last Post: 03-06-2013, 10:30 AM
  5. Replies: 5
    Last Post: 06-01-2012, 03:59 PM

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