Append Query - Incremental Numbering Question

[dtay1132]

I have a table with approx 8000 records in it which contain names and addresses. Many of the names are repeated with different addresses. I am trying to break the table apart into two tables one for names and then a separate table with address records that relate back to the names. Each Name has a unique ID associated with it so I have a key to tie them together, but my problem comes with how to incrementally number the address records for a given name.

The table looks like:

1234 |John Smith | 123 Main Street | Anytown |MD |20876
1234 |John Smith | 4 Broadway | Anytown |MD |20173
1234 |John Smith | 88 Center Drive | Mytown |MD |21183
5678 |Jane Doe | 6 Main Street | Southtown | MD |21183
5678 |Jane Doe | 12 Broadway | Northtown | MD |21184

What I'd like to do is creat either a make table or append query that will put the address info and Uniqe ID related to the name, but also create a sequential ID for the name that resets each time the Uniqe ID changes.

Example:
UID Loc Address Town St zip
1234 |1 | 123 Main Street | Anytown |MD |20876
1234 |2 | 4 Broadway | Anytown |MD |20173
1234 |3 | 88 Center Drive | Mytown |MD |21183
5678 |1 | 6 Main Street | Southtown | MD |21183
5678 |2| 12 Broadway | Northtown | MD |21184

Any suggestions? I was thinking about some type of conditional statement that would look at the prior record and if the UID changed, reset the LOC ID. My programming skills just aren't that sophisticated.

Thanks

 

[Remou]

Why do you need an incremental number? If it is a key field, all it needs to be is unique.

 

[Golom]

This is called a ranking query. It will return sequential numbers as long as the criteria are unique. In this instance I have made "Address & Town & Zip" the string that (I assume) will be unique.

Select A.UID, 

       (Select Count(*) From myTable As B
        Where A.UID = B.UID 
          AND (A.Address & A.Town & A.Zip <=
               B.Address & B.Town & B.Zip)) As Loc,

       A.Town, A.St, A.Zip

       INTO myNewTable

From myTable As A

Order By A.UID, A.Address, A.Town, A.Zip