G
Guest
I'm working on a project were I have tables for Companies, Addresses and
Contacts, as well as a bridge table that links all three. I know it is
cumbersome, but the contacts can belong to multiple companies and have
multiple phones, faxes and addresses and the companies can have multiple
contacts, have main phones and faxes but also have multiple addresses
(onsite, mailing and some satellite locations). And many of the companies
that share contacts are totally separate entities, and do not follow typical
parent/child corporate protocalls; the relationships are very complex.
Most of the contacts prefer to have all contact through a main company
phone, fax and address, while some prefer to be contacted through a direct
line, direct faxeor a satellite address. I developed tables, queries and
forms to allow for this by entering the individual data for all contacts, but
the end-users hate having to enter the same phone, fax and address
information that already exists for the main company.
I've figured out how to display and report the main company information for
contacts, and collect the direct contact information in a subform, but I'm
having a devil of a time figuring out how to create queries and reports that
are capable of showing the direct contact info for the 30% who have that
information entered, but will default to show the main company info for the
70% who don't.
I'm unsure as to whether I need to devise new table fields and data entry
forms that I will then need to devise queries and reports using VB, macros or
expressions that first search for direct contact info but defaults to the
main company info if a "null" value is returned, or if my table fields and
forms are already capable, but I still need to devise some kind of
conditional value code.
Is my challenge even possible to accomplish in MS Access? If not, my choice
is to tell the data entry folks, too bad - they'll have to enter the data
twice, or it will always default to the main company info (what I have the
current version doing now). If this is possible I'd appreciate some tips.
Thanks - you MVPs are always great!
Contacts, as well as a bridge table that links all three. I know it is
cumbersome, but the contacts can belong to multiple companies and have
multiple phones, faxes and addresses and the companies can have multiple
contacts, have main phones and faxes but also have multiple addresses
(onsite, mailing and some satellite locations). And many of the companies
that share contacts are totally separate entities, and do not follow typical
parent/child corporate protocalls; the relationships are very complex.
Most of the contacts prefer to have all contact through a main company
phone, fax and address, while some prefer to be contacted through a direct
line, direct faxeor a satellite address. I developed tables, queries and
forms to allow for this by entering the individual data for all contacts, but
the end-users hate having to enter the same phone, fax and address
information that already exists for the main company.
I've figured out how to display and report the main company information for
contacts, and collect the direct contact information in a subform, but I'm
having a devil of a time figuring out how to create queries and reports that
are capable of showing the direct contact info for the 30% who have that
information entered, but will default to show the main company info for the
70% who don't.
I'm unsure as to whether I need to devise new table fields and data entry
forms that I will then need to devise queries and reports using VB, macros or
expressions that first search for direct contact info but defaults to the
main company info if a "null" value is returned, or if my table fields and
forms are already capable, but I still need to devise some kind of
conditional value code.
Is my challenge even possible to accomplish in MS Access? If not, my choice
is to tell the data entry folks, too bad - they'll have to enter the data
twice, or it will always default to the main company info (what I have the
current version doing now). If this is possible I'd appreciate some tips.
Thanks - you MVPs are always great!