Union All reverting to ID numbers

[Clivios]

Hi

I am quite new to access and trying to learn more.

I have created a database for my hobby of being an (American) football official. I have one table of officials and another for matches to track the matches that I have worked. There are 3-7 positions that officials can work in any one match.

I want a query that will tell me which officials I have worked with and in how many matches (in total regardless of position). I have created a union all query to give me a list of officials names each time they appear. Rather than giving me the result as the names it comes back as the ID numbers from the Officials table.

The SQL query is:
SELECT Ref FROM Matches WHERE Ref Is Not Null
UNION ALL
SELECT Umpire FROM Matches WHERE Umpire Is Not Null
UNION ALL
SELECT LineJudge FROM Matches WHERE LineJudge Is Not Null
UNION ALL
SELECT HeadLine FROM Matches WHERE HeadLine Is Not Null
UNION ALL
SELECT BackJudge FROM Matches WHERE BackJudge Is Not Null
UNION ALL
SELECT SideJudge FROM Matches WHERE SideJudge Is Not Null
UNION ALL SELECT FieldJudge FROM Matches WHERE FieldJudge Is Not Null;

The field used in each position is a full name field ([Surname] & ", " & [Name])

Any thoughts?

 

[BigHappyDaddy]

Just looking at your query and field names, it appears that there could be a better database design.

It appears that your Match table includes one field for each official position. I would remove those fields from that table and create a OfficialPostion table and a Match/OfficialPosition/OfficialID table.

OfficialPosition could be like:
ID Position
1 Referee
2 Umpire
3 Back Judge
etc

The Match/OfficialPosition/OfficialID table relates Match to Official and Official to the Position he/she covered in the Match
MatchID OfficialID OfficialPositionID
1 4 1
1 5 2
1 8 3
2 6 1
2 4 2
2 5 3

This is a more complex table structure, but it will make any queries you want to write much simplier.

 

[Clivios]

Thanks for that... it makes sense now you've said it!

Cheers

 

[Clivios]

Hi BigHappyDaddy

just signed up to this forum and can't seem to reply to your message... so I'm in the UK so none of the levels you mentioned; we're not brought up on football here so I'm guessing our standard of officiating is much lower than in the states!

 

[BigHappyDaddy]

Hi Clivios,

No worries! I am just a fan of the sport and I was curious. I played in high school and knew a official in the college ranks that wanted to become an official in the NFL.

Luck to you in your project and hobby!

 

[MSAccessRookie]

Hi

I am quite new to access and trying to learn more.

I have created a database for my hobby of being an (American) football official. I have one table of officials and another for matches to track the matches that I have worked. There are 3-7 positions that officials can work in any one match.

I want a query that will tell me which officials I have worked with and in how many matches (in total regardless of position). I have created a union all query to give me a list of officials names each time they appear. Rather than giving me the result as the names it comes back as the ID numbers from the Officials table.

The SQL query is:
SELECT Ref FROM Matches WHERE Ref Is Not Null
UNION ALL
SELECT Umpire FROM Matches WHERE Umpire Is Not Null
UNION ALL
SELECT LineJudge FROM Matches WHERE LineJudge Is Not Null
UNION ALL
SELECT HeadLine FROM Matches WHERE HeadLine Is Not Null
UNION ALL
SELECT BackJudge FROM Matches WHERE BackJudge Is Not Null
UNION ALL
SELECT SideJudge FROM Matches WHERE SideJudge Is Not Null
UNION ALL SELECT FieldJudge FROM Matches WHERE FieldJudge Is Not Null;

The field used in each position is a full name field ([Surname] & ", " & [Name])

Any thoughts?

While BigHappyDaddy makes an excellent point, you might be able to get your existing structure to work for you. One problem that you are having is that each of the Queries uses a different Field name for its data, which prevents them from being related after the Dataset is completed.

Below is an example of how to add an Alias that changes the Field Name.

Change "SELECT FieldJudge FROM"
To "SELECT FieldJudge As MatchOfficial FROM"

Once this has been done, all rows in the returned Dataset will have a Field called MatchOfficial which should be able to be used to group, count, or otherwise evaluate the results.

-- Rookie