Handling Pre-populated Table = N in 1:N Relationship

[CG2009]

Can someone advise how to handle the following case in a project I'm working on:

Business scenario -- Multiple trucking companies make multiple deliveries at one location. Each driver is provided an Entry number each time s/he passes through a security gate to the location (s/he can make several deliveries in a given week and more than one delivery on a given day of the week). At the location, the driver may have a split load of 1 to 3 deliveries for 1 to 3 receivers of the cargo... upon entry, a Delivery number is assigned for each carglo load split.

So, there is a 1:N relationship between 2 tables, 'Entry' (PK = entry_no that's autonumbered) and 'Delivery' (PK is concatenated with delivery_no (that's autonumbered) and entry_no), where 1 entry can have 1 or more deliveries.

My dilemma is that the office staff of the location receives a pre-notification 24 hours prior to deliveries being made (for security reasons). The 'Delivery' table is therefore pre-populated, but the 'Entry' table will end up with entry_no fields filled in with no values in the fields of other attributes of the 'Entry' table (e.g. driver_id, entry_date, entry_time).

My course instructor suggested using a "dummy" number to be assigned to the entry_no of, I believe, the 'Delivery' table...

Is this the correct solution? If yes, how do I do it? If not, is there a solution in MS Access (version 2002)?

Thanks in advance for any advice provided.

 

[jzwp22]

Welcome to AWF!

If I understand correctly, it sounds like you have a many-to-many relationship which requires a junction table to relate the entries and deliveries.

I would also recommend an autonumber primary key for your delivery table not a composite pk. So the structure would look like this:

tblEntry
-pkEntryID primary key, autonumber
-other fields related to the entry

tblDelivery
-pkDeliveryID primary key, autonumber
other delivery fields

tblEntryDelivery
-pkEntryDeliveryID primary key, autonumber
-fkEntryID foreign key to tblEntry
-fkDeliveryID foreign key to tblDelivery

Using this format you can enter a delivery beforehand and then when it is actually delivered, it can be tied to the appropriate entryID via the junction table.