Limiting to first instance

[collyrium]

Okay, here's my problem. It involves two tables: Teams, Captains. There is a one to many relationship from Teams to Captains.

Here's the problem every time I create a query to show the teams and list the captains that belong those teams I get double listings or triple listings depending on how many captains each team has.

How do I limit the query to only show the first instance of the captain with a team. I only need to see one captain.

I can't limit by unique values, because technically each combination of team and captain is unique. I've tried grouping by but I don't get anything useful (i'm not sure I'm using it correctly though)

Any suggestions would be much appreciated. Thank you in advance.

 

[crosmill]

Not sure if this will work in Access but if you go into the SQL of the query and just after "SELECT" type "TOP 1"

To make sure you don't just get a ramdom Captain for each team you need to make sure that their ranked in some way.......

 

[raskew]

Try:

SELECT Distinct ....

 

[reel knotty]

are you sure your relationships are set up right? It sounds like it should be one to many from capts to teams not one to many teams to capts.

 

[collyrium]

Thanks Everyone!!!

It turns out limiting it by First() worked. I was having problems when I tried it because I was asking for the First(TeamID) when I should have been asking for the First(Captain).

Thanks again for everyone's input!