CODEWITHSUNDEEP
sqlms-access-2010
user3541923
user3541923

Reputation: 9

MSACCESS SQL Distinct Query

I am using MS Access 2010 and am stuck. have a table with two columns FileID and Name for which there can be multiple names for a FileID. I need to obtain the distinct Name, and a FileID for each Name. I do not care which FileID is returned as long as one is returned.

Table:
FileID, Name
1, John
1, John
2, John
3, Simon
4, David

I would expect the result of
3, Simon
4, David
and either 1, John or 2, John.

Would really really appreciate any advice on how this can be done in a query.

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can do this with an aggregation function:

select name, min(fileid) as fileid
from [table] t
group by name;

Access also supports first() and last(). So, first() will get the first value encountered (as opposed to the minimum value):

select name, first(fileid) as fileid
from [table] t
group by name;

Upvotes: 1

Noah
Noah

Reputation: 1857

Let me know if this works for you:

SELECT FileID, Name FROM [table] WHERE [conditions] GROUP BY Name;

This will return only 1 FileID, and return a record for every unique Name

Upvotes: 0

Related Questions