Subquery Help Needed

[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

 

[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.

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))

 

[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

 

[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.

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?