Re: Calculated field question

In a query, you could use a subquery to get the time from the previous row.
Example of what to type into the Field row in your query:
PriorTime: (SELECT Max(Dupe.EventTime)
FROM tblEvent AS Dupe
WHERE Dupe.EventID = tblEvent.EventID
AND Dupe.EventTime < tblEvent.EventTime)

If your report performs any aggregation that depends on the subquery field,
you are likely to run into a "Multilevel group-by error". In that case, you
would need to use the much slower DMax() instead of the subquery.

Another alternative is to use the events of the report section to store and
retrieve the details:

1. In the General Declarations section of your report's module (top),
declare the variable:
Dim PriorTime As Variant

2. In the Format event of your Event group header, reset it:
PriorTime = Null

3. In the Print event of your Detail section, store the value:
PriorTime = Me.[EventTime]

4. In the Format event of your Detail section, assign the value to the text
box:
Me.[txtPriorTime] = PriorTime

(There is a problem with this approach if the user prints only some pages of
the report, in that the variable may not be initialized properly, but this
would only affect the first record on the page.)

--
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.

"Neil Grantham" <neil40@xxxxxxxxxxxxxx> wrote in message
news:1135302367.472057.176530@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>I have a table that is a list of results (1st to 10th) for various
> events.
>
> Each line in the table has an event code, so that they get grouped
> correctly on a report (page per event)
>
> Each comptitor has a finishing time recorded.
>
> In my report, I would like to show the time difference from the
> previous competitor.
> I believe the best way is not to store calc's in the table, so how
> would I calculate this in a query?
>
> Thanks and Merry Christmas
> Neil


.

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: 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: Display a prior record value with current record value
    ... with a subquery, but IME, ... The report often gives ... possibly by stacking one query on top of another. ... TransactionID DateChange Amount InvID ...
    (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: Calculated field question
    ... I tried out the 'PriorTime' suggestion with my ... > In a query, you could use a subquery to get the time from the previous row. ... > If your report performs any aggregation that depends on the subquery field, ... In the Print event of your Detail section, store the value: ...
    (microsoft.public.access.queries)