SUBQUERY HELP

[icq_giggles]

What's wrong with my syntax?

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip,
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

 

[hprog]

What's wrong with my syntax?

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE   Student1.ZIP IN (SELECT Student2.Zip,
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

It’s possible that the problem is the missing square bracket in the
row starting HAVING COUNT([Student2].[ZIP

 

[icq_giggles]

Thanks for spotting that, but no it still won't accept it
--
Thank you,

What's wrong with my syntax?

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip,
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

It’s possible that the problem is the missing square bracket in the
row starting HAVING COUNT([Student2].[ZIP

 

[hprog]

On Dec 7, 5:24 pm, icq_giggles <[email protected]>
wrote:

A good idea would be to make the sql statement with an access query
You probably need a self join

 

[hprog]

it is possible that access don't like the brackets around a table name

i had also had some times problems with brackets around field names


i also realized that when access is making the sql staements from the
QBE grid
they all ways include all tables in the from cluase even those that
are only used for criterya

 

[John W. Vinson]

What's wrong with my syntax?

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip,
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

There's an extra comma after Student2.Zip.

 

[John W. Vinson]

it is possible that access don't like the brackets around a table name

i had also had some times problems with brackets around field names

Umm? Doesn't match my experience! Except in the one specific instance of
subqueries in brackets (which Access only did in a couple of versions),
bracketing table and fieldnames is usually actually preferable.

i also realized that when access is making the sql staements from the
QBE grid
they all ways include all tables in the from cluase even those that
are only used for criterya

Well... yes. The QBE grid is just a tool to build SQL, and the SQL *is* the
query; if you need to apply criteria to a field, then the table containing
that field needs to be in the FROM clause.

 

[icq_giggles]

Thank you John, but I'm still getting an error message about a "missing ],)
or item in " the subquery, actually the Where clause.

Here's what's currently in there:

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

I know how to do this with two queries, but figured there had to be way to
do it with a subquery.

--
Thank you,

What's wrong with my syntax?

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip,
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

There's an extra comma after Student2.Zip.

 

[John W. Vinson]

Thank you John, but I'm still getting an error message about a "missing ],)
or item in " the subquery, actually the Where clause.

Here's what's currently in there:

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

I know how to do this with two queries, but figured there had to be way to
do it with a subquery.

As noted elsewhere in the thread, there is indeed a missing ] - just as the
error message says. Look in the last line and count.

One hint for matching brackets and parentheses: scan left to right through the
string, adding +1 for each left and -1 for each right. If you don't come out
to 0 you're missing one.

 

[icq_giggles]

As noted earlier- thought I had corrected it - that and the comma - knew it
was something stupid I was missing - had been looking at it too long

Thanks a million!!!
--
Thank you,

Thank you John, but I'm still getting an error message about a "missing ],)
or item in " the subquery, actually the Where clause.

Here's what's currently in there:

SELECT Student1.Zip, Student1.Last_Name, Student1.First_Name, Student1.Address
FROM Student AS Student1
WHERE Student1.ZIP IN (SELECT Student2.Zip
FROM Student AS Student2
GROUP BY STUDENT2.ZIP
HAVING COUNT([Student2].[ZIP)>=4);

I know how to do this with two queries, but figured there had to be way to
do it with a subquery.

As noted elsewhere in the thread, there is indeed a missing ] - just as the
error message says. Look in the last line and count.

One hint for matching brackets and parentheses: scan left to right through the
string, adding +1 for each left and -1 for each right. If you don't come out
to 0 you're missing one.

 

[hprog]

Umm? Doesn't match my experience! Except in the one specific instance of
subqueries in brackets (which Access only did in a couple of versions),
bracketing table and fieldnames is usually actually preferable.

your 200% right but that is the case that I had problems in which case
when I took off the brackets everything went back to normal, I did not
took my time to check if this was really the problem,
By the way I would be happy to know if when I am accessing a field in
the collection (for example rst.fields(“[your field name]”) ) if it
is OK with brackets?

Well... yes. The QBE grid is just a tool to build SQL, and the SQL *is* the
query; if you need to apply criteria to a field, then the table containing
that field needs to be in the FROM clause.

The QBE grid “is” tool that might save hours of coding and debugging,
To make a self join just include (from the select table dialog box)
the table twice and join them as you would do with any other table, to
this in SQL just type SELF JOIN as you would do any other join,
by the way the whole idea of a join is to avoid the nested select
statement in the where clause