Union Query to show all items and duplicates by lowest price?

[gypsyman]

Im a bit of a "newby" but I have the following UNION ALL query in Access 2000:

SELECT [upc], [price], [qavl]
FROM [query1]

UNION ALL SELECT [upc], [price], [qavl]
FROM [query2];

Where upc is a unique 12 digit identifier for each item, price is the price,
and qavl is the quantity.

This UNION ALL combines all items from two of my queries. However, there are
some duplicates in this UNION ALL. What I would like to do is tell the query
to show all items and that when there is a duplicate only show the one with
the lowest price.

Adding to the code would be awesome but any help is greatly appreciated.

 

[Michel Walsh]

Hi,


Make two queries. The first one, you already have. The second could be like:

SELECT upc, MIN(price)
FROM savedQuery1
GROUP BY upc



Hoping it may help,
Vanderghast, Access MVP