Finding second on a ist

[JohnLouisWood]

When I tried SWITCH in the (sub)report for the source I got an error message telling me that I had to select a (1) table or query for my source.

[Gicu]

That is what I said in post #14...
Can you show the query with the left join (a screen shot, not SQL)?
Cheers,

[JohnLouisWood]

Here are 2 screenshots: First SQL:
SELECT Min(A.Tot_ws) AS Tot15ws, Min(A.Mx_cws) AS Mx_cws, Count(B![CountY]) AS Cnt_Mx, Min(B.FallY) AS Fir_Fall, Min(C.FallY) AS 2nd_Fall, Max(C.FallY) AS 5th_Fall, Max(B.FallY) AS Lst_Fall, Min(B.FallN) AS Fir_End, Min(C.FallN) AS 2nd_End, Max(C.FallN) AS 5th_End, Max(B.FallN) AS Lst_End
FROM Res_15cws_v3_Query AS A, Res_15cws_v2_Query AS B LEFT JOIN Res_15cws_v2_3_Query AS C ON B.[Cws_ID] = C.[Cws_ID]
WHERE (((B.CountY)=[A].[Mx_cws]))
GROUP BY A.Tot_ws, A.Mx_cws;
and Second SQL:
SELECT Min(A.Tot_ws) AS Tot15ws, Min(A.Mx_cws) AS Mx_cws, Count(B![CountY]) AS Cnt_Mx, Min(B.FallY) AS Fir_Fall, Min(C.FallY) AS 2nd_Fall, Max(C.FallY) AS 5th_Fall, Max(B.FallY) AS Lst_Fall, Min(B.FallN) AS Fir_End, Min(C.FallN) AS 2nd_End, Max(C.FallN) AS 5th_End, Max(B.FallN) AS Lst_End
FROM Res_15cws_v3_Query AS A, Res_15cws_v2_Query AS B INNER JOIN Res_15cws_v2_3_Query AS C ON B.[Cws_ID] = C.[Cws_ID]
WHERE (((B.CountY)=[A].[Mx_cws]))
GROUP BY A.Tot_ws, A.Mx_cws;
The only difference Second SQL has INNER join while First SQL has LEFT join. I tried LEFT OUTER join - same result.

[Gicu]

Please show the design view of the query with the left join (before you attempt to run it).

[JohnLouisWood]

That's what I had planned also.

[Gicu]

Can't see the join from A to B? Does A return just one record? Save the A and B as a separate query then build the final one based on that and add C with the left outer join.
Cheers,

[JohnLouisWood]

There is no join between A and B.
Just a moment ago when I tried to close Res_15cws_v5_LJ_Query, it would not let me close until I had changed to INNER JOIN or no join at all.
I had to go back in (Res_15cws_v5_Query) and remove the join between B and C in order to get all 4 values that I had/needed from FallY. The INNER JOIN restricted the records to only 2. I'm thinking actually eliminated my first and last values.
This scares me, in that I know that there are other queries - from the past that I used LEFT JOIN. Will these now be corrupted/not able to function/wrong results?

[JohnLouisWood]

I would send you my complete database to the forum, but when I tried previously it was WAY too large.

[Gicu]

The lack of join between A and B is the reason you cannot have the left join. So please try what I suggested in the previous post (create an intermediary query with just A and B then use that to build the final one by adding C with the left join pointing towards it).
If you want to send me the db please zip it and upload it here or to my email on my website.
Cheers,

[JohnLouisWood]

Query A has no common fields with B or C. I did LEFT JOIN B and C as a query v5_1, and then added Query A to make v5_2, and it seems to work fine.
I also set a zipped file of my database to your e-mail.

[Gicu]

That's great, that is what I meant, you need to either group A and B then C or B and C then A to avoid having a mix of no join/left join in the same query.
Please let me know if you still want me to look at your file.
Cheers,

[Gicu]

Edit: deleted - duplicate post

[JohnLouisWood]

Thank you very much!! At this point I am good. Later I will use this set of queries as templates for the other queries and sub-reports. Again thank you very much for your help.

[JohnLouisWood]

I'm finding that by doing the LEFT JOIN first, that I can then have the blank fields. This will mean that (I think) I will be able to look (My sub-report) at a single query whether printing a season, or several seasons, or several consecutive seasons. Because of my switch statements. I was having a problem because my switch statement was causing ACCESS to look for values of that the report was not using.
Hopefully this will make ACCESS happy.

[Gicu]