Stuck on this Difficult Query

[lchiav]

I have a contact database which contains three tables that I need to create a query from. The first table (Industry Companies) contains the primary mailing address for my companies. The second table (Industry Representatives) contains contact information and an address for the company representative only when their address is different than the primary mailing address. The third table (Request and Donor History) is used to record each request I make from a company or representative from a company.

I want to be able to run a query for a particular request date to send a mailing to the representatives and use their contact information and mailing address when it is different than the primary company mailing address or if the reps address is the primary company mailing address the query will return that information. In addition, I also want the query to include the primary company address when the representative id is null.

The table structures in my database are:

The table structures are -

Industry Companies -

Company Number
Company
Address 1
Address 2
City
State
Zip
Telephone
Fax
Tollfree
Website
Notes

Industry Representatives -

Rep ID
Company Number
Company
Last Name
First Name
Fullname
Title
Rep Tel
Rep Fax
Rep E-mail
Salutation
Rep Address
Rep Address 2
Rep City
Rep State
Rep Zip

Request and Donor History -

Program ID
Program
Company Number
Company
Industry Rep ID
Request Type
Request Date
Year
Request Amount
Status Type
Delivery Method
Grant Amount
Notes

Can any one help me with this query?

 

[rjoubert]

I may not have added every field you may need in your query, but try this out...

SELECT [Industry Companies].[Company Number], [Industry Companies].Company, [Industry Representatives].[Rep ID], [Industry Representatives].[Last Name], [Industry Representatives].[First Name], [Request and Donor History].[Request Date], IIf(IsNull([Industry Representatives]![Rep Address]),[Industry Companies]![Address 1],[Industry Representatives]![Rep Address]) AS Address1, IIf(IsNull([Industry Representatives]![Rep Address 2]),[Industry Companies]![Address 2],[Industry Representatives]![Rep Address 2]) AS Address2, IIf(IsNull([Industry Representatives]![Rep City]),[Industry Companies]![City],[Industry Representatives]![Rep City]) AS City, IIf(IsNull([Industry Representatives]![Rep State]),[Industry Companies]![State],[Industry Representatives]![Rep State]) AS State, IIf(IsNull([Industry Representatives]![Rep Zip]),[Industry Companies]![Zip],[Industry Representatives]![Rep Zip]) AS Zip
FROM ([Industry Companies] LEFT JOIN [Industry Representatives] ON [Industry Companies].[Company Number] = [Industry Representatives].[Company Number]) INNER JOIN [Request and Donor History] ON [Industry Companies].[Company Number] = [Request and Donor History].[Company Number];

 

[rjoubert]

You'll also need to add a WHERE clause to filter by your request date.

WHERE ((([Request and Donor History].[Request Date]) Between #1/1/2006# And #1/31/2006#))

 

[lchiav]

Thank you very much for responding. I will try you suggestion