Minimum Price Query

By AEmerson ·
I have two tables one with purchase history and one with offer prices from suppliers. Both are linked by a Product ID Primary Key.

I want to identify in the offer prices from suppliers which is the minimum price offered for each Product ID.

I'm pretty new at using databases and have been trying to write a basic query in Access but can't get anywhere.

I am using Access 2003.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Hope this helps you out......

by Peconet Tietokoneet In reply to Minimum Price Query
Collapse -

Something like

by Tony Hopkinson In reply to Minimum Price Query

It's not basic this one

do a query for minimum price
select ProductID,Min(OfferPrice) From ProductOffers group By productID

The join that to Products and ProuctOffers by ProductID and OfferPrice to get Product and Vendor information back

If two vendors are offering at the same price both will come out.

Is one way, there are some others.

Look up how to do it from the front end you probably know more about that than me anyway.

Best way to approach this sort of thing is to do it in stages and then look at how (and if) you can themn make it more efficient.

Related Discussions

Related Forums