MS Access Distinct Query that returns multiple fileds for each record

[cbenisch]

I have a query in access that pulls fields from multiple tables to pull together information in one place or record set if you will. I then create other queries to use this data set. I need to create a distinct query that will look at all the records and return only those with a distinct File As field. But I want to have it return all fields in each record.

Example



Current Query Coaches

File As First Name Last Name Team Position...
Smith, Bob Bob Smith T1 Head Coach
Doe, John John Doe T3 Head Coach
Doe, Sam Sam Doe T4 Head Coach
Smith, Bob Bob Smith T4 Assistant Coach


Results
Smith, Bob Bob Smith T1 Head Coach
Doe, John John Doe T3 Head Coach
Doe, Sam Sam Doe T4 Head Coach

I know it is something with the Max function and an INTER JOIN from what I have found but I just can't seem to create the correct query and format.

Can anyone help. by giving me an example to query Coaches with the fields shown?

Thank yo in advance.

[ssanfu]

It would really help if you would post the SQL of the current Coach query or the dB...

Is it [File As], [First Name], [Last Name], [Team Position] ??

Object names with spaces in them is really a BAD idea......

[Micron]

...and how many tables are involved and what they look like. There may be an issue such that neither the DISTINCT nor DISTINCT ROW predicates will help, given that the record you seek to eliminate is Bob Smith, who has different team and position values, and you want to eliminate one. One problem is that you provide no insight into why you eliminate the first vs any other records for Bob Smith. You might need to break this into two parts: where you get a list of distinct values based on some portion of the record, then using those record ID's (assuming you have them), get the rest of the fields based on the ID's. However, as noted, I don't grasp what drives the choice to eliminate one or the other.

[cbenisch]

I do realize that spaces are bad. I am working over time to correct those.
This is more of an example. I have many places in the database I would like to use this type of a query. What I am looking to do is create a query that can return a list of records that only has one name where there will be more than one. It does not matter which record is returned for that person but lets say for the example I am looking for the first record with that persons name. In this example I want to do a mail merge to coaches but only want to send one to them even if they coach on multiple teams.

I don't think that it will actually be distinct to get to the distinct result.

Here is an example I found on another forum, but I can't seem to apply it to my example and get it to work so I am looking to get an example of how to writing it with my fields.

SELECT
MAX(IncrementField) AS MaxOfField1,
GroupField
FROM someTable
GROUP BY GroupField

SELECT t.*
FROM someTable AS t
INNER JOIN [previous query] x
ON t.Field1 = x.MaxOfField1 AND
t.GroupField = x.GroupField

I would like to write this as one query with the subquery so it runs as one query.


Thanks for the responses.