Company Address or Contact ADdress???

[b9791]

My database is set up with a company table that has all relevant addy info and then I also have a contact table with companyID and additional addy info as not all contacts in the db have a company that they are associated to. My contacts table has a checkbox to indicate whether or not to use the business addy or the contact addy.

So my question is this...How do I create a query that displays the correct address info for each record in my contacts table?

I have attached a screenshot of my relationships to help anyone willing to try and answer the question. Thanks for your help.

Brian

 

[Fornatian]

I would build three queries,

Q1: Pull company address where company info is not null
Q2: Pulls contact address where company info is null
Q3: Uses a UNION query to put the two together.

I am aware that this could be achieved in one query using calculated column headers using Iif(IsNull([CompanyField],[ReplacementContactField].[CompanyField]) but that can be slow and require recalculation often if used in a report or form.

 

[b9791]

thank you, i will try that

 

[KevinM]

How about removing address fields from the Company table and just consider as 'One Company to Many Contacts & Addresses'.

Should only take an append and/or an update query or two to re do it all !

 

[Pat Hartman]

Kevin, that would cause the duplication of company addresses if there were multiple contacts for a company.