Thread: Substraction in a query (Access 2000)

1. 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. 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. 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. Re: Substraction in a query (Access 2000)

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

5. 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. 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. 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. 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. 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
•