Function max in update query in access

[Dontlk]

Hi, I tried to get the max date value, but I got an error - Cannot have aggregate function in WHERE clause (((Room.status)="free") AND ((Max([Reservation.date_checkout]))>=Date()));

Code: UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "occupied"
WHERE (((Room.status)="free") AND ((Max([Reservation.date_checkout]))>=Date()));

I also tried something like that, but without any luck:
UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "occupied", date_checkout = (SELECT MAX(date_checkout) FROM Reservation)
WHERE (((Room.status)="free") AND ((Reservation.date_checkout))>=Date());

Thanks in advance!

 

[vbaInet]

You will need to use DMax()

 

[Dontlk]

Ok, but where should I use it, because when I try it like that:

UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "occupied"
WHERE (((Room.status)="free") AND ((DMax([Reservation.date_checkout]))>=Date()));

there's a "invalid number of parameters" error

-----
UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "occupied", date_checkout = (DMax(date_checkout) FROM Reservation)
WHERE (((Room.status)="free") AND ((Reservation.date_checkout))>=Date());

This doesn't work either - "Syntax error (missing operator) in query operation"
Maybe I do something wrong, I don't know

 

[spikepl]

Lookup DMax in the documentation

 

[Dontlk]

Can't you just tell me how should I use it? Because I have no idea... :/

 

[Dontlk]

Ok, I think I managed to do it, but it's not really what I wanted it to do, because it only shows one record with the latest date, is there any chance to show all the records, but if for example we have 2 records with the same id, to show only one of them, the one which has the latest date?

 

[vbaInet]

Can you elaborate more on this. What SELECT statement are you using?

 

[Dontlk]

Well, basically I'm doing a database for a hotel and I wanted to do an automatic update query to change status(by comparing the dates - whether todays date is after the date of checkout or before) and it's working fine, but when I have two rooms(records) with the same id in Reservation table, query changes the status back and forth, becuase my database remembers old reservations and according to them room should be free, but in fact it's occupied, so I thought I should use max to only choose the latest date and then update the status without considering the earlier reservations, but unfortunately my query only works for the latest date in general, when in fact I only want it to update every record, but when when there will be two with the same id, it should only update the one with the latest date. So I don't know what exactly should I do right now.

I have 2 queries - UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "occupied"
WHERE (((Room.status)="free") AND ((Reservation.date_checkout)>=Date()));

and

UPDATE Room INNER JOIN Reservation ON Room.id_p = Reservation.room SET Room.status = "free"
WHERE (((Room.status)="occupied") AND ((Reservation.date_checkout)<Date()));

 

[Dontlk]

Any ideas?

 

[vbaInet]

Any ideas?

It's the weekend and most of us aren't on here all day

So, I don't think you need a separate Status field. If a room is free, the Due Date will be less than or equal to today's date.

 

[Dontlk]

But for example, during reservation when a guest is choosing a room, how can I for example show in a combo box (or any other way) only free rooms? Because if I create a query, in which I'll add a field from reservation table(date_checkout) and in criteria a <Date() it will only show rooms which have been occupied in the past(their id is in table Reservation), what about the others? also, if there are two fields with the same room id in reservation table, it will show both of them, not the latest one...

 

[Dontlk]

It's last thing I have to do to finish my database, so is there anyone here who could help me(yes, I know it's Sunday)?

 

[vbaInet]

I can't really envision it because I don't know what tables you have or how they relate to each other. Perhaps you can upload a sample db with only the relevant tables and queries + some test data.