Help With Update Queries

[Van DeWald]

I have a database where I need to reorder the customer id, based on an
alphabetical sort of the last name. My sample database looks like this:

Record, Customer ID, Last Name, First Name, County, ....
1, JEF1, Bobek, Rob, Jefferson, ...
2, JEF2, Johnson, Tina, Jefferson, ...
3, JEF3, Adams, Lisa, Jefferson, ...

but I need to alter the Customer ID, so that the database reads:

Record, Customer ID, Last Name, First Name, County, ....
3, JEF1, Adams, Lisa, Jefferson, ...
2, JEF2, Bobek, Rob, Jefferson, ...
1, JEF3, Johnson, Tina, Jefferson, ...

My customer ID is the first 3 characters of the County that they live in,
plus the numerical sequence number from the alphabetical sort of the last
name of all records for that county.

But, I'm finding that with Update Queries, I can't perform the update based
on a sort ascending option (as available in regular select queries). Thus,
I guess I have to use a Macro or some Visual Basic to do this? Anyone have
any ideas on how or where to begin?

Thank you,

Van
if replying via email, delete the _remove from the email address.

 

[GVaught]

You don't want to do this. If CustomerId is used in other tables you will
'kill' your relationship if you try to renumber them based on the alpha
sort. You will drive yourself crazy trying to renumber your listing anytime
a new user is added.

What is the purpose other than to see them in numerical sorted order
according to the alphabet.

 

[Van DeWald]

You don't want to do this. If CustomerId is used in other tables you will
'kill' your relationship if you try to renumber them based on the alpha
sort. You will drive yourself crazy trying to renumber your listing anytime
a new user is added.

The Customer ID field is not linked to any other table, so changing the ID
will not harm any relationship values.

What is the purpose other than to see them in numerical sorted order
according to the alphabet.

Simply, the customer never sees their own ID, it's for internal purposes
(i.e. my use only). But, I do constantly update that customer ID, based on
the sort ascending order of the last name in each county. The intended
purpose is too complicated to describe here, but it is needed, for a
map/table printout, and easy cross reference.

What I've been doing in the past, is to renumber the customer ID's by hand
after I iniate the query. I was hoping to automate this task with an update
query, but find that I can't do the update query in a specific order, i.e.
sort ascending by last name.

Regards,
Van

 

[John Spencer (MVP)]

You might be able to use a ranking query so that you don't have to renumber all
the time.

SELECT P.LastName, P.FirstName, P.County, ...
1 + (SELECT Count(*)
FROM YourTable as T
WHERE T.County = P.County AND
T.LastName & " " & T.FirstName < P.LastName & " " & P.FirstName
) as TheRank
FROM YourTable As P

This would give you problems with duplicate ranks when their was an identically
named individual in the same county.

And yes, you could do this using a recordset and step through the recordset. I
would still store the "Rank" in a separate field. If you don't want to use the
Ranking query idea (which I hope I entered correctly), then post back and
perhaps someone can help you with the code.