Find Lowest Price in Table

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

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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

Re: Find Lowest Price in Table

Hi
That is Ok for 1 supplier, I may have say a 1000 products, all of
which are available from each of 12 suppliers, some products will be
cheaper from one supplier or another, I need to be able to produce
purchase orders to each suppler detailing the items where they were
the lowest price.

On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
wrote:

>"Patrick Fisher" wrote in message
>news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
>
> Hi
> That is Ok for 1 supplier, I may have say a 1000 products, all of
> which are available from each of 12 suppliers, some products will be
> cheaper from one supplier or another, I need to be able to produce
> purchase orders to each suppler detailing the items where they were
> the lowest price.
>
> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
> wrote:
>
> >"Patrick Fisher" wrote in message
> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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?wp_ml=0
>

You have 12 tables, one for each supplier?

If you can, combine all the tables into one, keyed on a supplier ID. Other
wise use a union query to combine the 12 tables as you source. then uses
Keith's response on the resulting table or query.

Re: Find Lowest Price in Table

I still do not understand how I can get the lowest value from 12
possibles


On Wed, 9 Jan 2008 07:08:42 -0600, "paii, Ron" wrote:

>
>"Patrick Fisher" wrote in message
>news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
>>
>> Hi
>> That is Ok for 1 supplier, I may have say a 1000 products, all of
>> which are available from each of 12 suppliers, some products will be
>> cheaper from one supplier or another, I need to be able to produce
>> purchase orders to each suppler detailing the items where they were
>> the lowest price.
>>
>> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
>> wrote:
>>
>> >"Patrick Fisher" wrote in message
>> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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?wp_ml=0
>>
>
>You have 12 tables, one for each supplier?
>
>If you can, combine all the tables into one, keyed on a supplier ID. Other
>wise use a union query to combine the 12 tables as you source. then uses
>Keith's response on the resulting table or query.
>

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:4tm9o3pe7jiuka067ilkfg1p515agdopi8@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.

Re: Find Lowest Price in Table

On Jan 9, 9:36=A0am, Patrick Fisher wrote:
> I still do not understand how I can get the lowest value from 12
> possibles
>
>
>
> On Wed, 9 Jan 2008 07:08:42 -0600, "paii, Ron" wrote:
>
> >"Patrick Fisher" wrote in message
> >news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
>
> >> Hi
> >> That is Ok for 1 supplier, I may have say a 1000 products, all of
> >> which are available from each of 12 suppliers, some products will be
> >> cheaper from one supplier or another, I need to be able to produce
> >> purchase orders to each suppler detailing the items where they were
> >> the lowest price.
>
> >> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
> >> wrote:
>
> >> >"Patrick Fisher" wrote in message
> >> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@4ax.com...
> >> >> Hi
> >> >> I have tables from 12 suppliers each of whom can supply the same par=
t,
> >> >> 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?wp_ml=3D0
>
> >You have 12 tables, one for each supplier?
>
> >If you can, combine all the tables into one, keyed on a supplier ID. Othe=
r
> >wise use a union query to combine the 12 tables as you source. then uses
> >Keith's response on the resulting table or query.- Hide quoted text -
>
> - Show quoted text -

This is a good example of a simple question where the answer is simple
in principle, but can be awkward in practice.

Question: can you combine all the tables from all the suppliers into
a single table? If you did that, finding the minimum price would be
a simple query.

It sounds like you want to query all 12 tables, without combining them
into a single table. I would be concentrating on combining the tables
instead of trying to read from all of them. Just a personal
preference.

Re: Find Lowest Price in Table

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"
wrote:

>"Patrick Fisher" wrote in message
>news:4tm9o3pe7jiuka067ilkfg1p515agdopi8@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.

Re: Find Lowest Price in Table

On Jan 9, 10:25=A0am, Patrick Fisher wrote:
> 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 =A0or 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"
> wrote:
>
>
>
> >"Patrick Fisher" wrote in message
> >news:4tm9o3pe7jiuka067ilkfg1p515agdopi8@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. =A0If you're not familiar with using the=

> >"totals" button in queries then post back and someone will walk you throu=
gh
> >it. =A0Typically the SQL would look something like:
>
> >Select MySupplierID, Min(PriceField) As MinPrice from tblMyTable
> >Group By MySupplierID
>
> >Keith.- Hide quoted text -
>
> - Show quoted text -

I understand you have 12 tables with the same fields. So, what I am
saying is I would put all the records into one table, something like
this:

