Query for two fields

[Nigel]

In a table are fields QtyYear & QtyAmount. QtyYear contains the year (2007,
2008) for the donated amount (QtyAmount). But if no donation for that year
then no record.
I wanted to pull donors who gave in both 2007 & 2008. Not including folks
who gave in just one of those years. I've tried:
SELECT D.Donor
FROM TblOrg AS D
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = 2008))))
ORDER BY D.Donor;
which pulled all those who gave in 2008 regardless whether they gave in
2007. But I could not build on this query to get what I need. I also tried:
SELECT D.Donor, Y.LastYear
FROM TblOrg AS D, TblLastYear AS Y
WHERE (((Exists (SELECT *
FROM TblQty YA
WHERE YA.OrgId = D.OrgId
AND YA.QtyYear = Y.LastYear))=True))
ORDER BY D.Donor;
This has 2007 & 2008 in table TblLastyear. But it pulls those who donated in
2007 or 2008 or 2007 & 2008. I want just those who gave in both years.
Please can you help me out. Thanks Nigel

 

[John Spencer]

One method is to use two subqueries in your from clause

SELECT Q07.*
FROM (SELECT * FROM TblQTY WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT * FROM TblQTY WHERE qtyYear = 2008) as Q08
ON Q07.Donor = Q08.Donor

If you want to control this by OrgID also, then expand the join clause to
ON Q07.Donor = Q08.Donor
AND Q07.OrgID = Q08.OrgId


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Nigel]

I follow what you are saying but am having trouble putting it together. Here
is what I built:
SELECT D.Donor
FROM TblOrg AS D
WHERE (Exists (SELECT Q07.*
FROM (SELECT * FROM TblQTY WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT * FROM TblQTY WHERE qtyYear = 2008) as Q08
ON Q07.Donor = Q08.Donor))
ORDER BY D.Donor;

But it's asking me to input Q07.Donor ? I must not be following correctly.
Thanks Nigel

 

[John Spencer]

I seem to have misunderstood your structure. Perhaps the following will work
better.

SELECT D.Donor
FROM tblOrg as D
WHERE D.OrgID IN (
SELECT Q07.ORGID
FROM (SELECT ORGID FROM TblQTY WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT ORGID FROM TblQTY WHERE qtyYear = 2008) as Q08
ON Q07.OrgID= Q08.OrgID)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Nigel]

Thanks John that worked great.
Here is what I now have:
SELECT D.Donor
FROM TblOrg AS D
WHERE D.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08
ON Q07.OrgId = Q08.OrgId)
ORDER BY D.Donor;
The above selects Donor from table TblOrg. How would I also select QtyAmount
& QtyYear from table TblQty so that Donor, Amount & year are shown in the
results. I tried modifying the selects above but without success. Many
thanks. Nigel

 

[John Spencer]

Add tblQty to your query
Join TblOrg to tblQty based on OrdID or whatever fields you should join on

SELECT D.Donor, tblQty.*

FROM TblOrg AS D INNER JOIN tblQty
ON D.OrgId= tblQty.OrgID

WHERE D.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08
ON Q07.OrgId = Q08.OrgId)

ORDER BY D.Donor;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Nigel]

Thanks for the help. Below is what I did, however this has now negated the
check for 2007 & 2008 & I get all records. instead of just 2007 & 2008. I
was getting just the 2007 & 2008 records prior to selecting the extra fields
from table TblQty.

SELECT TblOrg.Donor, TblQty.QtyAmount, TblQty.QtyYear
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE TblOrg.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08

 

[John Spencer]

Of Course, that was silly of me.

You can try expanding the where clause to filter only the years 2007 and 2008.
You should get two records (ro more records) for each donor and you should
have at least one 2007 and one 2008 record for each donor.

SELECT TblOrg.Donor, TblQty.QtyAmount, TblQty.QtyYear
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE TblOrg.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08
ON Q07.OrgId = Q08.OrgId)
WHERE tblQty.QTYYear in (2007,2008)
ORDER BY TblOrg.Donor, TblQty.QtyYear;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Nigel]

I do appreciate the help. Thanks so much. I get an error when I try that:
Syntax error (missing operator) in query expression 'TblOrg.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08
ON Q07.OrgId = Q08.OrgId)
WHERE TblQty.QtyYear IN'.

so

I changed the "parens" at the end to
ON Q07.OrgId = Q08.OrgId
WHERE TblQty.QtyYear IN (2007,2008))

which allowed me to save okay, but when I run it , it says:
The specified field 'TblQty.QtyYear' could refer to more than one table
listed in the FROM clause of your SQL statement.
Thanks. Nigel

 

[John Spencer]

I guess I need more coffee or a better SQL syntax checker than my brain
that second WHERE should have been an AND.

SELECT TblOrg.Donor, TblQty.QtyAmount, TblQty.QtyYear
FROM TblOrg INNER JOIN TblQty ON TblOrg.OrgId=TblQty.OrgId
WHERE TblOrg.OrgId IN (
SELECT Q07.OrgId
FROM (SELECT OrgId FROM TblQty WHERE QtyYear = 2007) as Q07
INNER JOIN
(SELECT OrgId FROM TblQty WHERE QtyYear = 2008) as Q08
ON Q07.OrgId = Q08.OrgId)
AND tblQty.QTYYear in (2007,2008)
ORDER BY TblOrg.Donor, TblQty.QtyYear;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Nigel]

That worked great. Thanks so much. Merry Christmas. Nigel