Selectively update one value in a table by lowest date

[cheese]

I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.

The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.

Summary:

• One "Game"
• Several "GameCopy" using "Game" as a foreign key
• Reservation table with possibly several reservations for the same Game - not GameCopy(s)

In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.

As an idea would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.

Obviously if I simply:

UPDATE Reservation SET Reservation.Available = Date()
WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;

...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.

 

[nathansav]

you need to use the pk of the reservation table to.

 

[cheese]

I am surprised that no one has responded (with an actual answer) to this because it is actually relatively straight forward (in a manner of speaking). You have to use the TOP keyword in SQL as in:

UPDATE TableName
SET Fieldname = "Whatever"
WHERE Record_ordering_field =
(
SELECT TOP 1 Record_ordering_field
FROM TableName
WHERE Stuff = Stuff
ORDER BY Record_ordering_field
)

That is basically how it is done (whilst using appropriate values where necessary). Some people say it is SELECT TOP (1) but this will produce an error in Access (for some odd reason) so you have to use 1 on its own.

 

[jdraw]

It may actually be relatively straight forward (in a manner of speaking), but you are intimately familiar with the situation and the terminology. I find the instructions convoluted, but feel you don't have to use TOP 1, although as you have shown and experienced, that will work.

I think the general solution to this "relatively complex update" is to be clear on what exactly is to be updated.

If it is the oldest reservationDate then perhaps sql along this line

UPDATE TableName
SET Fieldname = "Whatever"
WHERE
reservationDate = (Select Min(reservationDate) from TableName)

The key as you have noted is to find 1 record to Update.

Top 1 if you sort them correctly, or
Min (reservationDate) you may have some WHERE condition as well

Anyway glad you have it resolved.

 

[cheese]

The record ordering field was just an auto numbering field that was not the PK. The PK is actually a composite (or compound - I never remember which one is which!) I think it could have been adapted to accept the dates instead of an auto-numbering routine. It is less than ideal and would mean that things would go slightly wrong if people were not to do it in real time. If people started using older dates then it could mean the the reserved game goes to someone in the wrong order.

I am presuming you are saying that I could have missed off the "1" in the "TOP 1". I am not too familiar with it and maybe could have left it off.

Maybe my question was a bit long and arduous but I was trying to make it as detailed as possible. I only said "relatively complex" because I am sure some people would consider it very simple. It seems simple to me now anyway.

 

[cheese]

If it is the oldest reservationDate then perhaps sql along this line

UPDATE TableName
SET Fieldname = "Whatever"
WHERE
reservationDate = Select Min(reservationDate) from TableName)

Actually, thinking about it, your solution seems much more appropriate.