Access query on multiple prices

[mjrob]

I have the following 2 tables linked by PartIndex which is unique in the Item table.
Item:
ItemNo
ItemDesc
PartIndex

Pricing:
MinQty
MaxQty
Price
PartIndex

The pricing table contains multiple pricing records for each item depending on the MinQty and MaxQty. Not all items have the same Min and Max qtys available and some have only one.

MinQty MaxQty Price PartIndex
1 10 10 1001
11 24 7 1001
25 50 5 1001
50 100 3 1001
25 50 14 1002


I am trying to write a MakeTable query to extract all of the data from the record with the lowest MinQty and discard the rest. Since the MinQty is not the same on all the items I cannot use MinQty=1 in my query.

Is there a way to get ItemNo, ItemDesc, PartIndex, MinQty, MaxQty, Price, and PartIndex for an Item record containing pricing from the lowest MinQty only?

Note: not using VB / SQL code, making it right from the query design view. Can do some limited SQL if

it's needed to make this work.

Thanks in advance!

John

 

[Pat Hartman]

If you really want to do what you are asking, you need nested queries to get the lowest min and its associated price.

query1:
Select PartIndex, Min(MinQty) as MinMinQty
From Pricing
Group by PartIndex;
query2:
Select i.*, q.price from Item as i Inner join Pricing as p On i.PartIndex = q.PartIndex;

But if you have a quantity available in tblItem you can get the correct price. You need to do what are called theta joins which the QBE grid doesn't support. So build the query using the grid and create the normal equi-joins. Then switch to design view and change the joins. Once you change them, you won't be able to switch back to QBE view for this query because it cannot properly render the join.

Select i.ItemNo, i.ItemDesc, i.PartIndex, p.Price
From Item as i Inner Join Pricing as p On i.PartIndex = p.PartIndex AND i.Quantity >= p.MinQty AND i.Quantity <= p.MaxQty;

 

[mjrob]

Pat,

Yes, we want to take the MinQty pricing and load it into our price file. Where does the price come from in the 2nd query? What is the purpose of the 1st query if it does not include the price field.

Thanks,

 

[Pat Hartman]

When you use aggregate functions, you need to group by everything that isn't being aggregated so, if you include price in the first query, the SQL would be:
Select PartIndex, Min(MinQty) as MinMinQty, Price
From Pricing
Group by PartIndex, Price;

That query would return a row for each different price for PartIndex and each row would include the minvalue field so -
If your table contained:
Part1, 19, 100, $8
Part1, 10, 18, $5
Part1, 2, 9, $3
The query would return:
Part1, 2, $8
Part1, 2, $5
Part1, 2, $3
Which is not what you want.

Sorry, I messed up query2. I forgot to include the price table in it:
query2:
Select i.*, p.price from query1 as q Inner join Pricing as p On q.PartIndex = q.PartIndex AND q.MinMinQty = p.MinQty, Inner Join Item as i on q.PartIndex = i.PartIndex;

So basically, the first query finds the minimum value of MinQty for a partIndex. The second query joins everything back together to pick up the Price field associated with that MinMinQty and the PartIndex.

This query will NOT be updatable by the way because it includes an aggregate function.