A
am157
Guest
I have a query which I am trying to make return the status of an injured player. This status is essentially the worst status (highest number) of any of the players injuries. The problem I have is that the injury status is updated each time the injury is treated and records of each update need to be kept. Hence I need to only select the most recent status of each injury and then select the worst of them.
The query I currently have is:
SELECT [Injury Treatment].Injury_ID, [Injury Treatment].[Treatment Date], [Injury Treatment].[Treatment Time], Status.[Status ID]
FROM Status INNER JOIN ([Injury Treatment] INNER JOIN [Player Injury] ON [Injury Treatment].Injury_ID = [Player Injury].Injury_ID) ON Status.Status = [Injury Treatment].Status
WHERE ((([Player Injury].ID)=[Forms]![PlayerProfile]![Player]))
ORDER BY [Injury Treatment].[Treatment Date] DESC , [Injury Treatment].[Treatment Time] DESC;
How can I make this ignore duplicate values in the Injury_ID field?
I intend to use a seperate query to extract the worst status from this result.
I know I can use an append query but I'd much rather do it in a select query if at all possible.
Hope this isn't too confusing.
Cheers
Andy
The query I currently have is:
SELECT [Injury Treatment].Injury_ID, [Injury Treatment].[Treatment Date], [Injury Treatment].[Treatment Time], Status.[Status ID]
FROM Status INNER JOIN ([Injury Treatment] INNER JOIN [Player Injury] ON [Injury Treatment].Injury_ID = [Player Injury].Injury_ID) ON Status.Status = [Injury Treatment].Status
WHERE ((([Player Injury].ID)=[Forms]![PlayerProfile]![Player]))
ORDER BY [Injury Treatment].[Treatment Date] DESC , [Injury Treatment].[Treatment Time] DESC;
How can I make this ignore duplicate values in the Injury_ID field?
I intend to use a seperate query to extract the worst status from this result.
I know I can use an append query but I'd much rather do it in a select query if at all possible.
Hope this isn't too confusing.
Cheers
Andy
