Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Allenhurst, New Jersey
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assign a sequence number (Access 2003)

    My problem is trying to assign a sequence number to a record based upon a date. The table "Meter Reads" has the following fields: DeviceID, Read Date and Meter Reading. For each unique DeviceID, I need to assign a sequence number based upon the chronological order of the "Read Date" field. The meters are read on an approximate monthly schedule, there can be two reads within one calendar month so that using the month number will not always give the correct read order.

    Thanks for the help.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Assign a sequence number (Access 2003)

    Just curious, unless you need to assign the number or the reads in a given month sequentially, why not just put an autoid counter as a forth field. Then you can use this field to sort your data. So if you have 100 readings in the month of January for one meter (quantity does not matter), query on the meterid, sortid, and date then sort by sort id. Your data would then be sorted properly.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assign a sequence number (Access 2003)

    I agree with Gary, but you could calculate the sequence number in a query:

    SeqNo: DCount("*", "Meter Reads", "[Device ID] = " & [Device ID] & " AND [Read Date] <= #" & Format([Read Date], "mm/dd/yyyy") & "#")

    This assumes that Device ID is a number field. If it is a text field:

    SeqNo: DCount("*", "Meter Reads", "[Device ID] = " & Chr(34) & [Device ID] & Chr(34) & " AND [Read Date] <= #" & Format([Read Date], "mm/dd/yyyy") & "#")

    If you want to store the numbers in a field in the table (for example because the calculations are too slow), you can use an update query to populate the field with the result of the calculation.

Posting Permissions

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