# Thread: Using a variable in calculation's (97)

1. ## Using a variable in calculation's (97)

I have recently been tasked at work to develop a benefit tracking system on one of our contracts, although the database is now set up and running,

we now need to inflate our baseline figures by a certain % Currently my calculation is a simple calculation (see below) which can only calculate the baseline % increase for the current months orders
[Data]![Baseline Price]*1.0299 AS [Baseline Price] (July)
However i need to build in a variable indexation for the above calculation, were the multiplication figure is dependent on what month the order was placed (retrospectively). so for example if the same order had been placed in March the calculation would be
[Data]![Baseline Price]*1.0255 AS [Baseline Price] (March)

Is there anyway i can do this.

Cheers
Suzanne

2. ## Re: Using a variable in calculation's (97)

Create a table to list the increase (multiplication factor) by month:

<table border=1><td>BaseMonth</td><td>Increase</td><td align=right>01/01/2004</td><td align=right>1.0221</td><td align=right>02/01/2004</td><td align=right>1.0239</td><td align=right>03/01/2004</td><td align=right>1.0255</td><td>. . .</td><td>. . .</td></table>
BaseMonth should be the primary key in this table. Create a query based on your table. Add the fields you need, plus a calculated column

BaseMonth: DateSerial(Year([OrderDate]),Month([OrderDate]),1)

where OrderDate is the name of the field that holds the order date. Save this query. Next, create a query based on the query you just created and the table with the increases. Join them on BaseMonth. You can now use the Increase field from the table in your calculations instead of a constant factor.

3. ## Re: Using a variable in calculation's (97)

Thank you so much, have just tried what you suggested, and it worked a treat

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•