Subquery Help Needed

G

Guest

Hi
Previously I received help from Allen B, MVP. If you are around can you help again. Thank you for the last date. I am still trying to do a subquery to find all donors who have not donated in a time period, 2 years as an example. There are 2 primary tables.
Donors
Cashgifts
I tried to write the query, but I am getting syntax errors. The primary key is DonorNum
In my query I included the following fields from Donor.
DonorNum
LastName
FirstName
Address
City
St
Zip
From the Cashgifts I included.
CashgiftID
DonorNum
Date

The sub query written was

SELECT * FROM Donors WHERE NOT EXISTS (SELECT Cashgiftid from Cashgifts (Date >= DateAdd ("yyyy",2,date())) And (Cashgifts.DonorNum=Donors.DonorNum))

Thanks for any help.
Jeff
 
A

Allen Browne

Three suggestions:
1. The word WHERE is missing from your subquery.

2. You my have meant to subtract 2 years (using -2)?

3. The field named "Date" may be a problem, since this a reserved word in
VBA for the system date. It will probably work in this context, but will
bite you at some stage.

Try:
SELECT * FROM Donors WHERE NOT EXISTS
( SELECT Cashgiftid from Cashgifts
WHERE (Date >= DateAdd ("yyyy", -2,date())) And
(Cashgifts.DonorNum=Donors.DonorNum))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JeffF2F said:
Hi
Previously I received help from Allen B, MVP. If you are around can you
help again. Thank you for the last date. I am still trying to do a subquery
to find all donors who have not donated in a time period, 2 years as an
example. There are 2 primary tables.
Donors
Cashgifts
I tried to write the query, but I am getting syntax errors. The primary key is DonorNum
In my query I included the following fields from Donor.
DonorNum
LastName
FirstName
Address
City
St
Zip
From the Cashgifts I included.
CashgiftID
DonorNum
Date

The sub query written was

SELECT * FROM Donors WHERE NOT EXISTS (SELECT Cashgiftid from Cashgifts
(Date >= DateAdd ("yyyy",2,date())) And
(Cashgifts.DonorNum=Donors.DonorNum))
 
G

Guest

Thank you for you help. The WHERE is missing I see. Does the first SELECT need to be in quotes? When it error out several times, it always highlighted SELECT. But it also may be having to do with the missing where. I will try it when I get to work tomorrow

I know that if we were to rename the DATE field, we would have to redo any reports, forms and queries. Any suggestions

Thanks again
 
A

Allen Browne

No: the SELECT does not go in quotes.

Yes: renaming your DATE field would need to be followed through where it is
used.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
JeffF2F said:
Thank you for you help. The WHERE is missing I see. Does the first
SELECT need to be in quotes? When it error out several times, it always
highlighted SELECT. But it also may be having to do with the missing where.
I will try it when I get to work tomorrow.
I know that if we were to rename the DATE field, we would have to redo any
reports, forms and queries. Any suggestions?
 

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