Syntax for SubQuery

[THWatson]

Using Access 2000

Below is my SQL. The problem part is the SubQuery that I have entered in bold print.

SELECT tblMiscellaneousDonations.RecordID, tblMiscellaneousDonations.LastName, tblMiscellaneousDonations.FirstName, tblMiscellaneousDonations.Amount, tblMiscellaneousDonations.ToFund, Year([DonationDate]) AS YearToCheck, tblMiscellaneousDonations.DonationDate
FROM tblMiscellaneousDonations
WHERE (((tblMiscellaneousDonations.ToFund) [b]In (SELECT ToFund 
FROM tblNewGivings))[/b] AND ((Year([DonationDate]))=[Forms]![frmYearChecker]![txtYearPicker]))
ORDER BY tblMiscellaneousDonations.RecordID, tblMiscellaneousDonations.LastName, tblMiscellaneousDonations.FirstName;

Here is the situation...
I have two tables, "tblMiscellaneousDonations" and "tblNewGivings." Each table has a field called ToFund.

I want to be able to pull all records from tblMiscellaneousDonations where the ToFund field entries match the ToFund entries in tblNewGivings. BUT this is the difficulty...the entries may not be exactly the same.
For example, in tblMiscellaneousDonations an entry is "Heritage" whereas in tblNewGivings the entry is "Heritage Fund."

How do I modify the SubQuery part of the SQL to ensure that all records are pulled?

I have tried various forms of Like but, so far, to no avail.

Thanks.

Tom

 

[PHV]

You may try this:
SELECT D.RecordID, D.LastName, D.FirstName, D.Amount, D.ToFund, Year(D.DonationDate) AS YearToCheck, D.DonationDate
FROM tblMiscellaneousDonations D, tblNewGivings G
WHERE G.ToFund Like '*' & D.ToFund & '*'
AND Year(D.DonationDate) = [Forms]![frmYearChecker]![txtYearPicker]
ORDER BY 1, 2, 3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

PHV
Thanks for the possibility. Unfortunately, that pulls a Cartesian product, with over 4000 records where there should be only 97.

I think I have resolved it by using an IIf expression, as shown in Bold print in the SQL below, and then putting the Subquery in the resultant IncludeCheck expression column.

SELECT tblMiscellaneousDonations.RecordID, tblMiscellaneousDonations.LastName, tblMiscellaneousDonations.FirstName, tblMiscellaneousDonations.Amount, tblMiscellaneousDonations.ToFund, Year([DonationDate]) AS YearToCheck, tblMiscellaneousDonations.DonationDate, [b]IIf(Right([ToFund],5)=" Fund" Or [ToFund]="ContacT Place",[ToFund],[ToFund] & " Fund") AS IncludeCheck[/b], [FirstName] & " " & [LastName] AS FullName
FROM tblMiscellaneousDonations
WHERE (((Year([DonationDate]))=[Forms]![frmYearChecker]![txtYearPicker]) AND ((IIf(Right([ToFund],5)=" Fund" Or [ToFund]="ContacT Place",[ToFund],[ToFund] & " Fund")) In (Select ToFund From tblNewGivings)))
ORDER BY tblMiscellaneousDonations.RecordID, tblMiscellaneousDonations.LastName, tblMiscellaneousDonations.FirstName;

Perhaps there is a better way, but this seems to work properly.

Tom

 

[PHV]

SELECT D.RecordID, D.LastName, D.FirstName, D.Amount, D.ToFund, Year(D.DonationDate) AS YearToCheck, D.DonationDate
FROM tblMiscellaneousDonations D, tblNewGivings G
WHERE (G.ToFund = D.ToFund OR G.ToFund = D.ToFund & ' Fund')
AND Year(D.DonationDate) = [Forms]![frmYearChecker]![txtYearPicker]
ORDER BY 1, 2, 3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

PHV
Thanks for the new SQL. Unfortunately, it yields the same result...4372 records instead of 97.

What I want to be able to pull from tblMiscellaneousDonations are those records, and only those records, for the year selected, where entries in the ToFund field match entries in the ToFund field, for the same year, in tblNewGivings.

There are two idiosyncracies to the ToFund field...
1. ContacT PlacE will always be the same in both tables.
2. A few funds, e.g. Heritage Fund, will be called "Heritage" in tblMiscellaneousDonations and "Heritage Fund" in tblNewGivings.

Tom