I'm putting together a traffic database, and I want to partition different types of traffic shipment data from other types yet I want to keep them all indexed together in a single table. This would allow me to use one shipping appointments table for all sorts of different traffic as well as other similar operations.
I'm thinking the best way to pull this off is with a one to one relationship or a set of 'is a' relationships from a single table. What I am wondering is how is this best practically achieved for form based data entry in Access. I'm thinking an arrangement using replicationID's inserted into the master table at a beforeUpdate event? Then all the keys for all traffic objects are together, but how about collisions if the replciationID's are being generated from 3 separate forms entering data at the same time. Do I have this bkwrds should I start a record in the master and then pass the key to the child? Any suggestions appreciated. Thanks.
I'm taking you to mean that I should use a type_id field in the table holding fleet objects. This would work, except that for each of these objects different data is being stored.
This would mean I would have a table with a huge amount of fields and many of them would be blank depending on the type of traffic object it was. This was the reason for the conceptual split of the data.
Reading my original post I could understand why you thought this would work; it is less than clear. Basically I have 4 separate types of traffic objects with different data being recorded for each but also they are similar in that they are all traffic objects and should have things like appointments, orders etc.
So what I want to do is index the four types of traffic objects in a table with keys to reference individual objects even though they are different. Otherwise I would need 4 appointment tables for each type of traffic object and 4 appointment lines tables for each appointment table etc. Perhaps I'm ass bkwrds here?