Query For Skins

[cbenisch]

I have an access database I am creating to run a golf tournament. In it there is a table that will contain a Team ID and the score for each of 18 holes as TeamID, Hole1, Hole2, Hole3... as records. What I would like to do is to create a query that will show me the records with the TeamID for each hole that has a unique score (a Skin) thus it will need to look at each column (Hole1 for example) and see if there is a unique score and if so display the record if there was no unique record display nothing. I need it to repeat this for each "Hole" column until it checks all 18 hole columns. Output would be something like:

Team1 Hole 1
Team5 Hole 8



I expect there to be very few records returned.

I would appreciate any thoughts on how to go about creating this of a query? I don't know if it can be done in one query or will need to be a series of quires with a union query to display the final results.

Thank you in advance if you would like to take a shot at helping me. I play with access like I play golf...not a pro at either.

[June7]

Each hole is a column? Non-normalized data.

Don't understand what you mean by 'TeamID for each hole that has a unique score'.

A UNION query would be like:

SELECT TeamID, 1 AS HoleID, Hole1 AS Score FROM tablename
UNION SELECT TeamID, 2, Hole2 FROM tablename
UNION SELECT TeamID, 3, Hole3 FROM tablename
...;

[cbenisch]

In each record there is data:

TeamID, Hole1,Hole2,Hole3...Hole18
1,3,4,5,3...4
2,3,4,4,2...4
3,3,4,5,3...4
4,3,4.4,3...4

The teamID is a number as are all the scores.

A skin occurs when there is one score for a hole that is the lowest and unique for that hole. In other words on a hole with a par of 4 and someone shoots a 2 that might be unique because the likely scores are 3 or above. I want to run a query that looks at all the scores for each hole and determines if there is a skin. In the example above team 2 had a skin on hole 4. There could be multiple skins in a round up to 18 (1 for each hole). The likelihood though is one or two maybe in 18 holes.

I would like the output of the report to be

Team 2 Hole 4

There would be more rows in the report if there were more than one skin.

Hope this explains better what I am trying to accomplish.

Thanks

[June7]

Still not clear.

What would be the output dataset if in your example team 4 had a score of 2 at Hole3 as well as the score of 2 for team 2 at Hole4?

[cbenisch]

If Team 4 had a score of 2 on hole 3 then we would get

Team 2 Hole 4
Team 4 Hole 3

As long as the score is the lowest for that hole and unique to the dataset for that hole it is considered a skin.

To extend it, if Team 3 had a 3 on hole 18 rather than a 4 it would be

Team 2 Hole 4
Team 4 Hole 3
Team 3 Hole 18

A skin is the lowest unique score for the hole. If there are no unique scores for a hole there is no skin for that hole. When I do this with a spreadsheet I just sort lowest to highest on each hole and look for a unique score. I figure there has to be a way to get access to do the same thing.

Thanks for taking a look at this.

[June7]

Can you provide db so I have a good dataset to test with? Follow instructions at bottom of my post. This might need VBA code.

[cbenisch]

I am building it now. I will upload it once I get the dataset built and will identify the teams and holes that should be skins.

Thanks

[cbenisch]

I left out one more thing as well. In the team table there is a field for if they are in the skins contest or not. There will be two tables in the database one with the scores and one with the team info that is linked to the scores via the Team ID.

[cbenisch]

Attaching DB and spreadsheet file showing records. Skins are highlighted. I guess I really don't care what the output format is as long as I can easily identify the skins like the highlighted cells in the spreadsheet.

In case you don't have excel or a reader available the skins are Team 2 hole 2, Team 6 hole 11, Team 6 hole 12 and Team 15 Hole 18.


Thanks again for taking a look at this.

[June7]

Well, here it is. I did do a query route. Not sure VBA would be any simpler:

First query: ScoresUNION
SELECT ScoreID, TeamID, 1 AS HoleID, Hole1 AS Score FROM Score
UNION SELECT ScoreID, TeamID, 2, Hole2 FROM Score
UNION SELECT ScoreID, TeamID, 3, Hole3 FROM Score
UNION SELECT ScoreID, TeamID, 4, Hole4 FROM Score
UNION SELECT ScoreID, TeamID, 5, Hole5 FROM Score
UNION SELECT ScoreID, TeamID, 6, Hole6 FROM Score
UNION SELECT ScoreID, TeamID, 7, Hole7 FROM Score
UNION SELECT ScoreID, TeamID, 8, Hole8 FROM Score
UNION SELECT ScoreID, TeamID, 9, Hole9 FROM Score
UNION SELECT ScoreID, TeamID, 10, Hole10 FROM Score
UNION SELECT ScoreID, TeamID, 11, Hole11 FROM Score
UNION SELECT ScoreID, TeamID, 12, Hole12 FROM Score
UNION SELECT ScoreID, TeamID, 13, Hole13 FROM Score
UNION SELECT ScoreID, TeamID, 14, Hole14 FROM Score
UNION SELECT ScoreID, TeamID, 15, Hole15 FROM Score
UNION SELECT ScoreID, TeamID, 16, Hole16 FROM Score
UNION SELECT ScoreID, TeamID, 17, Hole17 FROM Score
UNION SELECT ScoreID, TeamID, 18, Hole18 FROM Score;

Second query: HolesMIN
SELECT ScoresUNION.HoleID, Min(ScoresUNION.Score) AS MinOfScore
FROM ScoresUNION
GROUP BY ScoresUNION.HoleID;

Third query: ScoresCOUNT
SELECT ScoresUNION.HoleID, ScoresUNION.Score, Count(ScoresUNION.Score) AS CountOfScore
FROM ScoresUNION
GROUP BY ScoresUNION.HoleID, ScoresUNION.Score
HAVING (((Count(ScoresUNION.Score))=1));

Fourth query: HolesSKINS
SELECT ScoresCOUNT.HoleID, ScoresCOUNT.Score
FROM HolesMIN INNER JOIN ScoresCOUNT ON HolesMIN.HoleID = ScoresCOUNT.HoleID
WHERE (((ScoresCOUNT.Score)=[MinOfScore]));

Fifth query: TeamHoleSKINS
SELECT ScoresUNION.TeamID, Team.TeamName, ScoresUNION.HoleID, ScoresUNION.Score
FROM (HolesSKINS INNER JOIN ScoresUNION ON (HolesSKINS.ScoresCOUNT.Score = ScoresUNION.Score) AND (HolesSKINS.HoleID = ScoresUNION.HoleID)) INNER JOIN Team ON ScoresUNION.TeamID = Team.TeamID;

[cbenisch]

Thank you very much for this. I am pretty sure I may never have gotten to this on my own. It would appear from this that changing how I store the scores would have eliminated the first query. I will have to look at some of the other reports I want to do as well but they likely would be easier too if I changed the scores format to that of the first query.

It is great that skilled persons such as yourself take the time to help newbies such as myself. This will make me a better user of the software and for that I thank you as well. I only hope that someone helps you in your time of need as well in the future no matter what that need may be.