By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Find Lowest Price in Table

P: n/a
Hi
I have tables from 12 suppliers each of whom can supply the same part,
I need to be able to create a table or query containing a list of
suppliers who can supply at the lowest price for each item. I am
struggling with this, can anyone help.
Thanks
Patrick
Jan 9 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Patrick Fisher" <in**@psoftuk.comwrote in message
news:21********************************@4ax.com...
Hi
I have tables from 12 suppliers each of whom can supply the same part,
I need to be able to create a table or query containing a list of
suppliers who can supply at the lowest price for each item. I am
struggling with this, can anyone help.
Thanks
Patrick
Use a totals query (click on the "totals" toolbar button), "group on" both
supplier and item, "min" on the price field.

Keith.
www.keithwilby.com

Jan 9 '08 #2

P: n/a
"Patrick Fisher" <in**@psoftuk.comwrote in message
news:4t********************************@4ax.com...
>I still do not understand how I can get the lowest value from 12
possibles
By grouping on the unique supplier ID and setting the query to return the
minimum value for that grouping. If you're not familiar with using the
"totals" button in queries then post back and someone will walk you through
it. Typically the SQL would look something like:

Select MySupplierID, Min(PriceField) As MinPrice from tblMyTable
Group By MySupplierID

Keith.

Jan 9 '08 #3

P: n/a

I can't be you so it must be me not making my question clear lets
start again:

I have 12 tables each containing up to 1000 parts the only difference
in these table is that the price field will or may be different, I
need to produce a table containing one of each of the parts with the
lowest price out of all 12 tables together with the relevant supplier
name.

Your answers suggest to me that you are assuming that there is only
one table or am I misunderstanding.

On Wed, 9 Jan 2008 14:52:13 -0000, "Keith Wilby" <he**@there.com>
wrote:
>"Patrick Fisher" <in**@psoftuk.comwrote in message
news:4t********************************@4ax.com.. .
>>I still do not understand how I can get the lowest value from 12
possibles

By grouping on the unique supplier ID and setting the query to return the
minimum value for that grouping. If you're not familiar with using the
"totals" button in queries then post back and someone will walk you through
it. Typically the SQL would look something like:

Select MySupplierID, Min(PriceField) As MinPrice from tblMyTable
Group By MySupplierID

Keith.
Jan 9 '08 #4

P: n/a

"Patrick Fisher" <in**@psoftuk.comwrote in message
news:3h********************************@4ax.com...
>
I can't be you so it must be me not making my question clear lets
start again:

I have 12 tables each containing up to 1000 parts the only difference
in these table is that the price field will or may be different, I
need to produce a table containing one of each of the parts with the
lowest price out of all 12 tables together with the relevant supplier
name.

Your answers suggest to me that you are assuming that there is only
one table or am I misunderstanding.

On Wed, 9 Jan 2008 14:52:13 -0000, "Keith Wilby" <he**@there.com>
wrote:
"Patrick Fisher" <in**@psoftuk.comwrote in message
news:4t********************************@4ax.com...
>I still do not understand how I can get the lowest value from 12
possibles
By grouping on the unique supplier ID and setting the query to return the
minimum value for that grouping. If you're not familiar with using the
"totals" button in queries then post back and someone will walk you
through
it. Typically the SQL would look something like:

Select MySupplierID, Min(PriceField) As MinPrice from tblMyTable
Group By MySupplierID

Keith.
You really should have only 1 table with a vendor ID field. But you can
simulate this with a union query. Add a union for each vendor with a
calculate field for the vendor ID.

SELECT DISTINCT "V01" AS VendorID, tblPartsVendor01.[PART],
tblPartsVendor01.Price FROM tblPartsVendor01;

UNION ALL SELECT DISTINCT "V02" AS VendorID, tblPartsVendor02.[PART],
tblPartsVendor02.Price FROM tblPartsVendor02;
....

UNION ALL SELECT DISTINCT "V03" AS VendorID, tblPartsVendor03.[PART],
tblPartsVendor03.Price FROM tblPartsVendor03;

Then use the union query as the source to your selection query, Assume the
above query is name qryPartsVendor

SELECT qryPartsVendor.VendorID, qryPartsVendor.[PART],
Min(qryPartsVendor.Price) AS MinOfPrice
FROM qryPartsVendor
GROUP BY qryPartsVendor.VendorID, qryPartsVendor.[PART];
Jan 9 '08 #5

P: n/a
"Patrick Fisher" <in**@psoftuk.comwrote in message
news:3h********************************@4ax.com...
>

Your answers suggest to me that you are assuming that there is only
one table or am I misunderstanding.
Yes and no. The answer, as Ron states, is to either use a union query or
combine the 12 tables into one.

Regards,
Keith.
www.keithwilby.com

Jan 9 '08 #6

P: n/a
"Patrick Fisher" <in**@psoftuk.comwrote in message
news:qr********************************@4ax.com...
>I have combined the table into a single table.
<snip>
There is only one price per part in each table
???

Ron's query will work, are you sure you have Min and Group By in the
appropriate fields and all of the data in one place?

Jan 10 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.