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@xxxxxxxxx"
<dan.allen.dc@xxxxxxxxx> wrote:

On Jan 9, 9:36 am, Patrick Fisher <i...@xxxxxxxxxxx> 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" <n...@xxxxxx> wrote:

"Patrick Fisher" <i...@xxxxxxxxxxx> wrote in message
news:qah9o3dfb0qdab6bj952ndnvso408k00va@xxxxxxxxxx

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" <h...@xxxxxxxxx>
wrote:

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

.

Relevant Pages

  • Re: Find Lowest Price in Table
    ... There is only one price per part in each table, ... find the lowest price in each table I am trying to find the lowest ... which are available from each of 12 suppliers, ... a simple query. ...
    (comp.databases.ms-access)
  • Re: Find Lowest Price in Table
    ... it does not isolate the lowest one. ... There is only one price per part in each table, ... which are available from each of 12 suppliers, ... a simple query. ...
    (comp.databases.ms-access)
  • Re: query from linked table
    ... the same as that from a normalized table using a Cross-tab query. ... normalizes the table, and use that query as the source for your subsequent ... SELECT Supplier,, Price ... > I want to produce a query from this linked table showing only suppliers ...
    (microsoft.public.access.externaldata)
  • Re: Find Lowest Price in Table
    ... I have tables from 12 suppliers each of whom can supply the same part, ... suppliers who can supply at the lowest price for each item. ... wise use a union query to combine the 12 tables as you source. ...
    (comp.databases.ms-access)
  • Re: Find Min Value in Row or Selected Cells
    ... lowest price and/or the heading for the lowest price: ... I'm trying to create an expression in a query to find this info for me. ... ProductID ...
    (microsoft.public.access.queries)