Subtraction of two rows in the same column

[chi]

Hi,

I am trying to subtract two rows in a query from the same column. I have four columns each with two rows in a query. I want to add a Difference row to compare the results.

For example -

Number of months Total Sales Total Products
6 1200 10
7 1300 20
Difference 1 100 10


Please help, Thanks.

 

[ajetrumpet]

I want to add a Difference row to compare the results.

For example -

Number of months Total Sales Total Products
6 1200 10
7 1300 20
Difference 1 100 10


Please help, Thanks.

this cannot be done easily in access. I think your best bet here is to move the data to excel and do the calculations that way.

 

[Galaxiom]

Here is the basic principle but it is difficult to be precise without the whole picture.

Make a new query with the table/query in it twice. The second time you drag it into the query it will be called Name_1.

Put the month field from both tables into the grid.

On the month field from the second table include the criteria: > table1.monthfield.
This should give a result as a single record with both months in separate columns.

Add the other fields you want, calculating the differences between the columns using an expression in the usual way.
Difference: table1_1.fieldname - table1.fieldname

I suspect you could probably implement it further up the chain of queries. For example, assuming the table has records for several sales staff you would join the two instances of the table on the salespersonID fields. This would give you one record for each salesperson.

 

[gemma-the-husky]

how are you getting at these figures. it soundsl ike you are storing the cumulative figures for each month/period

what you SHOULD be storing is the actual figures for each period. Then you can easily obtain the figures for any month, or the cumulative figures up to a given month, but you wouldn't need to be trying to subtract month 6 from month 7 for any reason

 

[just.a.guy]

Chi,

There may be one problem with GalaxiomAtHome's solution.

you may end up with too many rows.

Here is a sample of the SQL he might have been proposing

SELECT
a.month_no - b.month_no as month_diff
, a.total_of_sales - b.total_sales as sales_diff
, a.total_prod - b.total_prod as prod_diff
from
sales_table as a
, sales_table as b
where a.month_no > b.month_no

the problem with this is that you not only
get the two adjenct rows, but for
the third row you get the difference
between it and the first row, etc.

What you need to do is create a new
"view of the table" where each row
has a new column that identifies
the previous row, then join
that "view" of the two tables to
do the difference.

Here is a modification that may do just that.
(Ignore the "periods", I put them in there to try and maintain spacing
to make the query more readable).

The "view" is the embedded "SELECT" statement
enclosed in "(..)". You can place this select
statement in a separate query, and then include it
by replacing the "(SELECT...)" with the name of the query.

SELECT
a.month_no - b.month_no as month_dif
, a.total_sales - b.total_sales as sales_dif
. a.total_prod - b.total_prod as total_dif
from
sales_table as a
, (select
m.month_no as cur_month
, max(p.month_no) as prev_month
from sales_table as m
, sales_table as p
where m.month_no > p.month_no
group by m.month_no
) as j
, sales_table as b
where a.month_no = j.cur_month
and b.month_no = j.prev_month

(I wish this tool did not eliminate the extra spaces. I think the spaces makes it easier to read and see the structure.)

With this result you will only get n-1 rows
for a table of n rows.

There are other ways to write the SQL to do the same thing,
which might be more "formal" and
work faster. I just wanted to
make this easy for you to understand what is happening.

Hope this helps.