Combining multiple queries into one and a few other questions...

[mpreston14]

Thanks again for your help over the last few months, I have found this forum to be worth its weight in gold!



I have a referee database that works very well in storing match data and producing the data that we need. The problem is that it is disjointed. I need a way of combining all of the data into a useful output and cant seem to make it work. My explanation below might be all over the place... hopefully you will understand what I need help with

I have three queries that I would like to merge into one query or report even if that is the better solution.
They are:

1) A ranking query that ranks the refs by the total points they have accumulated
2) Highest level match as a referee
3) Highest level match as an assistant referee

The referee's each have an ID that ties them to the data, so in theory (in my mind anyways) it should be easy in design view to just line all the data up in the order I want, but that isn't working. However, right now I have to export to excel and manually match up the three queries into a useful format for me.

Question 1: What is the best way to achieve this?



The Ranking query is a "summary query" in that it totals all of the matches, calculates the score and produces one record per referee. The Highest Level Match queries each produce a list of matches per referee by a calculated difficulty score. If a referee has officiated more than one match that gives the same difficulty score then I get two records (or more).

Question 2: How can I limit the query(s) output to only one record per referee, using the match date (most recent) as the actual value to choose if there is a tie?

I think I need to solve question 2 before the combined Query (question1) will work?

Thanks for your help!

Mike

I can try to upload screen captures of the data as it is now if that helps...

[mpreston14]

I was able to sort of get what I was looking for, but it is duplicating many of the records. I think it has to do with "ties" in the difficulty rating. Here is the SQL from the query.

SELECT Referee_T.RefID, Referee_T.LastName, Referee_T.FirstName, [Grade 8 Top 100 with Bonus applied].Total_Matches, [Grade 8 Top 100 with Bonus applied].[Total Points], [Grade 8 Top 100 with Bonus applied].FitTestBonus, [Report REF].[Highest Ref Assignment ID], [Report REF].[Highes Ref Difficulty Score], [Report REF].[Highes Ref League], [Report REF].[Highest Ref Match], [Report REF].GameDate, [Report AR].[Highest AR Assignment ID], [Report AR].[Highes AR Difficulty Score], [Report AR].[Highes AR League], [Report AR].[Highest AR Match], [Report AR].GameDate
FROM ([Report REF] INNER JOIN ([Report AR] INNER JOIN (Referee_T INNER JOIN Officiated_Matches_T ON Referee_T.RefID = Officiated_Matches_T.RefID) ON [Report AR].RefID = Referee_T.RefID) ON [Report REF].RefID = Referee_T.RefID) INNER JOIN [Grade 8 Top 100 with Bonus applied] ON Referee_T.RefID = [Grade 8 Top 100 with Bonus applied].RefID;


The first, second, and third ref each returned in rows 3x the total matches, the 4th didn't have any tied values and returned 13 rows that matched total number of matches. What can I do to change this so it only returns one row per referee with the desired data?

Thanks!

[mpreston14]

Here is the output for the first ref in the query's results, I hope it might help to explain what is going on:

RefID	LastName	FirstName	Total_Matches	Total Points	Highest Ref Assignment ID	Highes Ref Difficulty Score	Highes Ref League	Highest Ref Match	Report REF.GameDate	Highest AR Assignment ID	Highes AR Difficulty Score	Highes AR League	Highest AR Match	Report AR.GameDate
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXX	YYYY	12	797.36	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013

[mpreston14]

Sorry, this is turning more into a blog than a post...

I changed it to "Totals" query and that reduced the total output, but there are still duplicated rows per referee. Now I only get a corresponding row count to the number of "ties" in difficulty value.