insert into masterTable
(field1, field2, ...)
select field1, field 2, ..
from supplier1List

insert into masterTable
(field1, field2, ...)
select field1, field 2, ..
from supplier2List

insert into masterTable
(field1, field2, ...)
select field1, field 2, ..
from supplier3List

..
until you get to 12.

After running all those inserts, masterTable will have data from all
12 suppliers in it. The masterTable would need a column for the
supplierName or id.

To select the lowest price for any given part, you could:

Select top 1 price, supplier
from masterTable
where part=3DpartID
order by price


That will return the one record with the lowest price for that part.

To put all the lowest prices into a table, leaving out the other
records, you could:

select distinct partID
into parts /*makes a list of unique partIDs

select top 1 partID as targetPart from parts /*get first partID
while partID not null
insert into lowestPrices
(supplier, partID, price)
select top 1 supplier, partID, price
from masterTable
where partID =3D targetPart
order by price
select next partID
loop

This will fill the table lowestParts with the lowest price and
supplier for each part.

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:3hp9o31pquas14kg8pdk36evs7ben37718@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"
> wrote:
>
> >"Patrick Fisher" wrote in message
> >news:4tm9o3pe7jiuka067ilkfg1p515agdopi8@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];

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:3hp9o31pquas14kg8pdk36evs7ben37718@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

Re: Find Lowest Price in Table

I have combined the table into a single table.
Running a query as per Rons answer simply lists all the suppliers and
thier paert codes and prices, it does not isolate the lowest one.

There is only one price per part in each table, I am not trying to
find the lowest price in each table I am trying to find the lowest
price accross 12 tables for each part, I do not want to see all the
prices for a ABC123 Widget, just the lowest one with its supplier


On Wed, 9 Jan 2008 07:21:26 -0800 (PST), "dan.allen.dc@gmail.com"
wrote:

>On Jan 9, 9:36 am, Patrick Fisher wrote:
>> I still do not understand how I can get the lowest value from 12
>> possibles
>>
>>
>>
>> On Wed, 9 Jan 2008 07:08:42 -0600, "paii, Ron" wrote:
>>
>> >"Patrick Fisher" wrote in message
>> >news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
>>
>> >> Hi
>> >> That is Ok for 1 supplier, I may have say a 1000 products, all of
>> >> which are available from each of 12 suppliers, some products will be
>> >> cheaper from one supplier or another, I need to be able to produce
>> >> purchase orders to each suppler detailing the items where they were
>> >> the lowest price.
>>
>> >> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
>> >> wrote:
>>
>> >> >"Patrick Fisher" wrote in message
>> >> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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?wp_ml=0
>>
>> >You have 12 tables, one for each supplier?
>>
>> >If you can, combine all the tables into one, keyed on a supplier ID. Other
>> >wise use a union query to combine the 12 tables as you source. then uses
>> >Keith's response on the resulting table or query.- Hide quoted text -
>>
>> - Show quoted text -
>
>This is a good example of a simple question where the answer is simple
>in principle, but can be awkward in practice.
>
>Question: can you combine all the tables from all the suppliers into
>a single table? If you did that, finding the minimum price would be
>a simple query.
>
>It sounds like you want to query all 12 tables, without combining them
>into a single table. I would be concentrating on combining the tables
>instead of trying to read from all of them. Just a personal
>preference.

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:qr0ao3dkapbq309b401h18s8af00ji053d@4ax.com...
> I have combined the table into a single table.
> Running a query as per Rons answer simply lists all the suppliers and
> thier paert codes and prices, it does not isolate the lowest one.
>
> There is only one price per part in each table, I am not trying to
> find the lowest price in each table I am trying to find the lowest
> price accross 12 tables for each part, I do not want to see all the
> prices for a ABC123 Widget, just the lowest one with its supplier
>
>
> On Wed, 9 Jan 2008 07:21:26 -0800 (PST), "dan.allen.dc@gmail.com"
> wrote:
>
> >On Jan 9, 9:36 am, Patrick Fisher wrote:
> >> I still do not understand how I can get the lowest value from 12
> >> possibles
> >>
> >>
> >>
> >> On Wed, 9 Jan 2008 07:08:42 -0600, "paii, Ron" wrote:
> >>
> >> >"Patrick Fisher" wrote in message
> >> >news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
> >>
> >> >> Hi
> >> >> That is Ok for 1 supplier, I may have say a 1000 products, all of
> >> >> which are available from each of 12 suppliers, some products will be
> >> >> cheaper from one supplier or another, I need to be able to produce
> >> >> purchase orders to each suppler detailing the items where they were
> >> >> the lowest price.
> >>
> >> >> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
> >> >> wrote:
> >>
> >> >> >"Patrick Fisher" wrote in message
> >> >> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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?wp_ml=0
> >>
> >> >You have 12 tables, one for each supplier?
> >>
> >> >If you can, combine all the tables into one, keyed on a supplier ID.
Other
> >> >wise use a union query to combine the 12 tables as you source. then
uses
> >> >Keith's response on the resulting table or query.- Hide quoted text -
> >>
> >> - Show quoted text -
> >
> >This is a good example of a simple question where the answer is simple
> >in principle, but can be awkward in practice.
> >
> >Question: can you combine all the tables from all the suppliers into
> >a single table? If you did that, finding the minimum price would be
> >a simple query.
> >
> >It sounds like you want to query all 12 tables, without combining them
> >into a single table. I would be concentrating on combining the tables
> >instead of trying to read from all of them. Just a personal
> >preference.
>
Take a 2nd look at the query, it should have a min() on the price and GROUP
BY , Vendor ID and Part Number.

Re: Find Lowest Price in Table

Thanks for all your suggestions but I have managed to achieve the
result I wanted in code, notwithstanding that I would love to see
aquery that could do it as it would probably run faster.

On Wed, 9 Jan 2008 12:31:53 -0600, "paii, Ron" wrote:

>
>"Patrick Fisher" wrote in message
>news:qr0ao3dkapbq309b401h18s8af00ji053d@4ax.com...
>> I have combined the table into a single table.
>> Running a query as per Rons answer simply lists all the suppliers and
>> thier paert codes and prices, it does not isolate the lowest one.
>>
>> There is only one price per part in each table, I am not trying to
>> find the lowest price in each table I am trying to find the lowest
>> price accross 12 tables for each part, I do not want to see all the
>> prices for a ABC123 Widget, just the lowest one with its supplier
>>
>>
>> On Wed, 9 Jan 2008 07:21:26 -0800 (PST), "dan.allen.dc@gmail.com"
>> wrote:
>>
>> >On Jan 9, 9:36 am, Patrick Fisher wrote:
>> >> I still do not understand how I can get the lowest value from 12
>> >> possibles
>> >>
>> >>
>> >>
>> >> On Wed, 9 Jan 2008 07:08:42 -0600, "paii, Ron" wrote:
>> >>
>> >> >"Patrick Fisher" wrote in message
>> >> >news:qah9o3dfb0qdab6bj952ndnvso408k00va@4ax.com...
>> >>
>> >> >> Hi
>> >> >> That is Ok for 1 supplier, I may have say a 1000 products, all of
>> >> >> which are available from each of 12 suppliers, some products will be
>> >> >> cheaper from one supplier or another, I need to be able to produce
>> >> >> purchase orders to each suppler detailing the items where they were
>> >> >> the lowest price.
>> >>
>> >> >> On Wed, 9 Jan 2008 10:32:42 -0000, "Keith Wilby"
>> >> >> wrote:
>> >>
>> >> >> >"Patrick Fisher" wrote in message
>> >> >> >news:2159o3d9scs80rdqnkafds4n87ch8kba0a@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?wp_ml=0
>> >>
>> >> >You have 12 tables, one for each supplier?
>> >>
>> >> >If you can, combine all the tables into one, keyed on a supplier ID.
>Other
>> >> >wise use a union query to combine the 12 tables as you source. then
>uses
>> >> >Keith's response on the resulting table or query.- Hide quoted text -
>> >>
>> >> - Show quoted text -
>> >
>> >This is a good example of a simple question where the answer is simple
>> >in principle, but can be awkward in practice.
>> >
>> >Question: can you combine all the tables from all the suppliers into
>> >a single table? If you did that, finding the minimum price would be
>> >a simple query.
>> >
>> >It sounds like you want to query all 12 tables, without combining them
>> >into a single table. I would be concentrating on combining the tables
>> >instead of trying to read from all of them. Just a personal
>> >preference.
>>
>Take a 2nd look at the query, it should have a min() on the price and GROUP
>BY , Vendor ID and Part Number.
>

Re: Find Lowest Price in Table

"Patrick Fisher" wrote in message
news:qr0ao3dkapbq309b401h18s8af00ji053d@4ax.com...
>I have combined the table into a single table.



> 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?