Have two fields I need to make into one unique identifier

[Dicegod]

Hello everyone,
This is my first post so I apologize if I am not following proper layout or etiquette. I am currently using Access 2013.

I have a database that I am creating for my company to use. I work in the field of import and export logistics. Here is the problem I have. I have three tables that I created. A master table that houses all pertinent information for the shipment such has container number, bill of lading number, customer, so on. I have a table that is going to be the costs table for services rendered and an invoice table for what is going to be charged to the customer accordingly.



The problem is that container numbers can be reused and bills of lading can have more than one container attached to it. So neither field is completely unique. However, the combination of both the container number and bill of lading number will make a unique identifier for the shipment. I believe this is going to be the only way to link all three tables together. Since the form I created has an entry for both the container number and the bill of lading number, I tried creating a calculated field that concatenates them together. However I cannot make this a primary key.

I currently found a way around this by creating one huge table with all the fields that are required to be entered into one table so that they are already linked. I would like to break this down if possible and to make more efficient.

Any suggestions to my current issue would be greatly appreciate it. I am trying to prevent having the end users enter a combination of the two to prevent redundancy and I am trying to prevent creating one table with about 100 fields to it.

[ranman256]

There's no need to create a primary key when one is provided for you. Use the AUTONUM field and a key will be created for you.
This is passive and program free of any work on your part. This allows the same container# to be used over and over.
You can back fill this into child tables to create 1 to many.

you can still create your fake key by combining 2 fields in a query. But its not needed.

[Dicegod]

Thank you for the reply. I already have the autonumber for the ID set up. The issue that I am going to see with this is that since every single shipment will need to have an auto ID. Once the system has run for many many months, we will have thousands in there making it less managable for when they are entering the new information in to make sure the shipment ID so to speak will be added to every shipment that is going to be in the cost and than again the in the Invoice table.

[aytee111]

Look at your table structure and make sure that you are following the rules of normalization. In particular, that no information is repeated. If you have multiple containers for one bill of lading and you keep repeating the bill of lading id, then that is already repetition, showing bad table design. Each shipment will have a unique identifier of some kind, whether it is the bill of lading or shipment number or whatever your business dictates are, but there should be only one of those records on the table. Plus any other single fields that pertain to the master (customer. etc). All other information must be on a separate table, again with a unique identifier.

[NTC]

As posted by 256: fundamentally every table should have an autonumber field and that field alone should be the primary key field.

You state: container numbers can be reused and bills of lading can have more than one container attached to it. So neither field is completely unique.
* the Bill of Lading ID should be unique

What you present to the users as IDs in terms of their ability to look up information depends on the business requirements and can be any field at all. But behind the scenes all relationships should be between the auto number key fields.