Avoiding Ambigous Joins

[dandoescode]

I have constructed a fairly complicated query, by a series of smaller sub queries. Each one references the one before it, which simplified the complexity of the coding, and allowed me to avoid ambiguous joins. I now want format the final query, which is for the user, so that it contains all of the sub queries it relies on. How do I do this? I can provide the code, if that would be useful.

[ajetrumpet]

wow...no idea at all what you just said.

[boblarson]

+1 (I'm also confused as to what you mean?)

[dandoescode]

Okay let me try and clarify. I have several queries Q1,Q2,Q3... where Q2 performs a query based on the results of Q1. These queries culminate in a final query Qf. Is there any way that I can place all of these Q1,Q2,Q3, into Qf. Currently Qf does the job required, but I think that it might be difficult to follow for anyone who wants to look at the work I've done.

[boblarson]

What is the SQL for each? Sorry, but that is about the only way I can tell you definitively whether you can just bypass all of the nested queries and put it all in one. I'm guessing that it is not possible but won't be able to tell without the SQL of the queries.

Just in case you aren't sure how to do that, just go into the query in design view and then from the VIEW tab select SQL View and then copy and paste that string here. Each query will have its own SQL view so you want to provide each of them.

[dandoescode]

Here are the various queries culminating in %W.

W1
SELECT Species.SpeciesID, ProcessedFish.FishID, ProcessedFish.Weight
FROM Species INNER JOIN ProcessedFish ON Species.SpeciesID = ProcessedFish.SpeciesID
WHERE (((Species.CommonName)=[Input CommonName]))
GROUP BY Species.SpeciesID, ProcessedFish.FishID, ProcessedFish.Weight;

W2
SELECT W1.SpeciesID, Diet.FishID, Diet.PreyID, Diet.PreyWeight
FROM W1 INNER JOIN Diet ON W1.FishID=Diet.FishID;

W3
SELECT W2.FishID, W2.PreyID, Sum(W2.PreyWeight) AS SumOfPreyWeight
FROM W2
WHERE ((("FishID")="FishID"))
GROUP BY W2.FishID, W2.PreyID;

W4
SELECT W3.FishID, Sum(W3.SumOfPreyWeight) AS SumOfSumOfPreyWeight
FROM W3
GROUP BY W3.FishID;

W41
SELECT W4.FishID
FROM W4
WHERE (((W4.SumOfSumOfPreyWeight)>0))
GROUP BY W4.FishID;

W5
SELECT Count(W41.FishID) AS CountOfFishID
FROM W41;

W6
SELECT W3.FishID, W3.PreyID, W3.SumOfPreyWeight, W4.SumOfSumOfPreyWeight, W3.SumOfPreyWeight/W4.SumOfSumOfPreyWeight AS Fraction
FROM W3 INNER JOIN W4 ON W3.FishID=W4.FishID;

W7
SELECT W6.PreyID, Sum(W6.Fraction) AS SumOfFraction
FROM W6
GROUP BY W6.PreyID;

%W
SELECT W7.PreyID, W7.SumOfFraction/CountofFishID AS PercentW
FROM W7, W5;

[dandoescode]

I realize this is fairly complicated, it would be more than helpful if you could perhaps just suggest how one might merge the first two queries, I could likely replicate that process from there.