| Bob Quintal 2006-04-07, 8:27 pm |
| MLH <CRCI@NorthState.net> wrote in
news:vhbd321rjddcfba
dj9fles7j5bkttv3qi2@
4ax.com:
> Fields in MyTable:
>
> PostID
> PostDate
> RollQtyXfer
> RollDenomination
> 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.
|