Same table, different problem

  • Thread starter Krzysztof via AccessMonster.com
  • Start date
K

Krzysztof via AccessMonster.com

Good Afternoon ESD,

I have a problem that i thinked I backed myself into.

i have a transaction log table, that looks like this:

transID, value1, value2, DateID

1 .65 187520 39
2 .95 212177 40


ok, so the value changes, and based on that value, it changes the second
value. what i need to get is the change in the value, ie, so week 39 it was
187520, then week 40, it was 212177, thus an increase of 24657. how can i get
the 24657 using the dateID?

I didn't know how to call this scenario, so that hinders my searching for
answers.

Any ideas are appreciated.
 
K

KARL DEWEY

Create a query in design view and put your table twice. Access will add a
sufix '_1' to the table name.
Pull down all the fields you need from the first table. Pull down DateID
from second table.
In the criteria row for the field use --
[Table1Name].[DateID]+1
Then add a calculated field like this --
Monthly_Incr: [Table1Name_1].[value2]-[Table1Name].[value2]
 
L

Lord Kelvan

will you ever have two date ids that are 40 or will you ever have the
dateid

37
38
40

where it misses an date id


Regards
Kelvan
 
L

Lord Kelvan

regardless you could try this sql

SELECT transactionlog.transid, transactionlog.value1,
transactionlog.value2, transactionlog.dateid, (select [transactionlog].
[value2] - [subtransactionlog].[value2] from transactionlog as
subtransactionlog where transactionlog.dateid =
subtransactionlog.dateid+1) AS difference
FROM transactionlog;

replace transactionlog with whatever your table name is

Reagrds
Kelvan
 
Top