How to create query with calc'd field that equals value of [Field] in previous record + value of [AnotherField] in current record?

[MLH]

Fields in MyTable:

PostID
PostDate
RollQtyXfer
RollDenominatio n
RollCount37
RollCount23
PostID is an A97 AutoNumber field. PostDate is a Date field.
The rest of the fields are Integer type.

This is what I want to see in a query:
[PostID] [PostDate] [RollQtyXfer] [RollCount37] [CalcValue]
where [PostID]>1 and CalcValue is a calculated field that
equals [RollCount37] in the PREVIOUS record + [RollQtyXfer]
value in the current record.

Is it possible?

[Rich P]

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.developersdex.com ***

[Bob Quintal]

MLH <CR**@NorthStat e.net> wrote in
news:vh******** *************** *********@4ax.c om:

Fields in MyTable:

PostID
PostDate
RollQtyXfer
RollDenominatio n
RollCount37
RollCount23
PostID is an A97 AutoNumber field. PostDate is a Date field.
The rest of the fields are Integer type.

This is what I want to see in a query:
[PostID] [PostDate] [RollQtyXfer] [RollCount37]
[CalcValue] where [PostID]>1 and CalcValue is a calculated
field that equals [RollCount37] in the PREVIOUS record +
[RollQtyXfer] value in the current record.

Is it possible?

You can do this by creating a parameterized subquery that
returns the value of rollCount37 for the previous record and
embedding that in hte main query as a calculated field.

We'll start with
SELECT top 1 rollcount37
from mytable ALIAS st
WHERE st.postdate < mn.postdate
ORDER by postdate.desc

If the parameter (mn.postdate) is the same as the field value in
the main query, it will use that value.

and embed that in the next query
SELECT postID,
Postdate,
rollqtyXFER,
RollCount37,
(SELECT top 1 rollcount37
from mytable ALIAS st
WHERE st.postdate < mn.postdate
ORDER by postdate.desc
) as CalcValue
FROM mytable alias mn
Order by postdate;

Note I havent run this SQL so check for typos.
--
Bob Quintal

PA is y I've altered my email address.