Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Number Records in a Query (Access 97)

    I have a situation where there are a number of level-2 records associated with each top level record. I need to generate a record count, e.g 1, 2, 3, etc., and store it in each of the level-2 records, so that in other queries I can refer to the 3rd level-2 record associated with each of the top level records, for example. Can someone give me an SQL statement that will assign those record numbers?

    To restate the problem in a different way for clarity, the linking field in the level-2 records is the MailID of the parent record for each level-2 record. If I sort all the records by MailID and then by a date field they will be grouped and sorted like I want them. With them in that order I want to store into those records a 1 or 2 or 3, etc., to permanently record the order they appeared in each MailID group in that sorted list. Can a query accomplish that? How?

    Thanks,
    Bill

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

    Re: Number Records in a Query (Access 97)

    Can the child table contain multiple records with the same MailID and date? If so, how should the duplicates be treated?

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

    Re: Number Records in a Query (Access 97)

    If there are no duplicates, or if there are and it's OK with you if "ties" get the same number, you can use an update query:
    - If you haven't done it yet, add a number field (Long Integer, default value blank) to the level 2 table. I'll call it SeqNo.
    - Make a backup of the database, to be on the safe side.
    - Create a query in design view based on the level 2 table.
    - Add the SeqNo field to the query grid.
    - Select Query | Update Query.
    - Enter the following in the Update to line:

    DCount("*","[tblChild]","[MailID]=" & [MailID] & " AND [TheDate]<#" & Format([TheDate],"mm/dd/yyyy") & "#")+1

    where:
    tblChild is the name of the level 2 table.
    SeqNo is the name of the number field you want to fill.
    TheDate is the name of the date field.

    - Select Query | Run to execute the update.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Number Records in a Query (Access 97)

    Hans,

    I thought my existing date field was a date-time field so there would be no duplicates and the records could be sorted into the order they were added to the table but the date field contains only a date so there are a lot of duplicates. That means that my whole idea will not work because what I'm trying to do is separate the records between the first two that were added and any later ones. I'll have to go back and look for another approach to the original problem!

    I really appreciate your help.

    Bill

Posting Permissions

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