Duplicate names

[Legends77]

Really hope someone can help as this is a mess.

Here is the SQL from the query:
SELECT FlightMonday.Player, FlightMonday.Team
FROM FlightMonday INNER JOIN FlightMondayRanks ON (FlightMonday.Team = FlightMondayRanks.Team) AND (FlightMonday.OverorUnder = FlightMondayRanks.MinOfOverorUnder)
GROUP BY FlightMonday.Player, FlightMonday.Team;

Background..
There are 4 "flights" with 4 people per flight. The above works great to pull the name associated with the lowest score for each flight if there are no ties.
I.E.
John - A
Bob - B
Sam - C
Bill - D

However, if John and Paul are in the same flight and each have the score of -3, the results are
John - A
Paul - A
Bob - B
Sam - C
Bill - D

This really throws all my queries and reports off. Is there away to "combine" the names to show the winner as "John & Paul"?

Know this may seem sketchy, but I am describing the best I can, if anyone thinks they can help I will do my best as I do need to get this fixed in one way or another.

Thanks in advance for any and all help!

 

[Legends77]

I have found a work around but need help in formatting a report..... and may should start a new thread as my original may have been too confusing, but thought I would try before starting something new

Field: [Player] in the detail section of a report
Field: [UO] in the detail section of the report
box name: text.45 (which is =min([UO]) in the Team footer section of the report.

Is there away to say...
If UO = text.45 then
player = bold and underlined

I have tried many different combinations in different Events but nothing seems to be working...

this way I would not need to list the winners name as they would be bold and underlined in the detail.


Again, any help is greatly appreciated!

 

[MajP]

assume that your above query is called "qryScores" with data like

firstName	Score
Pete	       A
James	       A
Mike	       B
George	       B
Mary	       B
Bob	       C

function

Public Function combineNames(scoreVal As String) As String
  'put your names here
  Const domain = "qryScores"
  Const fldName = "firstName"
  Const fldScore = "score"
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "SELECT " & fldName & ", " & fldScore & " from " & domain & " WHERE " & fldScore & " = '" & scoreVal & "'"
 'MsgBox strSql
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  Do While Not rs.EOF
    If combineNames = "" Then
      combineNames = rs.Fields(fldName)
    Else
      combineNames = combineNames & " & " & rs.Fields(fldName)
    End If
    rs.MoveNext
 Loop
  
 End Function

Sql for next query

SELECT DISTINCT qryScores.Score, combineNames([Score]) AS Combined
FROM qryScores

out put
Score Combined
A Pete & James
B Mike & George & Mary
C Bob

 

[Legends77]

Thank you very much!!!
Working on inserting now.

Hate to ask as you have helped me a great deal already, but would you happen to know the fix for the formatting?

 

[MajP]

Use conditional formatting, then it will also work with a continous form.

set the condition

field value is
equal to
[text45]