Only get records with the best source

[bullseye09]

I have a dataset in Access with with multiple sources. So each item in the data has 1 or more records with different data. I would like to make a query to return only the record for each item with the best data source available. I have made a column in which I've numbered the sources with the lowest number being the best source. Figured I could make something with a MIN() function but haven't been able to get it to work.

So basically I want to take data like this:

Item	SOURCE	Source Rank	Data	MoreData
1	ABC	1	0.41936	0.10836
1	DEF	2	0.15215	0.19984
1	GHI	3	0.69386	0.82899
2	DEF	2	0.90919	0.26663
2	GHI	3	0.50644	0.08439
3	DEF	2	0.79123	0.77338

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

And return this:

Item	SOURCE	Source Rank	Data	MoreData
1	ABC	1	0.41936	0.10836
2	DEF	2	0.90919	0.26663
3	DEF	2	0.79123	0.77338

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

I've been struggling to make a query that does this. Can anyone help?

 

[GiveMeABreak]

You need to use two queries, the first one takes the min of Source Rank for each Item in MasterTable. (MasterTable is the first table in your post.) You can copy and paste this code in the SQL view for a query and save as "01: Get MinOfSource".

SELECT MasterTable.Item, Min(MasterTable.[Source Rank]) AS [MinOfSource Rank]
FROM MasterTable
GROUP BY MasterTable.Item;

The second query joins MasterTable to the query above selecting only the minimum source ranks:

SELECT MasterTable.Item, MasterTable.SOURCE, MasterTable.[Source Rank], MasterTable.Data, MasterTable.MoreData
FROM MasterTable INNER JOIN [01: Get MinOfSource] ON (MasterTable.Item = [01: Get MinOfSource].Item) AND (MasterTable.[Source Rank] = [01: Get MinOfSource].[MinOfSource Rank]);

 

[bullseye09]

That works like a charm. Thanks GiveMeABreak!

 

[GiveMeABreak]

happy to help