Update records based on value of other records in same table

[Guest]

I posted this yesterday , got one post for additional infor, posted update,
and then nothing. I would like to try again since I really do need to get
this done. thanks in acvance anr any help.

I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey.Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner

What I need after the update:
CID EsortKey Event LsortKey.Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner

The following SELECT query displays the desired results

SELECT Fees.Event, Fees.EsortKey, First(Fees.LsortKey) AS NewKey
FROM Fees
GROUP BY Fees.Event, Fees.EsortKey, Fees.[Contest ID]
HAVING (((Fees.[CID])=30));

But when I change to an UPDATE query (as shown) I get the error shown after
the SQL.

UPDATE Fees SET Fees.EsortKey = First([NewKey])
WHERE (((Fees.[CID])=30));


“You tried to execute a query that does not include the specified expression
‘EsortKey’ as part of an aggregate functionâ€

How do I modify the select query into a valid update query

Any and all help is appreciated

Steve S

 

[Guest]

First, an observation on the use of FIRST( ). Databases are sets of
unordered data, using FIRST( ) tells the database to give you the results of
the first value it finds when it returns an unordererd data set. There is
no guarantee that this will bring back the "minimum" value, which is what it
looks like you want. If that is the case, I recommend you use MIN( ).

Recommend you copy your tables or database before trying this, but this
should do it.

UPDATE Fees F
SET F.EsortKey = DMIN("Level", "Fees", "Event = '" & F.Event & "' AND [CID]
= 30")
WHERE F.[CID]=30

HTH
Dale

 

[Guest]

I ran the query below and it updates the table but puts null or blank values
in the field LSortKey. LsortKey is the field I wnat updated - not LEVEL

UPDATE Fees F
SET F.EsortKey =DSUM("LsortKey", "Fees", "Event = ' " & F.Event & " ' and
[CID] = 30")
WHERE F.[CID]=30;

HELP

Steve S.

First, an observation on the use of FIRST( ). Databases are sets of
unordered data, using FIRST( ) tells the database to give you the results of
the first value it finds when it returns an unordererd data set. There is
no guarantee that this will bring back the "minimum" value, which is what it
looks like you want. If that is the case, I recommend you use MIN( ).

Recommend you copy your tables or database before trying this, but this
should do it.

UPDATE Fees F
SET F.EsortKey = DMIN("Level", "Fees", "Event = '" & F.Event & "' AND [CID]
= 30")
WHERE F.[CID]=30

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I posted this yesterday , got one post for additional infor, posted update,
and then nothing. I would like to try again since I really do need to get
this done. thanks in acvance anr any help.

I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey.Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner

What I need after the update:
CID EsortKey Event LsortKey.Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner

The following SELECT query displays the desired results

SELECT Fees.Event, Fees.EsortKey, First(Fees.LsortKey) AS NewKey
FROM Fees
GROUP BY Fees.Event, Fees.EsortKey, Fees.[Contest ID]
HAVING (((Fees.[CID])=30));

But when I change to an UPDATE query (as shown) I get the error shown after
the SQL.

UPDATE Fees SET Fees.EsortKey = First([NewKey])
WHERE (((Fees.[CID])=30));


“You tried to execute a query that does not include the specified expression
‘EsortKey’ as part of an aggregate functionâ€

How do I modify the select query into a valid update query

Any and all help is appreciated

Steve S