Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Substraction in a query (Access 2000)

    Substraction in a query

    I refer to the excellent model of a summary report of Hans,moderator in the present Forum, and would like to ask how can
    i substract in a query the fields for the year 2004 from the fields of the year 2003.
    I need this in order to find out how much is the decrease in liters in 2004 as compared with 2003.
    There is a query Decrease in the example attached,but i cannot carry out the substraction there.
    How can i calculate the difference between the liters in 2004 and 2003? .And how is the
    percent of decrease caculated?
    If i know the total liters of the decrease and also the total liters of the increase,then i will know our efficiency.
    Since on the one hand we are loosing in the customers who have decreased,and we are
    gaining from the customers who have increased.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Substraction in a query (Access 2000)

    You can calculate the difference, both as a number and as a percentage, in a query based on the crosstab query. You will have to add this information to the other queries. See attached modified database.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substraction in a query (Access 2000)

    Thank you so much for the extended summary.This is the most wonderful solution . How can i sum up the results of all the liters for the years? Can we show the end result in three lines?

    kind regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Substraction in a query (Access 2000)

    What exactly do you want to display in thr three lines?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substraction in a query (Access 2000)

    I want with the =Sum([SumOfLiters]) to show the sum of all the liters for 2003 and 2004 and also the difference
    for example


    2003 - 900 tons
    2004 1000 tons


    kind regards
    increase 100 tons

    kind regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Substraction in a query (Access 2000)

    Should this depend on whether the user selected 'Increase' or 'Decrease', or do you want to sum the liters regardless of this choice?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substraction in a query (Access 2000)

    the best solution would be yes to depend on the increase or decrease,. Actually to have a sumup field at the end of the list with any option
    i have chosen

    kind regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Substraction in a query (Access 2000)

    1. Open the report in design view.
    2. Select View | Report Header and Footer.
    3. Set the height of the report header to 0 (you don't need it).
    4. Put 3 text boxes txt2003, txt2004 and txtDifference in the report footer.
    5. Set their Control Source to:
    =Abs(Sum([SumOfLiters]*(Year([Invoicedate])=2003)))
    =Abs(Sum([SumOfLiters]*(Year([Invoicedate])=2004)))
    =[txt2004]-[txt2003]

    See attached version.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substraction in a query (Access 2000)

    Thank you very much indeed.With this problem solved i think i have finished successfully my survey of the year.You are very kind and clever person.
    I wish you all the best with the coming New Year

    Happy New Year

Posting Permissions

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