RefID	LastName	FirstName	Total_Matches	Total Points	FitTestBonus	Highest Ref Assignment ID	Highes Ref Difficulty Score	Highes Ref League	Highest Ref Match	Report REF.GameDate	Highest AR Assignment ID	Highes AR Difficulty Score	Highes AR League	Highest AR Match	Report AR.GameDate
445	XXXXX	YYYYY	12	797.355	1.128	861	22	Kohls Cup	GU15	8/24/2013	8	210	SNESC	MENS OPEN - M	8/17/2013
445	XXXXX	YYYYY	12	797.355	1.128	861	22	Kohls Cup	GU15	8/24/2013	47	210	SNESC	MENS OPEN - M	8/25/2013
445	XXXXX	YYYYY	12	797.355	1.128	861	22	Kohls Cup	GU15	8/24/2013	86	210	SNESC	MENS OPEN - M	9/7/2013
86	AAAA	BBBB	11	700.25	1	821	72	NEP	GU11	9/8/2013	36	210	SNESC	MENS OPEN - M	8/24/2013
86	AAAA	BBBB	11	700.25	1	821	72	NEP	GU11	9/8/2013	75	210	SNESC	MENS OPEN - M	9/1/2013
86	AAAA	BBBB	11	700.25	1	821	72	NEP	GU11	9/8/2013	79	210	SNESC	MENS OPEN - M	9/7/2013
374	CCCCC	DDDDD	14	550	1	833	72	NEP	GU11	9/8/2013	72	210	SNESC	MENS OPEN - M	8/31/2013
374	CCCCC	DDDDD	14	550	1	833	72	NEP	GU11	9/8/2013	85	210	SNESC	MENS OPEN - M	9/7/2013
611	EEEEE	FFFFFF	13	460.1025	1.089	846	80	Maple	BU13	9/8/2013	845	67.5	Maple	GU14	9/8/2013
589	GGGGG	HHHHH	16	292.6125	1.02	842	50	Maple	GU12	9/8/2013	840	67.5	Maple	GU14	9/8/2013
589	GGGGG	HHHHH	16	292.6125	1.02	842	50	Maple	GU12	9/8/2013	997	67.5	Maple	GU14	9/15/2013

Thanks as always!

[Dal Jeanis]

I would suggest two things - first, aliasing your tables so that you can read the SQL easier. Once aliased and structured, your current query looks like this.

SELECT 
   RT.RefID, 
   RT.LastName, 
   RT.FirstName, 
   G8.Total_Matches, 
   G8.[Total Points], 
   G8.FitTestBonus, 
   RR.[Highest Ref Assignment ID], 
   RR.[Highes Ref Difficulty Score], 
   RR.[Highes Ref League], 
   RR.[Highest Ref Match], 
   RR.GameDate, 
   RAR.[Highest AR Assignment ID], 
   RAR.[Highes AR Difficulty Score], 
   RAR.[Highes AR League], 
   RAR.[Highest AR Match], 
   RAR.GameDate
FROM 
  ([Report REF] AS RR
   INNER JOIN 
      ([Report AR] AS RAR
       INNER JOIN 
          (Referee_T AS RT
           INNER JOIN 
           Officiated_Matches_T AS OT
           ON RT.RefID = OT.RefID) 
       ON RAR.RefID = RT.RefID) 
   ON RR.RefID = RT.RefID) 
INNER JOIN 
   [Grade 8 Top 100 with Bonus applied] AS G8
ON RT.RefID = G8.RefID;

Second, to get rid of your duplicates, you will use GROUP BY and an aggregate function, probably either MAX or FIRST, whichever is appropriate. MAX gets you the highest of something that is found in the group of records, and FIRST gives you the first one encountered. (There's a MIN, AVG, LAST and so on as well.)

I'd need to see the SQL for the queries [Report REF] and [Report AR] to know where you need them. If you needed to use the exact game that had the highest difficulty level, you'd use FIRST to have Access pick one of them at random. If you just needed the highest difficulty level, but didn't need to know which game it was, then you'd use MAX against all the calculated difficulties for each ref.

[mpreston14]

Thank you Dal Jeanis! The duplicates are gone! I used Last to give me the latest match.

I'll work on the Alias piece, I see how it will help read in the future.

I have one piece left, and that is if the Referee has never been a center ref and has only been an assistant, I need a way of still displaying the ref's detail even if null. How do I choose "Is or Is Not Null" for a field?

Thanks again!

[mpreston14]

I ended up creating several temp tables and corresponding queries to get the specific detail I needed. When I tried to build just one table I would get the data but it wouldn't be linked to the actual correct Assignment ID. Because I was trying to get too much on the output at once, due to First/Last/Min/Max I was getting mixed up data that didn't correspond. So I built it out in 3 queries/tables for each the Ref matches and the AR matches.

That enabled me to create the list but it still wouldn't list those Ref's that hadn't done both Center and AR Position....then I had an a-ha moment and changed the Joins from Inner to Left and BINGO it worked perfectly! Thanks for all of your help!

Mike

[Dal Jeanis]

Good job! Breaking down the total query into functional building blocks is one of the critical skills to develop in this industry.

Making those building blocks functional, simple and understandable is the key.