Primary Keys for Tables & Forms

[Tantum4]

Hi,



I am a new to this forum so I'm not sure if this is being posted in the correct thread. So straight to my questions. I have a Database for Manifests (Cargo & Waste). For each there are two tables and two Forms with a lot of fields. For the two Forms I have concatenated the Manifest Number which identifies the records like this.. Cargo Manifest: POIN-IN-00*. And Waste Manifest: POIN-WASTE-00* (* being the auto-number). Since these are separate records (Tables) there will be a point when the Auto-number (set as Primary Key) will be the same. What I want to do is have the Primary Key compliment each other. For eg. If a new Cargo Manifest Number is POIN-IN-007 and I have to generate a new Waste Manifest I want it to be POIN-WASTE-008 continuing in this manner don't matter which manifest is used ensuring that the prefix identifies the Manifest. Can this be done in Access? If so How? Please bear in mind that this is done in a program that I use but I want to create a back-up system in the event that the program is down, which happens often as well as use the database for other manifest tracking.

I'll be happy to share more info on my Database if necessary. Thanks for your support and ANY advice given.

Rgds

Tantum4

[June7]

Generating custom unique identifiers is a common topic. Here is one to review for starters https://www.accessforums.net/forms/a...ing-23329.html

[Tantum4]

Generating custom unique identifiers is a common topic. Here is one to review for starters https://www.accessforums.net/forms/a...ing-23329.html

Hi,

Don't quite understand what is being done in that link. Exactly how does it refer to what I'm trying to do? If it can be used, how can I modify the codes to reflect my issue.

Thanks.

[June7]

It's an example of one method to generate a custom unique identifier. Cannot use autonumber fields as primary key. The code calculates a sequential identifier for each new record. This appears to be your requirement; although more complicated because you want the sequence across two tables. Very odd.

[orange]

Please describe to readers exactly what you are trying to achieve in plain English.
How are Cargo and Waste related in business terms? As June says the request is quite strange, but we'd like to hear the rationale.

[Tantum4]

Ok, here goes.. Cargo Manifest is a form used to track items sent out from site via Sea. Waste Manifest is a form used to send Waste items from site via Sea. I want both to use separate tables but share progressive Manifest Number. By that I mean, as stated in above eg, when I create a Cargo and a Waste Manifest the ID Number should be one more than the other in succession. The problem is I dont want to have two manifests with the same ID Number eg. Cargo - IN -0007 and Cargo - WASTE - 0007. I hope this was explained properly.

[orange]

Why do you think

Cargo and a Waste Manifest the ID Number should be one more than the other in succession.

will make life easier??? The numbers are just numbers?? A Cargo Manifest 23 is not the same as a Waste Manifest 23--I'm missing your point.

It's a little like a 20 year old spectator sitting on the 20 yard line in section 20 row 20 seat 20????

You have to look at the values in context to understand what they represent. You wouldn't confuse a 20 year old spectator with seat number 20.

You can certainly have PurchaseOrder 20 and an Invoice number 20 and not be confused with them because they each have a 20 in their identification. Maybe I'm missing something basic???

[June7]

I understand you want one sequence for manifest. Also understand there are two types of manifest. The easiest approach would be to have one table with a field for the Type category (cargo, waste) then let autonumber assign the sequence.

[Tantum4]

Thanks guys for all your advise. June7, I think I will try the approach on your last post.. I'll let you all know how it goes.

[orange]

I agree with June based on the info you have provided. However, I have a feeling you have not analyzed your processes and requirements sufficiently, yet.

You should work through a few tutorials and get a working familiarity with database concepts and business rules.
You may want to research -normalization, supertype/subtype and entity relationship diagramming.