Displaying data if potentially null

[mpreston14]

I have a Soccer/Football Referee database that works very well so far, but I am having a bit of trouble with the final report (via a query) that will pull all of the data together in one place.

Thanks to everyone here I was able to get the query to work in general, but I have a problem with Null values.

Here is the output:

RefID LastName FirstName Matches Age Grade Total Points FitTestBonus Highest Ref League Highest Ref Match Highest Ref Final Highest AR League Highest AR Match Highest AR Final 445 XXX YYYYY 18 17 8 2412.087 1.128 RIWSL WOMENS OPEN - F No RISL O30/O40 - M No 86 CCCC DDDDD 19 21 8 2108.75 1 Super Liga BU12 No SNESC MENS OPEN - M No 374 EEEE FFFFF 19 19 8 1545.3 1 Super Liga BU12 No Super Liga BU12 No 611 GGGGGG HHHHHH 14 17 8 1279.0305 1.089 Super Liga GU10 No Maple BU13 No

In the above examples everything displays correctly, however if a Referee has not been a Center Ref (displayed as Ref above) or has not been an Assistant Ref (displayed as AR above) they do not appear on the query output.

So how can I define the query to include Ref SSSSS TTTTT who hasnt been a Center Ref and only an Assistant Ref in the output:

RefID

LastName

FirstName

Matches

Age

Grade

Total Points

FitTestBonus

Highest Ref League

Highest Ref Match

Highest Ref Final

Highest AR League

Highest AR Match

Highest AR Final

445

XXXX

YYYYY

18

17

8

2412.087

1.128

RIWSL

WOMENS OPEN - F

No

RISL

O30/O40 - M

No

86

AAAAA

BBBBB

19

21

8

2108.75

1

Super Liga

BU12

No

SNESC

MENS OPEN - M

No

374

CCCCC

DDDD

19

19

8

1545.3

1

Super Liga

BU12

No

Super Liga

BU12

No

611

EEEEE

FFFFF

14

17

8

1279.0305

1.089

Super Liga

GU10

No

Maple

BU13

No

393

SSSSS

TTTTTT

4

22

8

400.33

1.109

Null

Null

Null

Super Liga

GU14

No

SQL is below. I haven't aliased the tables or queries yet, hopefully not so horrible to read.

SELECT Referee_T.RefID, Referee_T.LastName, Referee_T.FirstName, Referee_T.Town, Referee_T.Gender, Count(Officiated_Matches_T.AssignmentID) AS Total_Matches, Int((Now()-[DOB])/365.25) AS Age, Referee_T.Grade, Sum([Officiated_Matches_T].Points*Referee_T.FitTestBonus) AS [Total Points], Referee_T.FitTestBonus, [Highest Ref Detail].League AS [Highest Ref LEague], [Highest Ref Detail].Match AS [Highest Ref Match], [Highest Ref Detail].[Final/Semi-Final] AS [Highest Ref Final], [Highest AR Detail].League AS [Highest AR League], [Highest AR Detail].Match AS [Highest AR Match], [Highest AR Detail].[Final/Semi-Final] AS [Highest AR Final]
FROM ((Referee_T INNER JOIN Officiated_Matches_T ON Referee_T.RefID = Officiated_Matches_T.RefID) INNER JOIN [Highest Ref Detail] ON Referee_T.RefID = [Highest Ref Detail].RefID) INNER JOIN [Highest AR Detail] ON Referee_T.RefID = [Highest AR Detail].RefID
GROUP BY Referee_T.RefID, Referee_T.LastName, Referee_T.FirstName, Referee_T.Town, Referee_T.Gender, Int((Now()-[DOB])/365.25), Referee_T.Grade, Referee_T.FitTestBonus, [Highest Ref Detail].FirstOfAssignmentID, [Highest Ref Detail].League, [Highest Ref Detail].Match, [Highest Ref Detail].[Final/Semi-Final], [Highest AR Detail].FirstOfAssignmentID, [Highest AR Detail].League, [Highest AR Detail].Match, [Highest AR Detail].[Final/Semi-Final]
HAVING (((Int((Now()-[DOB])/365.25))<25) AND ((Referee_T.Grade)=8))


ORDER BY Sum([Officiated_Matches_T].Points*Referee_T.FitTestBonus) DESC;


Many thanks for your help!

Mike

[June7]

Maybe:
Do a query for the Center refs. Do a query for Assistant Refs. Join both to a master dataset of all refs, join type "Show all records from {master dataset} ..."

[mpreston14]

Thanks June7, I will try that.

Anyone else think this is possible?

[mpreston14]

Actually that gives me the same problem, if the ref didn't do a Center Ref they don't appear on list.

The list started out as a ranking list, giving us the top ranked referees. When we post the ranking data, we wanted to also list their highest Center Ref and AR matches.

So when I run the list, it still needs to rank the referees by their points, but then display the data associated with each referee.

Thanks!!

[mpreston14]

Something I think that might make it easier to understand is that the "Position" Table (and field) has 3 possible entries, Referee, AR, and 4th (for 4th Official). Every Assignment ID is linked to a Referee and his or her position. So the field Position is never actually Null, it is always one of the 3 entries and therefore not really "Null".

With that noted, if a Referee doesn't have an entry in the table as any of those positions, how do I still list them in the Ranking Query?

[mpreston14]

It was the JOINS! I switched the joins from Inner to Left between the temp tables I used to pull out the specific match data and it worked like a charm!

Thanks again for all of your help!

Mike