Subquery Help Still Needed

[Guest]

Hi again - still having error

This is the subquery
SELECT*FROM[Donors]WHERE NOT EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date()))And([Cashgifts].[DonorNum]=[Donors].[DonorNum])

This is the error
The syntax of the subquery in this expression is incorrect
Check the subquery’s syntax and enclose the subquery in parenthese

If I enclose the entire subquery in parentheses I get this error
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query’s FROM clause. Revise the SELECT statement of the subquery to request only one field

Any suggestions


----- Allen Browne wrote: ----

No: the SELECT does not go in quotes

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

Thank you for you help. The WHERE is missing I see. Does the firs

SELECT need to be in quotes? When it error out several times, it alway
highlighted SELECT. But it also may be having to do with the missing where
I will try it when I get to work tomorrow

 

[John Viescas]

Try this:

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

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi again - still having errors

This is the subquery:
SELECT*FROM[Donors]WHERE NOT EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date()
))And([Cashgifts].[DonorNum]=[Donors].[DonorNum]))

This is the error:
The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses

If I enclose the entire subquery in parentheses I get this error:
You have written a subquery that can return more than one field without

using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field.

 

[Guest]

I am still getting the same errors. Maybe I am doing something incorrect

I am doing a select query. Pulling down the fields with in the subquery, plus some additonal information fields needed in a report. I am putting the subquery in the criteria of the first field, Donor.DonorNum. We are using Access 2000

----- John Viescas wrote: ----

Try this

SELECT * FROM [Donors
WHERE NOT EXIST
(SELECT [Cashgiftid] FROM [Cashgifts
WHERE ([Cashgifts].[Date] >= DateAdd("yyyy",-2,date())
And ([Cashgifts].[DonorNum] = [Donors].[DonorNum])

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=143441

Hi again - still having error

This is the subquery

SELECT*FROM[Donors]WHERE NO EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date(
))And([Cashgifts].[DonorNum]=[Donors].[DonorNum])

This is the error

The syntax of the subquery in this expression is incorrect
Check the subquery's syntax and enclose the subquery in parenthese

If I enclose the entire subquery in parentheses I get this error

You have written a subquery that can return more than one field withou

using the EXISTS reserved word in the main query's FROM clause. Revise th
SELECT statement of the subquery to request only one field

 

[John Viescas]

It's very difficult to do an EXISTS or NOT EXISTS subquery on the query
grid. You should be able to switch to SQL view, paste the SQL I gave you on
top of the existing SQL, and run the query.

Reading the description of what you're doing on the grid, you're not
building a subquery. What's the SQL from your latest attempt? What is it
you're really trying to do -- the business problem you're trying to solve?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I am still getting the same errors. Maybe I am doing something incorrect.

I am doing a select query. Pulling down the fields with in the subquery,

plus some additonal information fields needed in a report. I am putting the
subquery in the criteria of the first field, Donor.DonorNum. We are using
Access 2000.

----- John Viescas wrote: -----

Try this:

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

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi again - still having errors

This is the subquery:

SELECT*FROM[Donors]WHERE NOT

EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date()

))And([Cashgifts].[DonorNum]=[Donors].[DonorNum]))

This is the error:

The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses

If I enclose the entire subquery in parentheses I get this error:

You have written a subquery that can return more than one field

without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. through where
it is

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

Reply to group, rather than allenbrowne at mvps dot org.
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. redo any
reports, forms and queries. Any suggestions?

 

[John Viescas]

My original focus was correcting the syntax of your query. Looking at the
SQL, it appears that you're trying to find all Donors who did not make a
cash donation in the last two years. Another way to solve that would be:

SELECT Donors.*
FROM Donors LEFT JOIN
(SELECT DonorNum
FROM Cashgifts
WHERE CashGifts.[Date] >= DateAdd("yyyy", -2, Date())) As Cash2
ON Donors.DonorNum = Cash2.DonorNum
WHERE Cash2.DonorNum IS NULL;

The above will run tons faster than a NOT EXISTS.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I am still getting the same errors. Maybe I am doing something incorrect.

I am doing a select query. Pulling down the fields with in the subquery,

plus some additonal information fields needed in a report. I am putting the
subquery in the criteria of the first field, Donor.DonorNum. We are using
Access 2000.

----- John Viescas wrote: -----

Try this:

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

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi again - still having errors

This is the subquery:

SELECT*FROM[Donors]WHERE NOT

EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date()

))And([Cashgifts].[DonorNum]=[Donors].[DonorNum]))

This is the error:

The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses

If I enclose the entire subquery in parentheses I get this error:

You have written a subquery that can return more than one field

without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. through where
it is

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

Reply to group, rather than allenbrowne at mvps dot org.
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. redo any
reports, forms and queries. Any suggestions?

 

[Guest]

Thank you for your help. Yes the basis is to find donors that have not given within a certain time period. When I get to work tomorrow I will give it a try using the SQL view. I got busy today, I I could not get back to your other message

Thank
Jef

----- John Viescas wrote: ----

My original focus was correcting the syntax of your query. Looking at th
SQL, it appears that you're trying to find all Donors who did not make
cash donation in the last two years. Another way to solve that would be

SELECT Donors.
FROM Donors LEFT JOI
(SELECT DonorNu
FROM Cashgift
WHERE CashGifts.[Date] >= DateAdd("yyyy", -2, Date())) As Cash
ON Donors.DonorNum = Cash2.DonorNu
WHERE Cash2.DonorNum IS NULL

The above will run tons faster than a NOT EXISTS

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=143441

I am still getting the same errors. Maybe I am doing something incorrect

plus some additonal information fields needed in a report. I am putting th
subquery in the criteria of the first field, Donor.DonorNum. We are usin
Access 2000

----- John Viescas wrote: ----
Try this
SELECT * FROM [Donors

WHERE NOT EXIST
(SELECT [Cashgiftid] FROM [Cashgifts
WHERE ([Cashgifts].[Date] >= DateAdd("yyyy",-2,date())
And ([Cashgifts].[DonorNum] = [Donors].[DonorNum])

--

John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=143441

Hi again - still having error

This is the subquery

SELECT*FROM[Donors]WHERE NO

EXISTS(SELECT[Cashgiftid]FROM[Cashgifts]WHERE(Date>=DateAdd("yyyy",-2,date(

))And([Cashgifts].[DonorNum]=[Donors].[DonorNum])

This is the error

The syntax of the subquery in this expression is incorrect
Check the subquery's syntax and enclose the subquery in parenthese

If I enclose the entire subquery in parentheses I get this error

You have written a subquery that can return more than one fiel

withou
using the EXISTS reserved word in the main query's FROM clause Revise th
SELECT statement of the subquery to request only one field through wher
it i

used
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org
firs
SELECT need to be in quotes? When it error out several times

i

alway
highlighted SELECT. But it also may be having to do with th

missing

where.
I will try it when I get to work tomorrow. redo any
reports, forms and queries. Any suggestions?

 

[Guest]

WOOOHOOO!!

Thanks

 

[John Viescas]

Well, that makes my day! Glad I could help.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411