Need opinions on storing a calculated value, please

[Gina K]

I’m using a subquery to assign a FinishPlace to athletes competing in a race:

SELECT tblEventResults.fkMeetEventID, tblEventResults.EventResultsID,
tblEventResults.TimeResult, (SELECT Count(*) FROM tblEventResults AS T WHERE
T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS FinishPlace, tblEventResults.Override
FROM tblEventResults
WHERE (((tblEventResults.TimeResult)>0));

This results in a non-updateable query. However, I need the ability to
override the FinishPlace assigned by the query, hence the field “Overrideâ€.
I then want to have a stored field called FinalPlace:
(IIf(IsNull([Override]), [FinishPlace],[Override])

I know you’re almost never supposed to store a calculated value in a table,
but I will have to perform further subqueries against the value of the
FinalPlace in order to assign points to athletes, and I’m ending up with a
lot of nested queries/subqueries.

Any opinions/suggestions would be greatly appreciated.

Thanks.

 

[Baz]

Neat query, but there's no way it can be got updateable.

However, in my opinion, if the finishing place can be entered manually it is
not calculated data. I really think that, in this instance, I would add
FinishPlace as a column in the table.

I do a lot of order processing databases where the price of an item in an
order defaults from a standard price stored in a products table. However,
the order-entry operator always needs to have the option to enter a special
price just for that order, so the price column has to be on the order.
Quite similar to your situation, I think.

 

[Michael Gramelspacher]

I?m using a subquery to assign a FinishPlace to athletes competing in a race:

SELECT tblEventResults.fkMeetEventID, tblEventResults.EventResultsID,
tblEventResults.TimeResult, (SELECT Count(*) FROM tblEventResults AS T WHERE
T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS FinishPlace, tblEventResults.Override
FROM tblEventResults
WHERE (((tblEventResults.TimeResult)>0));

This results in a non-updateable query. However, I need the ability to
override the FinishPlace assigned by the query, hence the field ?Override?.
I then want to have a stored field called FinalPlace:
(IIf(IsNull([Override]), [FinishPlace],[Override])

I know you?re almost never supposed to store a calculated value in a table,
but I will have to perform further subqueries against the value of the
FinalPlace in order to assign points to athletes, and I?m ending up with a
lot of nested queries/subqueries.

Any opinions/suggestions would be greatly appreciated.

Thanks.

This is only an example. It seems to work with my data.

Having the finish rank stored in the table makes it easier
to show every race Jimmy Brown ran in and his finish rank.

Query: Qualified Finishers
-------------------------
SELECT EventResults.contestant_nbr,
EventResults.event_nbr,
EventResults.event_date,
EventResults.start_date,
EventResults.finish_date,
EventResults.disqualified
FROM EventResults
WHERE (((EventResults.disqualified) = 0));

Query: Rank Qualified Finishers
------------------------------
SELECT a.contestant_nbr,
a.event_nbr,
a.event_date,
a.start_date,
a.finish_date,
COUNT(* ) AS Rank
FROM [Qualified Finishers] AS a
INNER JOIN [Qualified Finishers] AS b
ON (a.start_date = b.start_date)
AND (a.event_nbr = b.event_nbr)
AND (DATEDIFF('s',NZ(a.finish_date,#2099-01-01 #),a.start_date)
<= DATEDIFF('s',NZ(b.finish_date,#2099-1-1 #),b.start_date))
GROUP BY a.contestant_nbr,a.event_nbr,a.event_date,
a.start_date,a.finish_date;

Query: Update Finish Rank
--------------------------
UPDATE EventResults SET EventResults.finish_rank = Dlookup("Rank","Rank
Qualified Finishers",
"event_nbr = " & EventResults.event_nbr & " AND event_date = #" &
EventResults.event_date &
"# AND contestant_nbr = " & EventResults.contestant_nbr & " AND start_date =
#" &
EventResults.start_date & "#");

 

[Jamie Collins]

I need the ability to
override the FinishPlace assigned by the query, hence the field "Override".
I then want to have a stored field called FinalPlace:
(IIf(IsNull([Override]), [FinishPlace],[Override])

Assuming Override is a NOT NULL (Required = Yes) column in a base
table then this is how I would do it but I'd still under normal
circumstances calculate (not store) FinalPlace.

Jamie.

--