Re: Display a prior record value with current record value

Fair enough Janna.

Access is not good at this kind of thing. You can solve it fairly easily
with a subquery (along the lines of the Michel Walsh article), but IME,
Access doesn't handle these well in this application. The report often gives
the 'Multi-level group-by' error, and if it doesn't Access will sometimes
churn for ages choosing a really inefficient plan, and if it doesn't it will
not infrequently crash as JET is prone to this kind of error.

Still, I wouldn't store the value. There has to be some way to get the
value, possibly by stacking one query on top of another. You may be able to
run one of Michel Walsh's queries in the lower level query, and get it that
way.

It's worth persisting with, and it is possible to find an efficent solution.
I had one of these just a couple of weeks ago. There were 900k records in
the table, so it really was a matter of persisting until we got around the
JET bugs and got a stable, reliable solution.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Janna" <Janna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:097C6124-286F-482A-96CD-D11FA49CF178@xxxxxxxxxxxxxxxx
Yes, I'm finding it a bit challenging to try to do what I want in a
reliable
way. Dlookup will not work, as there is no guarantee the autonumbers will
be
contiguous.

I got the coding example to work that you referenced in your original
post,
but in addition to wanting to try to pull out a previous value (which is
actually a calculated value) for InvID 1 on a report, there will be other
InvID's that may actually end up be the previous record value (in looking
at
my original post, I realize, I didn't explain that piece.

Maybe what I'll have to resort to is storing the calculated value of the
previous record in a field versus trying to always calculate the previous
value on the fly. I posted a question to this effect under the Database
Design topic area titled Calculate on the Fly vs Update Query on 10/19/06,
which more fully explains the design/objective of my tables/db. I realize
calculating the value is usually a better option, than calculating a value
and storing it statically in a field, but I just don't know how to
accomplish
it. Thanks for taking the time to answer my previous posts. I appreciate
it.


"Allen Browne" wrote:

You can use Method 1 - the DLookup() in a report.
Just set the ControlSource property the same way.
Main problem with that is the assumption that the sequence of ID numbers
is
contiguous: not a safe assumption.

Alternative approaches (query based):
http://www.mvps.org/access/queries/qry0020.htm
There are some limitations with subqueries in reports. If the report does
any sorting/grouping, you may be told it has a "multi-level group-by
error".
A workaround is to use a stacked query (one query on top of another)
instead
of a subquery.

There is another approach that uses the event of the report sections. If
the
control is in the Detail section, declare a variable in the report's
module,
Assign a value in the Print event of the Detail section, and that value
to
the unbound control in the Format event of the section. This approach is
flawed though: it doesn't work correctly in a report where you don't
print
all pages, because the events may fire for the printed pages also.

"Janna" <Janna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1A537A02-C017-4EC7-8CC4-9148F75D823E@xxxxxxxxxxxxxxxx
Thanks Allen. Can you suggest how to adapt module code to a report?

"Allen Browne" wrote:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

"Janna" <Janna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EFC0EBA3-F70F-4D83-AE74-D6AD8E11E294@xxxxxxxxxxxxxxxx
Is there any way via programming to grab a value to display from the
record
just prior to the current record and display it with the current
record.

For example, a table has following four fields with data entered:
TransactionID (PK) DateChange Amount InvID
1 8/1/06 $100 1
2 9/1/06 $200 1
3 10/1/06 $300 1

I would like to display in a report or form run for InvID 1 for the
period
of 10/1/06 to 10/20/06 the following:

InvID Date of Change Amount prior to change Current Amount
1 10/1/06 $200 $300


.

Relevant Pages

  • Re: Reporting the last two updates
    ... a subquery. ... correlated subquery because it refers to the outer query, ... last two updates per property. ... My report brings in all the information ok, and I was able to limit the ...
    (microsoft.public.access.gettingstarted)
  • Re: Calculated field question
    ... In a query, you could use a subquery to get the time from the previous row. ... PriorTime: ... If your report performs any aggregation that depends on the subquery field, ...
    (microsoft.public.access.queries)
  • Re: Report based on query w/subquery wont give a Report Footer to
    ... I don't have the underlying query updating the tables. ... the user's entry into the entry form. ... and report on the difference between them. ... multiple Group Bys in a subquery. ...
    (microsoft.public.access.reports)
  • Re: show records based on calculated field
    ... it in the query, not the report and you'd do it using subqueries. ... You need look at potential sale numbers, ... The key issue is the subquery that is used in item 2 above. ...
    (microsoft.public.access.reports)
  • Re: Display a prior record value with current record value
    ... Design topic area titled Calculate on the Fly vs Update Query on 10/19/06, ... calculating the value is usually a better option, ... If the report does ... TransactionID DateChange Amount InvID ...
    (microsoft.public.access.reports)
Loading