Looking up data in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a pricing table (costs) that looks something like this
QTY Price
23 1.60
48 1.33
72 1.23
and so on.
In another table I have a qty field which the user inputs. I would like to
be able to "look" thru the cost table and based on <> formula use the figure
from the cost table in a formula on my pricing table. Something like
Qty is >23<49 and be able to use the correct answer of 1.33.
Since I am rather new at this, I can not figure out how to get the 1.33 into
my formula from the other table.
Any help will be appreciated.
Rick
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hopefully, you have a product ID in the costs table and the other table.
If so, a query like this will get the correct Price (assumes that 23
and 49 are the lo quantity and hi quantity in the other table).

SELECT S.product_id, P.price
FROM Price As P INNER JOIN Sales As S
ON P.product_id = S.product_id
WHERE P.qty BETWEEN S.hi_qty And S.lo_qty

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRD1M6IechKqOuFEgEQI40wCfTsrl/gllM8uqhP30x2PnVpUAl8sAni4H
fYDQUqiRziWdxl+2NarZ88xT
=rAAB
-----END PGP SIGNATURE-----
 
What I wanted to do was test the qty against the cost table. It might fall
into any of the categories shown below. This is a qty pricing, more qty
cheaper price.
I want to test against the qty field in my first database.
I can do this with nested if statements, however that is rather cumbersome
and difficult to make updates.
Hope this explaination makes what I want to do more clear.
Rick
 
Hi,


In this case, since the price continuously decrease, you can do

myPricingTable ' table name
QTY Price
0 1.60
24 1.33
49 1.23

(not that I started at 0, and imply that qty up to 23, included, will be at
1.60 each)
and use:

SELECT a.OrderID, LAST(a.qty), LAST(a.clientID), LAST(a.other), MIN(b.price)
FROM myOrderTable As a INNER JOIN myPricingTable As b
ON a.qty >= b.qty
GROUP BY a.OrderID


If you have multiple itemID:

myPricingTable ' table name
ItemID QTY Price
1 0 1.60
1 24 1.33
1 49 1.23
2 0 12.20
2 2 10.50
2 10 9.60



and the query


SELECT a.OrderID, a.ItemID, LAST(a.qty), LAST(a.clientID), LAST(a.other),
MIN(b.price)
FROM myOrderTable As a INNER JOIN myPricingTable As b
ON a.ItemID=b.ItemID AND a.qty >= b.qty
GROUP BY a.OrderID, a.ItemID




note that LAST(a.other) stands for using LAST on any other fields from
myOrder table you wish to keep, if appropriate.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for taking the time, however this is mostly over my head.
I think I will just have a field for every price break calculated, and show
that on a report.
Once again thanks for your efforts.
Rick
 
Back
Top