Subquery re team members scores

[Bhoc]

Hello all I am hoping someone may help me:banghead:. I may be totally off mark with this but I believe that a sub query may help me sort this out. As part of a golf database upgrade I have added team numbers to players in a table called players. This table also collects the scores of each player.
The query posted below places all the teams from each club together and then shows the team with lowest score (Top 1 only being shown) I only want the top one to show BUT I need the query to ensure that all the members of the team (4 in all) have scores against their name. If not ignore that team. In the result shown of my query you will see that there are only 2 persons shown in this result as the other two members of the team did not have a score that day.

What the query does is groups the team together first, ignores the ones without any Nett score against their name and then displays the team with the lowest total score.

SELECT TOP 1 Players.[Team Number CBB], Sum(TeamTotalCBB([clubteam],[Team Number CBB])) AS TotalCBB, Players.Fullname, Players.clubteam, Players.[Nett Score Day 1], Players.NCRday1, Players.Scratched
FROM Players
GROUP BY Players.[Team Number CBB], Players.Fullname, Players.clubteam, Players.[Nett Score Day 1], Players.NCRday1, Players.Scratched
HAVING (((Players.[Team Number CBB])>0) AND ((Players.[Nett Score Day 1])>0) AND ((Players.NCRday1)=False) AND ((Players.Scratched)=False)) OR (((Players.[Team Number CBB])>0) AND ((Players.[Nett Score Day 1])>0) AND ((Players.NCRday1)=False) AND ((Players.Scratched)=False))
ORDER BY Sum(TeamTotalCBB([clubteam],[Team Number CBB]));

I hope this explains it
All the data required comes from one table called players and is based around the fields teamnumberCBB, Clubteam and TotalCBB.

I can paste a cut down version of the db if required

 

[Ranman256]

you want an OUTER JOIN.
join tables , tPlayers to tScores, set the join to OUTER join,
bring fields down tPlayers.PlayerID, tPlayers.Name, tScore.PlayerID
set criteria for tScore.PlayerID = IS NULL

this will show what players have no score.

 

[plog]

BUT I need the query to ensure that all the members of the team (4 in all)

If you have set your table up correctly (and I'm not sure you have), then you should be able to verify that with a new HAVING clause based on the COUNT. If you have exactly 4 records, then you know that you have the right amount of data.

But like I said, I'm not convinced your table is set up properly. Can you demonstrate your issue with data? Provide 2 sets:

A. Starting sample data from Players. Include field names and enough data to cover all cases.

B. Expected results of A. Show what you hope your query shows based on the data you provide in A.

 

[Bhoc]

Plog and Ranman thanks to both of you but I don't think I have explained myself clearly on what this is
Each year a large group of female members of several golf clubs get together over a three day tournament. They play for numerous trophies and in numerous events. The greater majority of these events are all based on their individual play and scores. The team side of it is ancillary whereby they nominate to be in a team representing their club they are registered with. These are teams of 4 (they do not play together) and there could be up to 6 teams for each club. Not everyone in the competition is part of the teams also. To work out the winner for the team competition each of the members that are linked to a team have their individual scores tallied together and the winner is the four that have the lowest combined score. This is auto0matically done by the database along with numerous other calculations that occur over the three days.

Plog in answer to your question re the table it has numerous fields but the fields that relate to this team competition are:
ID
FullName
clubteam
Team Number CBB
NCR Day 1
Nett Score day 1

My original query grabs these details and groups the individual scores into the team from the team club example
Joan Smith, Bega, 2, False, 89
Mary Jones, Bega, 2, False, 87
Helen Hill, Bega, 2, False, (no score entered)
Colleen Watson, Bega, 2, False, (no score entered)
The total for this team (the query works out ) would be 176 BUT they can't win as the last two have not entered any score which to be eligible they must. and so on for the over 200 persons who play and have a team number.
In an ideal situation no one would fail to enter a score but some refuse to because they had a lousy round etc.

What the problem is if all the four members of a particular team do not enter scores then we get a false result as the team score would be too low as all four scores (no zeros) must count towards the total to be eligible for the team event.

What I need to happen is for the query after grouping into clubteam and team numbers to ignore any team that does not have at least four members all with a score entered.

I do run a similar query to give me a report for all the team results and have the report not show the teams with less than four results. This is done by hiding each groupheader etc that does not have the appropriate four scores. This same report would be fine to illustrate the winner only if I could get it to only show the first of the eligible teams BUT in using my original query shown it still shows the lowest aggregate score for a team that does not have four scores entered.

I have uploaded a pdf copy of the reports I mention above. The one with the title winner does not hide the groupheaders etc. because if I do with my query nothing shows up as the one showing is the top record.
The winner report needs to show the first record as shown on the other report

I hope this better explains the problem - In Ranman's reply I can easily find the players that have not scores that is not the problem.

I apologise if I am confusing you and if you can't help I can fully understand.

 

[JHB]

..
I can paste a cut down version of the db if required

Then do that.

 

[plog]

I apologise if I am confusing you and if you can't help I can fully understand.

I can fully understand if you give me data to demonstrate. 2 sets:

A. Starting sample data from Players. Include field names and enough data to cover all cases.

B. Expected results of A. Show what you hope your query shows based on the data you provide in A.


If you can post the database do that--including just the tables necessary. Then also provide the data you expect the query to return based on the data in those tables. Don't explain it, show me with data.

 

[Bhoc]

I have uploaded a cut down version of the database. This includes 2 reports - the query I am talking about is only used for these reports. The one Titled Cox Balmain Bush and Stewart shows the total field for the team playing that comp with the teams that did not have four individual results shown hidden by a VBA expression. The other report with ending winner needs to show the winner of the teams competition as per the first team shown on the first report. By asking the query to only show the top value I end up with a team that has only 2 results - if I hide these as per the other report I end up with a blank page. I may be way off the mark by using a query but help:banghead::banghead::banghead:

 

[JHB]

Try it now, open the reports "JHB Cox Balmain Bush & Stewart" and "JHB Cox Balmain Bush & Stewart Winner"

 

[Bhoc]

JHB
thanks that is fantastic exactly what I needed

 

[JHB]

Good that it could help you, good luck.