Assign unique # to groups in table

[LNewton]

I have a table that has varying numbers of rows with unique data, i.e., Col A = xxxx and Col B = B or S, Col C = D or U. I can have XXXX B,D or XXXX B,U or XXXX S, D or XXXX S, U. These are group by XXXX. Finaly the question. How do I assign a unique number for XXXX B,D Etc. If I have 10 rows of XXXX B, D I want each row to have 1 assigned. If I have 6 rows of YYYY S,D I want each row to have a 2 assigned and so on to EOF?

 

[Pat Hartman]

1. Create a new table with three fields:
GroupId (autonumber)
Fld1
Fld2

2. Create a query to select the distinct values of the two fields:
Select Distince Fld1, Fld2
From YourTable;

3. Add the new GroupId column to your original table.

4. Create an update query that joins the original table to the new table on Fld1 and Fld2 and updates the GroupId field in the original table with the value from the new table.

 

[LNewton]

Thank you the solution worked.