"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];
