Update question

[Guest]

Hello all,

I have a table (tbResults). In this table I have a record with a count that
I want to deduct. I want to deduct if from another record in this table. I
have tried the code below, but get errors.

Here is the data

TbResults:

Area Count
Divert 15 2000
Carousel 200

I want to update Divert 15 to 2000 – 200 = 1800.

UPDATE tbResults SET tbResults.Count = [count]-[expr1]
WHERE ((([expr1]) In (SELECT tbResults.Count FROM tbResults WHERE
(((tbResults.Area)="Carousel")))) AND ((tbResults.Area)="Divert 15"));

 

[Van T. Dinh]

You can use either DLookUp to get the Count belonging to Carousel like:

UPDATE tbResults SET tbResults.[Count] = tblResults.[Count]
- DLookUp("[Count]", "tblResults", "[Area] = 'Carousel'")
WHERE ((tbResults.Area)="Divert 15");

Alternatively, you can use an UPDATE query with a Cartesian Join like:

UPDATE tbResults AS T1, tblResults AS T2
SET T1.[Count] = T1.[Count] - T2.[Count]
WHERE (T1.Area = "Divert 15")
AND (T2.Area = "Carousel");

BTW, Count is a bad name for a Field name: it is a reserve word for JET SQL
as well as most other SQL flavours!

 

[Guest]

Thank you very much! I will also be changing the name of the count field. I
had a problem with date a while back and it can do some funny things.

You can use either DLookUp to get the Count belonging to Carousel like:

UPDATE tbResults SET tbResults.[Count] = tblResults.[Count]
- DLookUp("[Count]", "tblResults", "[Area] = 'Carousel'")
WHERE ((tbResults.Area)="Divert 15");

Alternatively, you can use an UPDATE query with a Cartesian Join like:

UPDATE tbResults AS T1, tblResults AS T2
SET T1.[Count] = T1.[Count] - T2.[Count]
WHERE (T1.Area = "Divert 15")
AND (T2.Area = "Carousel");

BTW, Count is a bad name for a Field name: it is a reserve word for JET SQL
as well as most other SQL flavours!

--
HTH
Van T. Dinh
MVP (Access)

Hello all,

I have a table (tbResults). In this table I have a record with a count
that
I want to deduct. I want to deduct if from another record in this table.
I
have tried the code below, but get errors.

Here is the data

TbResults:

Area Count
Divert 15 2000
Carousel 200

I want to update Divert 15 to 2000 - 200 = 1800.

UPDATE tbResults SET tbResults.Count = [count]-[expr1]
WHERE ((([expr1]) In (SELECT tbResults.Count FROM tbResults WHERE
(((tbResults.Area)="Carousel")))) AND ((tbResults.Area)="Divert 15"));