Need a little help please

[chobo321321]

In theory the tables below, and their relationshio make sense to me. I know I could have done two tables, and skipped the third table, but for this project it has to be like that.

Right now I am using an autonumber dataype for tblPersons (PerID), and using an int for the other two primary keys on the other tables. But, what I realized pretty quick was if add a new record(in the datasheet view starting with tblPersons because it won't let me add new records until I have a PerID) I can only have one subdatasheet, but I need two. One for Regualr customers, and one for business customers when I add records to tblPersons. Also, the way it is setup now a regular customer can have a CustID of 1, and a Business Customer can have a BusID of 1 which is stupid and makes no sense since they are in a 1 to 1 relationship with PerID...

Sorry for the confusing questions, but this in my opinion is pretty basic, but almost impossible to find answers for. Any help is appreicated.

 

[Jings00]

Why not use only one table for all the data? Add a field for ContactType "B" or P" and then the subset data could be in dual use fields (CusBusID for example) - If some data does not pair up well with data from the other type you could and would have fields that have a use in one contact type, but not the other. - just a thought to keep the database set-up simple.

 

[chobo321321]

I'm not sure if I understand you correctly. Do you want me to put all the fields in one table, and just add a new field with contact type? If thats the case I would have a lot of Null fields. I'm kinda hoping someone could help me get the original example working.

 

[Jings00]

Yes, with the single table set-up, you would potentially have null fields for data that does not match the person type you are entering, but so will you unless you intend on doing all outer joins to the subtable data. I think you are making something simple overly complex by doing this and once you start adding additional tables and relationships, the set-up may become a burden for future development.
Either way, as for you form - I would not use a subform for data entry if the relationship is 1-1, I would put all the fields on the main form. - possibly hiding/disabling the ones that are not relevant.

 

[chobo321321]

I agree it does seem to be overly complicated, and if it was up to me I would have to two tables (tblRegualrCustomers, and tblBusinessCustomers), but my teacher has this obsession with having a third table for common fields I'll try it the way you proposed. Thanks for the help.

 

[chobo321321]

Thank you so much for the help Pat, these three tables are driving me crazy. I made all the changes you suggested. For the data entry I'll try to make an option for selecting between business, and regular customers.

Is this the way people usually setup tables in the business world? I would have preferred having just two tables even though some field names are the same name.

 

[chobo321321]

Thanks for the explanation, Pat. I had no idea using two tables would break referential integrity. It's kinda sad that I have already taken a basic access course, and this sort of thing was never brought up or explained...