This is a question about recommended approach and syntax.
I have a sports-management program in which swimmers are grouped into teams of four, with a team pace. I need Access to determine the team pace for each team based on the slowest member of each team.
Specifically, I have a Participants table with each participant's pace and the name of each participant's team, and a Paces lookup table of sorted and color coded paces. I need Access to compare the participant paces on a team, choose the slowest pace on thta team (which has the lowest index# on the pace lookup table) and return the color code for that pace, such as "Green". (This team would then be one of the Green teams.)
That data value is then written to a Teams table that lists all teams by color.
Where do I start? Thx.
Here is a linear straight forward approach: Create a view that returns the team name and maximum pace using the MAX function using the Participants table; GROUP BY team name. Then create a second view that joins the first view to your Paces look up table WHERE MAX pace is BETWEEN the minimum and maximum values for a color.