Calculated field question

  • Thread starter Thread starter Neil Grantham
  • Start date Start date
N

Neil Grantham

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

Thanks for the tips, I tried out the 'PriorTime' suggestion with my
table/field name thus in the Field section of my query

PriorTime: (SELECT Max(Dupe.Time) FROM WRC-Rallyresults AS Dupe WHERE
Dupe.VenueID = WRC-Rallyresults.VenueID AND Dupe.Time <
WRC-Rallyresults.Time)

But, I just get a 'Syntax Error' pop up and have no idea where the
error is!

Thanks
Neil

Allen said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Neil Grantham said:
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
Click to expand...
Click to expand...
 
No: it does not go in your table.

The expression goes into a fresh column of the Field row of a *query*.

You could also try adding square brackets around [WRC-Rallyresults]
whereever it occurs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Neil Grantham said:
Allen

Thanks for the tips, I tried out the 'PriorTime' suggestion with my
table/field name thus in the Field section of my query

PriorTime: (SELECT Max(Dupe.Time) FROM WRC-Rallyresults AS Dupe WHERE
Dupe.VenueID = WRC-Rallyresults.VenueID AND Dupe.Time <
WRC-Rallyresults.Time)

But, I just get a 'Syntax Error' pop up and have no idea where the
error is!

Thanks
Neil

Allen said:
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.)

Neil Grantham said:
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
Click to expand...
Click to expand...
Click to expand...
 
Misconception there Allen
I didn't say I put it in my table, what I meant was that I substituted
my table and field names to the query you had written out, and it IS in
a fresh column in my query.
With the [ ] added it now works.
Thanks.
Presume I need to now use something like Datediff to get the time
difference in a further column.

Merry Christmas (presume it's already come!)
Neil

Allen said:
No: it does not go in your table.

The expression goes into a fresh column of the Field row of a *query*.

You could also try adding square brackets around [WRC-Rallyresults]
whereever it occurs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Neil Grantham said:
Allen

Thanks for the tips, I tried out the 'PriorTime' suggestion with my
table/field name thus in the Field section of my query

PriorTime: (SELECT Max(Dupe.Time) FROM WRC-Rallyresults AS Dupe WHERE
Dupe.VenueID = WRC-Rallyresults.VenueID AND Dupe.Time <
WRC-Rallyresults.Time)

But, I just get a 'Syntax Error' pop up and have no idea where the
error is!

Thanks
Neil

Allen said:
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.)

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top