Update only one record

[Guest]

I've found much information on this site without having to post a question...
This time I must surrender and post the following:

I would like to use an update query to find one record to make an update to.
I've had no dificulty updating my data if my query returns only one record.
The problem lies in having two or more records that satisfy the query
criteria. It really doesn't matter which of the multiple returned records I
update, as long as I only update one of them.

Using Access VBA 2003, what is the syntax for selecting the record sorted to
the top of the query datasheet?

 

[John Spencer]

It would help to give you a specific solution if you posted your SQL.

Does the record to be updated have a primary key - a unique value shared
by no other record in the table? A single field primary key would let
you do the following:

UPDATE YourTable
SET FieldA = "Bison"
WHERE PrimaryKeyField =
(SELECT TOP 1 PrimaryKeyField
FROM YourTable
WHERE FieldA = "Alley Cat"
ORDER BY PrimaryKeyField)

IF you have a multi-field primary key, post back.

 

[Guest]

Thanks John, if I had SQL code for you, I would be glad to post it. I've
tried putting code that looks similar to the lines you've provided into
numerous iterations trying to get anything to return but I was extremely
unsuccessful. It had occured to me that this code structure does not work in
Access VBA but only in SQL.

I like the look of the line
WHERE PrimaryKeyField =
(SELECT TOP 1 PrimaryKeyField..... though

If I was to try to bluff my way through this I would try the following. I
can't do it right now because my application keeps erroring - I've tied it
into a knot that I'll have to sort out in the morning

UPDATE tblRaceAdmin
SET FieldA = "LaneNumber"
WHERE ParticipantID =
(SELECT TOP 1 ParticipantID
FROM qryAssignLane1
WHERE FieldA = 0
ORDER BY LapCountLane1, Ascending)

The intended operation is to create the temporay table tblRaceAdmin
The default value of "LaneNumber" is 0
Assign Lane 1 to the racer that has not been assigned a lane with the least
laps recorded in Lane 1
Then repeat the process for lanes 2 thru 4.

Essencially tblRaceAdmin is a Query that is made to hold race setup data and
get race result data written to it for the purpose of calculating all sorts
of geeky type things like wagering, handicapping, ratings etc etc... The
race data gets written to permanent tables forever and ever and the
tblRaceAdmin is deleted and recreated for the next race.

I'm open to any other method of achieving the same result

Norm

 

[Rick Brandt]

Thanks John, if I had SQL code for you, I would be glad to post it. I've
tried putting code that looks similar to the lines you've provided into
numerous iterations trying to get anything to return but I was extremely
unsuccessful. It had occured to me that this code structure does not work in
Access VBA but only in SQL.

That's not code. It is SQL.

Open a query and on the menu View - SQL. That's where it goes.

 

[Guest]

Thanks Rick, and thanks John

You were both very helpful. Between the two responses and my now knowing
that there is a separate SQL view area in the queries, I bet I can start
making progress again.

Thank you to all.
Like I said, I don't post often, but I get tons of answers from this site

Norm

 

[John Spencer]

I did make an error in the SQL that I posted. The = should be an in


UPDATE YourTable
SET FieldA = "Bison"
WHERE PrimaryKeyField In
(SELECT TOP 1 PrimaryKeyField
FROM YourTable
WHERE FieldA = "Alley Cat"
ORDER BY PrimaryKeyField)

Or alternative


UPDATE YourTable
SET FieldA = "Bison"
WHERE PrimaryKeyField =
(SELECT First(PrimaryKeyField) as AnyRecord
FROM YourTable
WHERE FieldA = "Alley Cat"
ORDER BY PrimaryKeyField)


---
John Spencer
Access MVP 2001-2005, 2007

Thanks Rick, and thanks John

You were both very helpful. Between the two responses and my now knowing
that there is a separate SQL view area in the queries, I bet I can start
making progress again.

Thank you to all.
Like I said, I don't post often, but I get tons of answers from this site

Norm

--