Complicated Query/Subquery

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[John Vinson]

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]

 

[Guest]

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

 

[John Vinson]

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]

 

[Guest]

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

 

[Guest]

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

 

[strive4peace]

Hi Rebecca,

'-----------------------
Sub LookAtYears()
Dim s As String, mYear As Integer

For mYear = 1981 To 2000
s = "SELECT A.DRVID, A.S_N_PC_A, " _
& " Sum(A.RNDWT) AS SumOfRNDWT, " _
& " A.YEAR, Sum(A.REV_ADJ) AS SumOfREV_ADJ " _
& " FROM Moss_Allports AS A " _
& " WHERE A.DRVID IN " _
& "(SELECT B.DRVID FROM Moss_Allports as B " _
& " WHERE (B.YEAR=" & mYear & ") " _
& " AND (B.S_N_PC_A=22))" _
& " GROUP BY A.DRVID, A.S_N_PC_A, A.YEAR;"

'do something with the SQL ... look at it?
'this is not SQL to make a table

'print SQL to the debug window
debug.print s

Next mYear

End Sub
'-----------------------


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug
window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar,
first icon)
3. cut the SQL statement from the debug window (select, CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where
the problem is in the SQL



After you gather the data, what do you want to do with it?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Thanks so much for getting back to me. I need this to output a table of data
so that I can trun stats on it. I think I have figured out a way to do it
with stored procedures and the original query. I am now reading about stored
procedures and Access. I really appreciate your time.
Have a good day!
Rebecca