Group by distinct string

[stephen.h.dow]

Hi -

I'm trying to assign unique id's to distinct values in a field. For
instance,

Query1 (select Customer from Customer_tbl):
Customer
ABC
ABC
XYZ
ABC
XYZ
QWE
ABC
QWE

I want to have:
Customer ID
ABC 1
ABC 1
XYZ 2
ABC 1
XYZ 2
QWE 3
ABC 1
QWE 3

Any help is greatly appreciated.

Steve

 

[Guest]

Search on Ranking.

 

[Neil Sunderland]

I'm trying to assign unique id's to distinct values in a field. For
instance,

Query1 (select Customer from Customer_tbl):
Customer
ABC

I want to have:
Customer ID
ABC 1

[snipped]

The quickest and easiest way is to cheat

First, build a new table containing your distinct customers:
SELECT DISTINCT Customer INTO UniqCust FROM Customer_tbl

Then create your ID field, either using the GUI-weenie method of
opening it in design view and adding an AutoNumber field, or use the
SQL-purist method:
ALTER TABLE UniqCust ADD COLUMN ID Counter