Query Chaos - Selecting Certain Field from Different Tables/Querie

J

jdb

Hello, I have a Query based on 2 tables and another query. All three are
joined by the field ID (PK in the tblComplete List of Contacts table).

I want to select certain records from the Affiliations table (those where
the yes/no Business field is Yes) and certain records from the Total
Donations by ID query (those where the SumOfAmount field is >25).

The following is the SQL code. The problem is it ONLY returns records from
the Affiliations table where Business =Yes and SumOfAmount is >25. I want it
to return ALL the businesses, as well as all other records who donated more
than 25.

Somehow, somewhere, it is using an AND rather than an OR. I think.
Frankly, not really sure.

SELECT [tblComplete List of Contacts].ID, [tblComplete List of
Contacts].ContactName, [tblComplete List of Contacts].Address1, [qryTotal
Donations by ID].SumOfAmount, tblAffiliations.Business,
tblAffiliations.[Professional Advisor]
FROM ([tblComplete List of Contacts] INNER JOIN tblAffiliations ON
[tblComplete List of Contacts].ID = tblAffiliations.ID) INNER JOIN [qryTotal
Donations by ID] ON [tblComplete List of Contacts].ID = [qryTotal Donations
by ID].ID
WHERE ((([tblComplete List of Contacts].Address1) Is Not Null) AND
(([qryTotal Donations by ID].SumOfAmount)>25)) OR
(((tblAffiliations.Business)=Yes));


Thank you.
 
J

jdb

Thank you. After some wrangling, I think I got what I want by using a Left
Join.
--
Joe


[MVP] S.Clark said:
An Inner join is an AND of sorts.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

jdb said:
Hello, I have a Query based on 2 tables and another query. All three are
joined by the field ID (PK in the tblComplete List of Contacts table).

I want to select certain records from the Affiliations table (those where
the yes/no Business field is Yes) and certain records from the Total
Donations by ID query (those where the SumOfAmount field is >25).

The following is the SQL code. The problem is it ONLY returns records
from
the Affiliations table where Business =Yes and SumOfAmount is >25. I want
it
to return ALL the businesses, as well as all other records who donated
more
than 25.

Somehow, somewhere, it is using an AND rather than an OR. I think.
Frankly, not really sure.

SELECT [tblComplete List of Contacts].ID, [tblComplete List of
Contacts].ContactName, [tblComplete List of Contacts].Address1, [qryTotal
Donations by ID].SumOfAmount, tblAffiliations.Business,
tblAffiliations.[Professional Advisor]
FROM ([tblComplete List of Contacts] INNER JOIN tblAffiliations ON
[tblComplete List of Contacts].ID = tblAffiliations.ID) INNER JOIN
[qryTotal
Donations by ID] ON [tblComplete List of Contacts].ID = [qryTotal
Donations
by ID].ID
WHERE ((([tblComplete List of Contacts].Address1) Is Not Null) AND
(([qryTotal Donations by ID].SumOfAmount)>25)) OR
(((tblAffiliations.Business)=Yes));


Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top