Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sequential Numbering (A2K3)

    Good Morning,

    I have a table (tbl_CompleteList) w/an ID field (AutoNumber) and an indexed Member_ID (duplicates OK) field.

    The table can house multiple identical Member_ID records.

    Let's say Member_ID, 123456789 has 5 records in the table and Member_ID 987654321 appears 3 times in the table, in a query I need to show a Count of these records in the format below:

    Member ID CountOfOccurences
    123456789 1
    123456789 2
    123456789 3
    123456789 4
    123456789 5

    987654321 1
    987654321 2
    987654321 3

    I've tried DCount, DMax, Select Distinct, and much much more. I've traveled this site and many more over the internet using "Sequential Numbering" for searching and I haven't had any luck. I'm hoping someone in here can provide me a suggestion or point me in the right direction.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Sequential Numbering (A2K3)

    Define the field like this in the query:
    <code>
    CountOfOccurrences: DCount("*","tbl_CompleteList","Member_ID=" & [Member_ID] & " AND ID<=" & [ID])
    </code>
    Warning: if you're reading this reply in the e-mail notification, please visit the Lounge to view it; the e-mail version will be mangled.

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

    Re: Sequential Numbering (A2K3)

    CountOfOccurrences: DCount("*","tbl_CompleteList","Member_ID=" & Chr(34) & [Member_ID] & Chr(34) & " AND ID<=" & [ID])

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering (A2K3)

    I have it working but it returns the total count of the recordset rather than sequentially on the group, any ideas?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering (A2K3)

    Ok...I've modified you suggestion as you said to and it provides me with the count but the count appears like it was a totals query rather than a select query. i.e.

    123456789 5
    987654321 3

    I need each record to appear with the count next to it.

    123456789 1
    123456789 2
    123456789 3
    123456789 4
    123456789 5

    987654321 1
    987654321 2
    987654321 3
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Sequential Numbering (A2K3)

    Make sure that View | Totals is not ticked.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sequential Numbering (A2K3)

    I got it Hans, as always, thanks so much for your help
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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