Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    enumerating unique records (AC 2K)

    I need to look through a temporary table, count the number of unique values in a particular column, then add a column to the table with a number (1-n but it has to start at 1 each time) based on this value. E.g., in the following table

    fldCpdID fldPltID
    ======= ======
    ABC001 plt004
    ABC002 plt004
    ABC059 plt835
    DEF023 sup04
    DEF194 sup01

    would need to become...

    fldCpdID fldPltID fldPltNo
    ======= ====== =======
    ABC001 plt004 1
    ABC002 plt004 1
    ABC059 plt835 2
    DEF023 sup03 3
    DEF194 sup03 3

    Any ideas??

    TIA
    Gavin

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

    Re: enumerating unique records (AC 2K)

    The following is based on ideas from Microsoft's Sample Queries database for Access 97. It uses two queries. For illustration purposes, I've named the table tblTemp. You should replace this with the actual table name.

    First, create a query qryUniqueID that returns the unique values for fldPltID. Its SQL is:
    <pre>SELECT DISTINCT fldPltID
    FROM tblTemp</pre>

    Next, create a query based on tblTemp with SQL
    <pre>SELECT fldCpdID, fldPltID,
    (SELECT Count(*) FROM qryDistinctID WHERE [fldPltID] < t.[fldPltID])+1 AS Ranking
    FROM tblTemp AS t
    ORDER BY fldPltID</pre>

    This query is not updateable. If you want an updateable query, you can use
    <pre>SELECT fldCpdID, fldPltID,
    DCount("*","qryDistinctID","[fldPltID] < '" & [fldPltID] & "'")+1 AS Ranking
    FROM tblTemp
    ORDER BY fldPltID</pre>

    Note the use of single and double quotes.

Posting Permissions

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