Sum of Difference Question (1 Viewer)

[Malachy39]

Sum of difference
Column A is a date field
Column B is a currency field.

How do I query the difference between record X and record X+1.

For Instance.
Date Currency
1/1 $310
1/2 $432
1/3 $500

The query should show on the second line $122 and on the third line $168.

Thanks for you help.

 

[Rich]

Are you dealing with a large recordset?

 

[RV]

Try this:

SELECT table.DateColumn, table.CurrencyColumn-table1.CurencyColumn
FROM table, table AS table1
WHERE table1.DateXColumn=
(SELECT MAX (table1.DateColumn)
FROM table AS table1
WHERE table1.DateColumn<table.DateColumn);

Datecolumn = your "Dates" columnname,
CurrencyColumn = your "Amounts" columnname,
table = your tablename

RV

 

[RV]

...and post your questions only once.....

RV

 

[Malachy39]

Thanks for the reply.

Where exactly do I type:

SELECT table.DateColumn, table.CurrencyColumn-table1.CurencyColumn
FROM table, table AS table1
WHERE table1.DateXColumn=
(SELECT MAX (table1.DateColumn)
FROM table AS table1
WHERE table1.DateColumn<table.DateColumn);

Datecolumn = your "Dates" columnname,
CurrencyColumn = your "Amounts" columnname,
table = your tablename

Thanks

 

[RV]

It's called an SQL statement.
Copy the statement, open the databse in design mode, make a new query in the SQL view.
Paste the statement:

SELECT table.DateColumn, table.CurrencyColumn-table1.CurencyColumn
FROM table, table AS table1
WHERE table1.DateXColumn=
(SELECT MAX (table1.DateColumn)
FROM table AS table1
WHERE table1.DateColumn<table.DateColumn);

You'll have to adapt the statement:

Datecolumn = your "Dates" columnname,
CurrencyColumn = your "Amounts" columnname,
table = your tablename

Adapt the statement using your tablename and columnnames. Save the query.

RV