Return ID of Lowest Cost

[Matthew Pfluger]

I am trying to build a query that returns the lowest part cost from a set of
quotes. The database is organized so that each Quote can have multiple
Prices, where a Price is a part cost at a given quantity and year.

I also have a Bill of Material table (tblBoM) that stores Part Numbers and
Quantities.

To get the cheapest cost, I run a query on tblPrices that has the following
criteria:
PartNumber = [each part number in tblBoM]
PriceQty <= [qty of part # in tblBoM] (we can't order 10 at the qty 100
price)
QuoteYear = 2009
ReceiveDate <= 3/1/2009 (Date we need the parts)

Now here's the challenge. For each Price record that meets these criteria,
I want to find the cheapest cost and return the PriceID. Then, I need to run
these steps for each Part Number in the BoM.

The query successfully finds the Prices that match the criteria, but I can't
get it to return the correct PriceID. I turned on the Totals Row for the
query, set each of the filter criteria fields to Where, and set the total for
the Cost field to Min.

I don't know what to do with the PriceID field. If I set it to Group By, it
returns each record that matches the filter criteria since each record has a
unique PriceID. If I set it to First/Last, it does not return the correct
PriceID. I tried nesting queries, but that dramatically increases processing
time since it has to return matching Price records and then total and THEN
filter.

I even tried running the Prices through a separate query that sorts all
Prices by Cost before running the 4 filters. I thought that if I did this
and set the PriceID total row to First, it would return the correct PriceID
since Prices would be sorted with Costs. This still didn't work.

Is there a way that I can filter for the Minimum cost and return the correct
PriceID all within the same query? Would I be able to use a Totals Row
expression of some sort?

I know this is long-winded, but I appreciate your help.
Matthew Pfluger

 

[Allen Browne]

Matthew, I haven't gone through this in detail, but could you solve this
just omitting the GROUP BY clause (an all reference to Min, First, etc), and
instead *sorting* the query on the resultant price?

That should give you the lowest price first, and the PriceID of the row that
generated that price.

If that's not suitable, you may be able to do it with a subquery using
techniques like this:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I am trying to build a query that returns the lowest part cost from a set
of
quotes. The database is organized so that each Quote can have multiple
Prices, where a Price is a part cost at a given quantity and year.

I also have a Bill of Material table (tblBoM) that stores Part Numbers and
Quantities.

To get the cheapest cost, I run a query on tblPrices that has the
following
criteria:
PartNumber = [each part number in tblBoM]
PriceQty <= [qty of part # in tblBoM] (we can't order 10 at the qty 100
price)
QuoteYear = 2009
ReceiveDate <= 3/1/2009 (Date we need the parts)

Now here's the challenge. For each Price record that meets these
criteria,
I want to find the cheapest cost and return the PriceID. Then, I need to
run
these steps for each Part Number in the BoM.

The query successfully finds the Prices that match the criteria, but I
can't
get it to return the correct PriceID. I turned on the Totals Row for the
query, set each of the filter criteria fields to Where, and set the total
for
the Cost field to Min.

I don't know what to do with the PriceID field. If I set it to Group By,
it
returns each record that matches the filter criteria since each record has
a
unique PriceID. If I set it to First/Last, it does not return the correct
PriceID. I tried nesting queries, but that dramatically increases
processing
time since it has to return matching Price records and then total and THEN
filter.

I even tried running the Prices through a separate query that sorts all
Prices by Cost before running the 4 filters. I thought that if I did this
and set the PriceID total row to First, it would return the correct
PriceID
since Prices would be sorted with Costs. This still didn't work.

Is there a way that I can filter for the Minimum cost and return the
correct
PriceID all within the same query? Would I be able to use a Totals Row
expression of some sort?

I know this is long-winded, but I appreciate your help.
Matthew Pfluger

 

[Matthew Pfluger]

Allen,

Thanks for your response. After reading your suggestion, I went back to my
favorite reference, Access 2002 Desktop Developer's Handbook, and re-read the
SQL chapter. I came across the section on the TOP predicate, and realized
that I could perform the query in the following steps:

1. Filter the Prices for the four parameters.
2. Of the matching price quotes, sort by cost.
3. Use the TOP predicate to return only the TOP 1 records (i.e. PriceID).
4. Take the SQL code from steps 1-3 and make it a subquery.

This method worked very well and far faster than I had anticipated. I
appreciate the nudge in the right direction.

Thanks,
Matthew

Matthew, I haven't gone through this in detail, but could you solve this
just omitting the GROUP BY clause (an all reference to Min, First, etc), and
instead *sorting* the query on the resultant price?

That should give you the lowest price first, and the PriceID of the row that
generated that price.

If that's not suitable, you may be able to do it with a subquery using
techniques like this:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I am trying to build a query that returns the lowest part cost from a set
of
quotes. The database is organized so that each Quote can have multiple
Prices, where a Price is a part cost at a given quantity and year.

I also have a Bill of Material table (tblBoM) that stores Part Numbers and
Quantities.

To get the cheapest cost, I run a query on tblPrices that has the
following
criteria:
PartNumber = [each part number in tblBoM]
PriceQty <= [qty of part # in tblBoM] (we can't order 10 at the qty 100
price)
QuoteYear = 2009
ReceiveDate <= 3/1/2009 (Date we need the parts)

Now here's the challenge. For each Price record that meets these
criteria,
I want to find the cheapest cost and return the PriceID. Then, I need to
run
these steps for each Part Number in the BoM.

The query successfully finds the Prices that match the criteria, but I
can't
get it to return the correct PriceID. I turned on the Totals Row for the
query, set each of the filter criteria fields to Where, and set the total
for
the Cost field to Min.

I don't know what to do with the PriceID field. If I set it to Group By,
it
returns each record that matches the filter criteria since each record has
a
unique PriceID. If I set it to First/Last, it does not return the correct
PriceID. I tried nesting queries, but that dramatically increases
processing
time since it has to return matching Price records and then total and THEN
filter.

I even tried running the Prices through a separate query that sorts all
Prices by Cost before running the 4 filters. I thought that if I did this
and set the PriceID total row to First, it would return the correct
PriceID
since Prices would be sorted with Costs. This still didn't work.

Is there a way that I can filter for the Minimum cost and return the
correct
PriceID all within the same query? Would I be able to use a Totals Row
expression of some sort?

I know this is long-winded, but I appreciate your help.
Matthew Pfluger