Picking a value from a list in a table

[douglas.hough]

Hi all

I have a list of parts with quantities in an Access table (imported
from Excel). In another table there is a list of parts with price break
information. I'm trying to grab the correct price for each part in the
first list from the entry for the part in the second list. Can anybody
give me some pointers as to how to do this in an Access query or
module? The two data sets look like the info below:

List One
Part Number Qty
11020203 45
12935719 890
12394828 12

List Two

Part Number, Qty1, Price1, Qty2, Price2, Qty3, Price3, Qty4,
Price4
11020203, 5, 2.3, 10, 2.1, 20, 1.90,
50, 1.75
12935719, 50, .098, 250, 0.089, 1000, 0.085,
5000, 0.079
etc etc.

The problem I'm having is that there are a maximum of 10 price columns
for each part, but some may have only 4 columns filled in, some 8. Also
the qty in sheet one is sometimes less than the minimum qty band in
sheet 2, but on another part could be higher than the maximum qty band.

Sorry for the rather lengthy question!

 

[Jeff L]

I think you might be better off taking List Two and making a pricing
list table with PartNumber, MinQty, MaxQty, and Price. It will make
querying your information a lot easier. Do some append queries to set
up the price list. For the first one,
Select PartNumber, 1, Qty1, Price1

From ListTwo

For the ones after that:
Select PartNumber, [Qty1] + 1, Qty2, Price2

From ListTwo

Where Price2 Is not null

Select PartNumber, [Qty2] + 1, Qty3, Price3

From ListTwo

Where Price3 Is not null

And so on

Now when you join ListOne with PriceList, you join it on PartNumber.
In the criteria for the Qty from ListOne, put Between [MinQty] and
[MaxQty] and output the Price from the PriceList.

Hope that helps!

 

[Larry Daugherty]

Before sweating a query you should re-organize your data... The
indicator that you must change your data structure is that you have
repeating pairs of fields: Qty n & Price n.

tblPart should have the Part Number and description of every part in
your database.

tblPartPrice should be the many side of a won-to-many relationship
with tblPart. Each row in tblPartPrice would have a field each for
QtyLow, QtyHigh, and Price

With your data arranged as above you can easily create a query that
will return the correct unit price for the quantity of the part in
list 1.

HTH

 

[Huff]

Jeff

Thanks for the message. I've got round it by creating a query with
multiple IF statements that give a result of zero if the qty is outside
the bands and then summing that result. A big fudge but seems to
work...

The real problem is that List Two is a real movable feast, with parts
being taken off or added or amended all the time. Perhaps building a
table for each update would be the way forward.
Thanks
Duff

I think you might be better off taking List Two and making a pricing
list table with PartNumber, MinQty, MaxQty, and Price. It will make
querying your information a lot easier. Do some append queries to set
up the price list. For the first one,
Select PartNumber, 1, Qty1, Price1

From ListTwo

For the ones after that:
Select PartNumber, [Qty1] + 1, Qty2, Price2

From ListTwo

Where Price2 Is not null

Select PartNumber, [Qty2] + 1, Qty3, Price3

From ListTwo

Where Price3 Is not null

And so on

Now when you join ListOne with PriceList, you join it on PartNumber.
In the criteria for the Qty from ListOne, put Between [MinQty] and
[MaxQty] and output the Price from the PriceList.

Hope that helps!

Hi all

I have a list of parts with quantities in an Access table (imported
from Excel). In another table there is a list of parts with price break
information. I'm trying to grab the correct price for each part in the
first list from the entry for the part in the second list. Can anybody
give me some pointers as to how to do this in an Access query or
module? The two data sets look like the info below:

List One
Part Number Qty
11020203 45
12935719 890
12394828 12

List Two

Part Number, Qty1, Price1, Qty2, Price2, Qty3, Price3, Qty4,
Price4
11020203, 5, 2.3, 10, 2.1, 20, 1.90,
50, 1.75
12935719, 50, .098, 250, 0.089, 1000, 0.085,
5000, 0.079
etc etc.

The problem I'm having is that there are a maximum of 10 price columns
for each part, but some may have only 4 columns filled in, some 8. Also
the qty in sheet one is sometimes less than the minimum qty band in
sheet 2, but on another part could be higher than the maximum qty band.

Sorry for the rather lengthy question!

 

[Huff]

Larry

Thanks for the reply.

I'm trying to understand how to put your idea into practice whilst
using the data we have around us here. In our scenario, List One is a
sheet sent to us from another source at random intervals, which we then
need to provide appropriate pricing for using List Two.

We're only interested in parts that have a price of some kind so don't
necessarily need the entire part database in this db (there's over
200000 of them so it'd slow things down a tad I think, probably only
30% currently have a price matrix)

If I understand you correctly you're suggesting that for the example I
gave, instead of:
Part Number, Qty1, Price1, Qty2, Price2, Qty3, Price3, Qty4, Price4
11020203, 5, 2.3, 10, 2.1, 20, 1.90,
50, 1.75

There'd be a table with:
Part, Qty Low, Qty High, Price
11020203 0 5 2.30
11020203 6 10 2.10
11020203 11 20 1.90
11020203 21 50 1.75

and so on?

Given that the data comes out of our back office system in the format I
initially gave, could you give some idea of how to convert it to your
suggested format? And then if that's achieved how I could produce a
query that only results in one record per part, giving part plus
correct price?
I've posted a reply with the rather clunky method that I used to get
the result out, but would be grateful for a more confidence inspiring
version!

Thanks

Doug

 

[Larry Daugherty]

Hi Doug,

Your representation of the way records in tblPartPrice would look is
on the mark. If the primary key in tblPart is the part number then
the foreign key in tblPartPrice would be that same part number.
tblPartPrice would have a row for every price break of every part.
That means a lot of records but that's the way these things work.

As to which parts have prices and are entered into tblPartPrice,
that's up to you. As you know, if you want your query to return items
they have to be in the tables....

To make it work well you'd want to establish referential integrity and
enable cascading deletes.

-------------------------------------------

Converting 60,000 records into 240,000 records manually might be a
chore!

You can create a conversion routine in which you have two recordsets
open at the same time. rst1 based on whatever the table for List 2
presently is and rst2 based on your new table, tblPartPrice.

Each record in the current List 2 will become 4 records in
tblPartPrice.

By the way, do all parts have only 4 price breaks or is that an
arbitrary limit because it was becoming too awkward to deal with more?
In this new structure you can have any number of price breaks; from
none up....

Note also that you don't really need a range in either your existing
system nor in the one I propose. You could easily read the price
breaks as "Beginning at qty n, the unit price is m.mm" . It's your
app so you choose.

Anyway, step through rst1 (LIst2), copy the Part number (or Primary
Key, if different) to rst2!(tblPartPrice), ForeignKey, copy the value
in rst1!Qty1 to rst2!Qty, copy the value in rst1!Price1 to rst2!Price.
UPdate. step rst2 and repeat the foregoing for the remaining Qty-Price
pairs in the current record in rst1. Repeat until EOF in rst1

If you haven't done it before, be careful that each new record in rst2
is initiated with rst2.Addnew and that the record isn't actually
created until you have copied in your changes and follow them with
rst2.Update.

HTH