need query to create unique number for each record

[rjmdt]

I have a query that is appending new records to another table, the problem is that for unique records I need to assign a unique number to a field. I have figured out that I can get the last number using DMAX, and I just want to add 1 number for record that I am adding. The field is a ship_to_key field and the field must be unique for every cust_key field ie. you can have the same ship_to_key as long as it has a different cust_key. The table I am adding the new records to is ARSHIP. The table I am adding from OWHeader.

OW Header
Cust Key Ship Address
OWSIN 123 Anywhere
OWSIN 158 Place
OWSIN 357 Street
OWSIN 678 Ave

ARSHIP
Cust Key Ship_to_Key Ship Address
OWSIN 001 123 Anywhere
OWSIN 002 158 Place
OWSIN 003 357 Street
OWSIN 004 678 Ave

The Ship_to_Key is a string field

Any help would be appreciated

Thanks in advanced

 

[dhookom]

Try something like:
SELECT [Cust Key], [Ship Address],
Format((SELECT Count(*) FROM OWHeader O
WHERE O.[Cust Key] = OWHeader.[Cust Key]
AND O.[Ship Address]<=OWHeader.[Ship Address]),&quot;000&quot as Ship_to_Key
FROM OWHeader;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Duane - Good idea but if there are two shipments to the same customer and address, you'll get duplicate keys.

rjmdt - If the address could be the same, is there another field to use instead of address in Duane's solution? If there is no single field that works, you can also concatenate fields to produce a unique value for that customer record.

 

[dhookom]

JonFer,
Good catch. I was assuming all data was similar to the examples provided.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rjmdt]

The fields that have to be unique are the cust_key and the ship_to_key. This table represents every ship to location for every customer number. So there won't be multiple addresses, but other customers might have the same ship to location. The Ship_to_key field is only 3 characters long so the concatenate of fields won't work. I got my code to count correctly, but I can't figure out how to apply the number to my field. If you can help I can send the code.

Thanks again

 

[dhookom]

rjmdt,
What do you &quot;apply the number to my field&quot;? This is an append query right? Have you tried to use the SQL I suggested as the source for the append query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Maybe I misunderstood the schema. Is the following correct?

You are appending from OWHeader which has a single record for each Customer with the customer's address. You want to add a new shipping record for that customer in ARSHIP and create a unique key for that customer shipping record.

If that is right, then why don't you just use an autonumber field for the ship_to key? It doesn't have to be sequential for each customer - it could be sequential across all customers.

If you still want a sequence number for each customer, use this to generate a key:

Format((SELECT Count(*) FROM ARSHIP a
WHERE a.[Cust Key] = OWHeader.[Cust Key])+1 ,&quot;000&quot as Ship_to_Key

This assumes you are adding a single record for a customer at a time although you can process multiple customers together.

By the way, are you keeping the address in the ARSHIP table (even though it is already in the OWHeader table) in case a customer moves so that you'll have the true ship to address?