Multiple forms sharing common subform

[jtuj71]

I have 3 tables (architects, customers, and vendors) using a contacts subform. It all seems to work correctly but I want to ensure that I'm linking them correctly.
I have the three tables' key fields joined to the ID field in the contacts table but I can't enable referential integrity because the ID's may or may not exist in the contacts field and they may be duplicate (ie. architectID and customer ID both =3). What is the proper way to set this up?
I also have three main forms using the common subform with the recordsource based on a query with the Parent name as criteria...

 

[LittleSmudge]

First off some questions

1) Can the contact for the Architect table ever be the same contact as links to the Customers or Vendors tables?

2) Can a contact for the Customer table ever be the same contact as links to the Vendors table

3) Can a record in the Architects table ever have more than one contact ?

4) Can a record in the Customer table ever have more than one contact ?

5) Can a record in the Vendor table ever have more than one contact ?

6) What other data do you store within the contact table



I suspect that there is a much simpler schema than the one you describe - but I'd need answers to the above before I'd commit to it.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jtuj71]

The Architects, Customers, and Vendors tables can have multiple contacts per companyID. For example, XYZ Architects can have 5 contacts (jim smith, jane doe, etc.) The data stored in the contacts db is on an individual level - cell phone, office extension, spouse, etc..
But, Acme Distributors (vendor) may also happen to have a contact named Jane Doe.
The way I have it set up, that doesn't matter b/c when you click that company from the filter Window, the subform has a listbox with only those contacts that deal with that particular company.

The main 3 tables all have CompanyID and company name fields in common but have other fields not in common otherwise I would have combined them in one table with a type selector.

Hope this helps...
JPT

 

[LittleSmudge]

But, Acme Distributors (vendor) may also happen to have a contact named Jane Doe.
BUT - I'm assuming ( reading between the times ) that the Acme Distributors Jane Doe is NOT actually the same person as the Arcictects Jane Doe.


Then in that case just SIMPLY have three tables
tblAContact
tblCContact
tblVContact

You they will have the same structure, BUT - as they contain specific data that links ONLY to the table that they relate to, then that is perfectly acceptable in Normalisation terms.
There is no duplication of data but there is data integrity improvements.


tblArchitect
ArchitectId Prime Key
CompanyName
ArchField1
ArchField2
etc..

tblAContact
AContactId Prime Key
ArchitectRef Foreign Key to tblArchitect
ContactFName
ContactLName
DOB
InsideLeg
DogsName
etc..

Repeat for other two main tables.

Side Issue
DO NOT have the Primary key for each of the three main tables as "CompanyId".
Have them as "ArchitectId, CustomerId, VendorId so that when you are working in VBCode in two years time looking at pages of code that you haven't glanced at for 18 months - you'll be instantly able to work out what is going on and WHICH table that Primary Key in the code refers to.
This is a similar reason to why Foreign Keys should always end in Ref ( not Id ).



'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jtuj71]

Yes, thanks.

One other clarification...If I'm understanding you correctly, the contacts table should have all three foreign keys in it?

 

[LittleSmudge]

No NO NO !

There is no THE contacts table any more

There is a tblAContacts that holds contacts for Architects
There is a tblCContacts that holds contacts for Customers
There is a tblVContacts that holds contacts for Vendors

In OO speak - the arcitectural contact is a completely different object to a customer contact.
They might have similar looking structures and words in the same place etc BUT they are very different and do not belong in the same table.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D