Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Bristol, England
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Bristol, England
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •