Unique Identifier 1

[benderulz]

I have a table that contains a record for PO# 12345, the same PO arrives again and a new records needs to be inserted into the table, but since the PO# is the unique identifier I need it to be inserted as something like 123451. Is it possible to have Access check for the PO, if it already exists in the table, add a digit to the end before inserting it into the table?

 

[dhookom]

How are new records inserted into the table? Are users keying them in or are the imported from Excel or are they appended from another table?

Duane
Hook'D on Access
MS Access MVP

 

[benderulz]

Appended from and ODBC source.

 

[jedraw]

??? Why is the same PO, or a second PO with same unique number, arriving to you?

 

[dhookom]

I would append to a temporary staging table and then process and update the field in the staging table prior to appending to the final table.

Duane
Hook'D on Access
MS Access MVP

 

[benderulz]

Ok that makes sense. I append to the holding table, run the update so if the PO is not null in the "real" table it updates to [PO]&"1".

Now, is there a way to make it update to the next available number in the case that there was a backorder already on the table, or do I have to step it through again and again. In some cases our vendors send multiple backorders for the same PO, so the PO number could read anywhere from 12345 to 123459.

 

[dhookom]

I would think you need to write code to step through the recordset where there are duplicate and update a record at a time.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I was just thinking that I would consider adding a new sequence field with a default of 1 or 0. Then keep the original value and increment the sequence field.

Duane
Hook'D on Access
MS Access MVP

 

[benderulz]

I think I figured it out. I ended up writing additional updates that run after the original but use the Update To of Left([PO],5)&"2" and so on before I append to the table and clear the holding table.

Thanks for all your help!