MS Access database support - Re: How to create query with calc'd field that equals value of [Field] in previous record + value o

Author

Re: How to create query with calc'd field that equals value of [Field] in previous record + value o

Rich P

2006-04-07, 8:27 pm

something like this is doable using Transact sql in Sql server, but
Access doesn't support that kind of operation. Here is a sample of the
tsql - then I explain how you do this in Access

create table #temp1(
rowId int Identity(1,1),
fld1 int,
fld2 int)

insert into #temp1(fld1, fld2)
select 1, 2 union
select 3, 4 union
select 5, 6 union
select 7, 8 union
select 9, 10

select t1.fld1, t1.fld2, t2.fld1 + t2.fld2 from #temp1 t1 join #temp1 t2
on t1.rowid = t2.rowID + 1

You are using a self join here where the first table is listing the rows
that you have, but the second copy is listing the next set of rows - and
you join the ID field by tricking it to count the next ID (rowID + 1).
You can't do this in Access though. Instead, in Access you create a 2nd
copy of the table and populate it with the next set of rows (or previous
set of rows - whatever - offset by one row) and join the ID fields as in
the example above, then do a basic query.

Rich

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links