Query Chaos - Selecting Certain Field from Different Tables/Querie

[Guest]

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.

 

[[MVP] S.Clark]

An Inner join is an AND of sorts.

 

[Guest]

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

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

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.