SUBQUERY HELP

I

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

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);
Click to expand...

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

icq_giggles

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


hprog said:
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);
Click to expand...

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

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
 
J

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);
Click to expand...

There's an extra comma after Student2.Zip.
 
J

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
Click to expand...

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
Click to expand...

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

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,


John W. Vinson said:
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);
Click to expand...

There's an extra comma after Student2.Zip.
Click to expand...
 
J

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.
Click to expand...

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

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,


John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
H

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.
Click to expand...

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.
Click to expand...

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
 
Top