Hello,
I am trying to ask a fairly complicated question with a correlated subquery
in Access 2003. This is my first endeavor into SQL, I have always been able
to use design view to run my queries. I am trying to select boats that have
landings in a specific port and all of the other ports they landed in. To
rephrase: If boat x has data in port y, please return all data in all ports
for that boat.

Here is my current code:

SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
FROM Moss_Allports
WHERE EXISTS (SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, YEAR
FROM Moss_Allports
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
HAVING (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
HAVING (((Moss_Allports.YEAR)=1981));

With this code, I am still getting all boats in 1981 whether they landed in
port y or not. I only want ports that landed in port y, and I want all of the
other ports those boats went to.

Thanks,
Rebecca

Rebecca, could you explain what your fields are? I'm assuming DRVID is some
sort of ID field, and YEAR is obviously the year, in this case 1981, but just
by looking at your code I have no idea what the other fields are supposed to
be.

As for boat x and port y, I'm not quite sure how they fit in since it isn't
obvious what I'm looking at. It looks like you're using a subquery for your
WHERE clause, but without more information I can't really tell you anything
else.

-Chris

"sassycat8" wrote:

> Hello,
> I am trying to ask a fairly complicated question with a correlated subquery
> in Access 2003. This is my first endeavor into SQL, I have always been able
> to use design view to run my queries. I am trying to select boats that have
> landings in a specific port and all of the other ports they landed in. To
> rephrase: If boat x has data in port y, please return all data in all ports
> for that boat.
>
> Here is my current code:
>
> SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
> AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
> FROM Moss_Allports
> WHERE EXISTS (SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, YEAR
> FROM Moss_Allports
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> HAVING (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> HAVING (((Moss_Allports.YEAR)=1981));
>
> With this code, I am still getting all boats in 1981 whether they landed in
> port y or not. I only want ports that landed in port y, and I want all of the
> other ports those boats went to.
>
> Thanks,
> Rebecca

I'm having a little of the same trouble Chris is. However, try removing the
multiple column references, in your subquery. Your subquery is not providing
specific results, and your base query has no parameter associated to the
subquery.

My SQL is a little rusty, so I may be a little off on the syntax:

SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
FROM Moss_Allports
WHERE Moss_Allports = (SELECT Moss_Allports.DRVID FROM Moss_Allports
WHERE (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
HAVING (((Moss_Allports.YEAR)=1981));


Good luck

Sharkbyte



"sassycat8" wrote:

> Hello,
> I am trying to ask a fairly complicated question with a correlated subquery
> in Access 2003. This is my first endeavor into SQL, I have always been able
> to use design view to run my queries. I am trying to select boats that have
> landings in a specific port and all of the other ports they landed in. To
> rephrase: If boat x has data in port y, please return all data in all ports
> for that boat.
>
> Here is my current code:
>
> SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
> AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
> FROM Moss_Allports
> WHERE EXISTS (SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, YEAR
> FROM Moss_Allports
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> HAVING (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> HAVING (((Moss_Allports.YEAR)=1981));
>
> With this code, I am still getting all boats in 1981 whether they landed in
> port y or not. I only want ports that landed in port y, and I want all of the
> other ports those boats went to.
>
> Thanks,
> Rebecca

My apologies.
DRVID=unique boat ID
s-n-pc-a=port id
year=year
rndwt=weight of fish brought to that port
adj_rev= $worth of above fish

The sub query is supposed to be asking for boats that have data in port 22
in 1981, and the main query is asking for the data about those specific
boats in all ports.

I hope this helps?

WithSharkbyte's code I get an error that says "at most one record can be
returned by this subquery"

"Sharkbyte" wrote:

> I'm having a little of the same trouble Chris is. However, try removing the
> multiple column references, in your subquery. Your subquery is not providing
> specific results, and your base query has no parameter associated to the
> subquery.
>
> My SQL is a little rusty, so I may be a little off on the syntax:
>
> SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
> AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
> FROM Moss_Allports
> WHERE Moss_Allports = (SELECT Moss_Allports.DRVID FROM Moss_Allports
> WHERE (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> HAVING (((Moss_Allports.YEAR)=1981));
>
>
> Good luck
>
> Sharkbyte
>
>
>
> "sassycat8" wrote:
>
> > Hello,
> > I am trying to ask a fairly complicated question with a correlated subquery
> > in Access 2003. This is my first endeavor into SQL, I have always been able
> > to use design view to run my queries. I am trying to select boats that have
> > landings in a specific port and all of the other ports they landed in. To
> > rephrase: If boat x has data in port y, please return all data in all ports
> > for that boat.
> >
> > Here is my current code:
> >
> > SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
> > AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
> > FROM Moss_Allports
> > WHERE EXISTS (SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, YEAR
> > FROM Moss_Allports
> > GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> > HAVING (((Moss_Allports.YEAR)=1981)) AND (((Moss_Allports.S_N_PC_A)=22))
> > GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR
> > HAVING (((Moss_Allports.YEAR)=1981));
> >
> > With this code, I am still getting all boats in 1981 whether they landed in
> > port y or not. I only want ports that landed in port y, and I want all of the
> > other ports those boats went to.
> >
> > Thanks,
> > Rebecca

On Thu, 2 Mar 2006 10:54:29 -0800, Sharkbyte <(E-Mail Removed)->
wrote:

>My SQL is a little rusty, so I may be a little off on the syntax:

Looks very close but... If there might be more than one ID, you need
to use the IN() operator rather than =. I'd also move the Year
criterion into the WHERE clause (applied before grouping) rather than
the HAVING clause:

SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A,
Sum(Moss_Allports.RNDWT) AS SumOfRNDWT, Moss_Allports.YEAR,
Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
FROM Moss_Allports
WHERE Moss_Allports IN (SELECT Moss_Allports.DRVID FROM Moss_Allports
WHERE (((Moss_Allports.YEAR)=1981)) AND
(((Moss_Allports.S_N_PC_A)=22)))
AND (((Moss_Allports.YEAR)=1981))
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A,
Moss_Allports.YEAR;


John W. Vinson[MVP]

Thanks John,
That didn't give me any errors, but it also didn't give me any output after
90 minutes.
Rebecac

On Thu, 2 Mar 2006 13:21:29 -0800, sassycat8
<(E-Mail Removed)> wrote:

>
>
>Thanks John,
>That didn't give me any errors, but it also didn't give me any output after
>90 minutes.
>Rebecac

Yow! How big is your table? What fields are indexed? You should have
indexes on DRVID (of course), [Year] and S_N_PC_A; you might also want
to rename the field Year, since that's a reserved word.

You might get better performance with an Inner Join rather than a
Subquery; Access can be inefficient at In() and Exists() queries.
Hmmm... let's see... air code, untested:

SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A,
Sum(Moss_Allports.RNDWT) AS SumOfRNDWT, Moss_Allports.YEAR,
Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
FROM Moss_Allports
INNER JOIN (SELECT M.DRVID FROM Moss_Allports AS M
WHERE M.YEAR=1981 AND M.S_N_PC_A=22) AS qry1981
ON qry1981.DRVID = Moss_Allports.DRVID
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A,
Moss_Allports.YEAR;

This will get the total across all years for any value of DRVID which
satisfies the subquery - not sure that's exactly what you want.

John W. Vinson[MVP]

So I fixed on little thing in the subquery that made it work perfectly.
Access was asking for a parameter in a text box when I tried to run the
query. I went and looked at design view, and for whatever reason it made
sense to me. This is my first ever written SQL query, I am so happy it has
come to fruition. This is the final version that works:

SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
FROM Moss_Allports
WHERE Moss_Allports.DRVID IN (SELECT Moss_Allports.DRVID FROM Moss_Allports
WHERE (((Moss_Allports.YEAR)=1981)) AND
(((Moss_Allports.S_N_PC_A)=22)))
AND (((Moss_Allports.YEAR)=1981))
GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR;
Thank you all so much for your help. This is so exciting!!!!
Rebecca

A month later, and now I need to run this query for 26 years, and I would
like to be able to do that with one query as opposed to changing the year 26
times and later appending 26 tables. Is it possible to generalize this query?
Can I make a box in Access where I input the port and year and out comes a
table that has the information I want? I have no programming experience and
am not sure how to go about dealing with this issue.
Thanks,
Rebecca

"sassycat8" wrote:

> So I fixed one little thing in the subquery that made it work perfectly.
> Access was asking for a parameter in a text box when I tried to run the
> query. I went and looked at design view, and for whatever reason it made
> sense to me. This is my first ever written SQL query, I am so happy it has
> come to fruition. This is the final version that works:
>
> SELECT Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Sum(Moss_Allports.RNDWT)
> AS SumOfRNDWT, Moss_Allports.YEAR, Sum(Moss_Allports.REV_ADJ) AS SumOfREV_ADJ
> FROM Moss_Allports
> WHERE Moss_Allports.DRVID IN (SELECT Moss_Allports.DRVID FROM Moss_Allports
> WHERE (((Moss_Allports.YEAR)=1981)) AND
> (((Moss_Allports.S_N_PC_A)=22)))
> AND (((Moss_Allports.YEAR)=1981))
> GROUP BY Moss_Allports.DRVID, Moss_Allports.S_N_PC_A, Moss_Allports.YEAR;
> Thank you all so much for your help. This is so exciting!!!!
> Rebecca
>