Structuring Subqueries

[dandoescode]

Written the following master query called abundance, which calculates the abundance of a certain species per year. It relies on two other queries C2 and C1. I would like to know if there is a simple way to write Abundance so that the entirety of the query is contained in just one file, rather than three. I have provided a brief summary of each query followed by the sql code.



C1, takes data from the tables Species and CatchSummary, and does a search for a given species by name. It then provides a listing of all catches and associated event numbers.




SELECT Species.SpeciesID, CatchSummary.EventNum, CatchSummary.TotCt
FROM Species INNER JOIN CatchSummary ON Species.SpeciesID=CatchSummary.SpeciesID
WHERE (((Species.CommonName)=[Enter CommonName]))
GROUP BY Species.SpeciesID, CatchSummary.EventNum, CatchSummary.TotCt;


C2 takes catches for the given species and associates them with a given cruise and station by event number.

SELECT C1.SpeciesID, C1.EventNum, Station.CruiseID, C1.TotCt
FROM Station INNER JOIN C1 ON Station.EventNum = C1.EventNum;


Abundance takes data from C2 and Cruise and sums the catch by year.

SELECT Cruise.Year, Sum(C2.TotCt) AS [Count]
FROM C2 INNER JOIN Cruise ON C2.CruiseID = Cruise.CruiseID
GROUP BY Cruise.Year;


Thanks for the help.

[June7]

You can build nested queries in the SQL View window.

So where Abundance has FROM C2 put:

FROM (SELECT C1.SpeciesID, C1.EventNum, Station.CruiseID, C1.TotCt FROM Station INNER JOIN C1 ON Station.EventNum = C1.EventNum) AS C2

And were query C2 has INNER JOIN C1 put:

INNER JOIN (the c1 sql without semicolon) AS C1