[AccessD] Something too hard for me...

Okay, made a table called tblTest.  Put two fields in it. Quantity and
Price.  I filled it with the following information:

10  55
20  80
30  90
40  95
50  97

To do this on an individual basis, I created the following SQL:

SELECT [Enter Number of Spots] AS NumberOfSpots, (SELECT [Price]/[Quantity]
As PerUnit FROM tblTest WHERE Quantity=(SELECT Min(Quantity) AS MinQuantity
FROM tblTest WHERE Quantity>=[Enter Number of Spots])) AS NextPerUnitCost,
tblTest.Quantity AS BaseQuantity, [Price]+(([Enter Number of
Spots]-[BaseQuantity])*[NextPerUnitCost]) AS NewCost
FROM tblTest
WHERE (((tblTest.Quantity)=(SELECT Max(Quantity) AS MaxQuantity FROM tblTest
WHERE Quantity<=[Enter Number of Spots])));

Run that, and put in 25, when prompted for 'Enter Number of Spots', and you
get 25 for the NumberOfSpots, 3 for NextPerUnitCost, 20 for BaseQuantity,
and $95 for NewCost.

Now, if you want have the SQL generate a complete table, Create a table
called tblTestValues and give it one field, TestQuantity. (Long)

Then, put the following SQL into a Query, and call it qryPriceMatrix

SELECT T1.TestQuantity, (SELECT [Price]/[Quantity] As PerUnit FROM tblTest
WHERE Quantity=(SELECT Min(Quantity) AS MinQuantity FROM tblTest WHERE
Quantity>=T1.TestQuantity)) AS NextPerUnitCost, (SELECT Max(Quantity) AS
MaxQuantity FROM tblTest WHERE Quantity<=T1.TestQuantity) AS BaseQuantity,
(SELECT Price FROM tblTest WHERE Quantity=(SELECT Max(Quantity) AS
MaxQuantity FROM tblTest WHERE
Quantity<=T1.TestQuantity))+(([TestQuantity]-[BaseQuantity])*[NextPerUnitCos
t]) AS NewCost
FROM tblTestValues AS T1 LEFT JOIN tblTest ON T1.TestQuantity =
tblTest.Quantity;

Then create a form, and put a command button on it, and put the following
code behind the OnClick Event:

Dim rsTest As Recordset
Dim rs As Recordset
Dim i As Long
CurrentDb.Execute "DELETE * FROM tblTestValues"
Set rsTest = CurrentDb.OpenRecordset("tblTestValues", dbOpenTable)
Set rs = CurrentDb.OpenRecordset("Select Min(Quantity) As Minimum,
Max(Quantity) As Maximum FROM tblTest")
For i = rs.Fields(0).Value To rs.Fields(1).Value
    rsTest.AddNew
    rsTest.Fields(0).Value = i
    rsTest.Update
Next i
rsTest.Close
rs.Close
Set rsTest = Nothing
Set rs = Nothing
DoCmd.OpenQuery "qryPriceMatrix"

(Note, this code was written in Access 97, so it uses DAO by default.  If
you are using A2k or up, set a reference to DAO)

Now, you can change the price matrix in tblTest, and just click the button
to see the complete 'list' of quantity versus price.

Enjoy.  By the way, this was fun, should have posted this before I got into
the Date Dimension table issue! <grin>

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Kostas
Konstantinidis
Sent: Friday, February 06, 2004 2:28 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Something too hard for me...


Hi all,
I am trying to create a little project to calculate some prices of a Radio
Station advertising list
In a table I keep CATEGORY and PRICE e.g.
10 --> 55,00EURO
20 --> 80,00
30 --> 90,00
bla, bla

What I want to calculate is the price of an uncategorized spots between two
categories.
25 SPOTS....
The calculation is:
A) the 20 spots cost 80,00
B) I have to select the next category (in that case 30) to find out how much
cost one spot (90,00/30 = 3,00EURO)
C). the 5 spots cost 5X3,00=15,00
so, the total price for the uncategorized query must be
80,00+15,00=95,00euro


Could anybody please help me?

Thank's a lot
Kostas Konstantinidis







_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com