Finding second on a ist

[JohnLouisWood]

I am running a query (of a query) where 4 values fit the criteria. I know that I can use FIRST and LAST to find 2 of the values. How can I find the other values, and work with them. My report prints the First([Field]), but do I 'find' the second value? I know in other places I could do First(NotFirst([Field])), but that does not seem to be a choice in ACCESS.
I'm sure that I'm not the first to ask this question, but I did not see where.

[Gicu]

First and Last are based on the sort order, usually Min/Max are more dependable. One way to get your seconds would be to use a subquery to get your first and use <> (Select FIRST (YourField) From tblYourTable) in the criteria row of the First (YourField) of the main query.
Cheers,

[JohnLouisWood]

Looks like a beautiful dog. The Min and Max idea seems to have answered my other question - different thread.
What happens to my subquery if there is only 1 value to start with? I make query v2.3 and then call the Min in v4, just like I am now doing to v2. With 4 values like I now have, it should 'catch' my second value. But what happens if I had only 1 value to start with? My MIN, First call would get what result?

[Gicu]

Would return no results as expected (the subquery would return the only value as the first/min and the query itself would return no results as there are no others different than that).
Cheers,

[JohnLouisWood]

But that seems to create a problem: Query_v2 has 4 records (seasons with 15 wins), Q_v4 is a single record finding the MIN and MAX from Q_v2
Q_v2_3 strips the MIN and MAX from Q_v2, thus 2 records, and Q_v5 then is a single record showing the MIN, 2nd, 3rd and MAX from Q_v2 (2nd and 3rd are MIN and MAX from Q_v2_3)
And when I create Q_v2_7 which strips MIN and MAX from Q_v2_3, it has 0 records, as we expected. But this is necessary since the next time the team wins 15 games, it will be the 5th non-consecutive season of 15 wins.
The problem is that when I try to run/create Q_v6 to show Min, 2nd, 3rd, 4th and MAX, I get no record at all. I was expecting a single record as I had in Q_v4 and Q_v5 with a 'blank field'.
This happens even if I just include Q_v2_7 in the FROM statement (without any SELECT)

[JohnLouisWood]

The 'other' concern is where should my report(s) Look at. Right now I am fine looking at Q_v5 for what I need, but how does the report automatically go to Q_v6 when the team wins 15 games the next time.
Also most of my other reports use a different cutoff (not 15 wins) so will they 'automatically' look at Q_v4. How will they know where to look (Source code for the (sub-)report). I'm in the process of using/creating SWITCH statements to tell the report how to narrate the seasons, single or consecutive, and how many sets.

[Gicu]

Hard to say without seeing your data and\or expected output, but I think you should look into building a ranking query instead of these nested queries (to show the blank field you need to change the join type from equi join to outer join - with the arrows pointing from the one with most records towards to ones with less).
http://allenbrowne.com/ranking.html
Cheers,

[JohnLouisWood]

Because of the way the module populates my table(s), the FallY values are already ranked. I thought maybe that I should be using a JOIN expression to get Q_v2_7 with 0 present records to work for Q6. Well Q6 works fine withOUT Q_v2_7 (does not include any info). When I tried to put a JOIN in Q6, I got a JOIN expression not supported error. And this was using 2 of the queries from Q5.
Q5 SQL:
SELECT Min(A.Total15ws) AS Tot15ws, Min(A.Max_cws) AS Mx_cws, Count(B![CountY]) AS Cnt_Max, Min(B.FallY) AS Fir_Fall, Min(C.FallY) AS 2nd_Fall, Max(C.FallY) AS 5th_Fall, Max(B.FallY) AS Lst_Fall, Fir_Fall & "-" & Right([Fir_Fall]+1,2) AS Fir_Seas, [2nd_Fall] & "-" & Right([2nd_Fall]+1,2) AS 2nd_Seas, [5th_Fall] & "-" & Right([5th_Fall]+1,2) AS 5th_Seas, Lst_Fall & "-" & Right([Lst_Fall]+1,2) AS Lst_Seas, Min(B.FallN) AS Fir_End, Min(C.FallN) AS 2nd_End, Max(C.FallN) AS 5th_End, Max(B.FallN) AS Lst_End, Fir_End-1 & "-" & Right([Fir_End],2) AS F_E_Seas, [2nd_End]-1 & "-" & Right([2nd_End],2) AS 2_E_Seas, [5th_End]-1 & "-" & Right([5th_End],2) AS 5_E_Seas, Lst_End-1 & "-" & Right(Lst_End,2) AS L_E_Seas
FROM Res_15cws_v3_Query AS A, Res_15cws_v2_Query AS B, Res_15cws_v2_3_Query AS C
WHERE (((B.CountY)=[A].[Max_cws]))
GROUP BY A.Total15ws, A.Max_cws;

And Q6 SQL trying to include JOIN:
SELECT Min(A.Total15ws) AS Tot15ws, Min(A.Max_cws) AS Mx_cws, Count(B![CountY]) AS Cnt_Max, Min(B.FallY) AS Fir_Fall, Min(C.FallY) AS 2nd_Fall, Max(C.FallY) AS 5th_Fall, Max(B.FallY) AS Lst_Fall, Fir_Fall & "-" & Right([Fir_Fall]+1,2) AS Fir_Seas, [2nd_Fall] & "-" & Right([2nd_Fall] + 1,2) AS 2nd_Seas, [5th_Fall] & "-" & Right([5th_Fall] + 1,2) AS 5th_Seas, Lst_Fall & "-" & Right([Lst_Fall]+1,2) AS Lst_Seas, Min(B.FallN) AS Fir_End, Min(C.FallN) AS 2nd_End, Max(C.FallN) AS 5th_End, Max(B.FallN) AS Lst_End, Fir_End-1 & "-" & Right([Fir_End],2) AS F_E_Seas, [2nd_End] - 1 & "-" & Right([2nd_End],2) AS 2_E_Seas, [5th_End] - 1 & "-" & Right([5th_End],2) AS 5_E_Seas, Lst_End-1 & "-" & Right(Lst_End,2) AS L_E_Seas


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].[Max_cws]))
GROUP BY A.Total15ws, A.Max_cws;

[Gicu]

Do you have a mix of join types? You should ensure they are all left outer joins.
Cheers,

[JohnLouisWood]

I only have the 1 JOIN expression at this time. When I changed LEFT to INNER, it ran but as expected it only ran for the 2 matching records, not the 4 records from Q_v2. When I use OUTER instead of INNER, I get a syntax error because of OUTER

[JohnLouisWood]

How can I get data from several queries? The problem is that Q_v2_7 has no records at this time. At this time, anytime I include Q_v2_7 in the FROM statement, I get no records at all. My other desired queries are 1 record each. I want some data from each of them, and was hoping that I could have a blank field at this time from the Q_v2_7 field(s), until they are populated in the future. I am expecting my 'new' query to have 1 record that I can use to narrate a sub-report.

[JohnLouisWood]

I am expecting to use this query and sub-report as a template for multiple other sub-reports.

[JohnLouisWood]

Is it legitimate for Report - Data - Source to have this statement? SWITCH (Count(Q_v2_7.(Cws_ID)>0, Q_9, Count([Q_v2_3]![Cws_ID]>0, Q_8, true, Q_7)

[Gicu]

A report has a record source which can be a table, query or a SQL statement, what you have is an expression (which would work for a calculated field). Can you show a screenshot of the query where you attempted to use the left outer join in design view?

[JohnLouisWood]

I did the LEFT JOIN in Post #8. 2 SQL's, first runs fine, and I think the 2nd is the 'same' except I tried to use LEFT JOIN.