Expression for Update query to populate IDs

[ttomw]

I have a large table of property parcels and need to create from it a separate owners table. For the new table I need an OwnerID for each unique Owner. I would prefer to use a simple incrementing number for each unique owner value.

Many owners own multiple parcels. So if I create an OwnerID field in the parcels table as a starting point, what is the expression that I could put in an update query that would assign a number to each unique owner and all subsequent records with the same value in the OwnerName field for the parcels table?


Fields in Parcels table (Simplified): ParcelID, OwnerName, OwnerAddress, ParcelDetail1, ParcelDetail2, OwnerID

Thanks,
Tom

 

[Pat Hartman]

SELECT OwnerName INTO TEMP_Names
FROM tblParcels
GROUP BY OwnerName
Order By OwnerName;

After you make the table, open it and add OwnerID as the autonumber primary key.

To populate OwnerID in the parcel table, create a query that joins the two tables on the OwnerName field and update the OwnerID in the parcel table with the OwnerID from the new names table.

 

[ttomw]

Thanks Pat! You Rock! That worked perfectly.
Tom