Help with query for school project please!

[malvenx]

Hello, could anyone help me with a query for my school project?




The database represents the campus housing, and I need help with a query that assigns students to newly available rooms, and then updates the waiting list.


Once a Student graduates(indicated by GraduationDate), he/she will be unassigned the RoomID which then makes that room available in the Room table.


The next student on the waiting list would then be assigned the available room, and the waitlist number for that student will either be be updated to a null value (easier, and keeps the wait list column static) or can be dynamically updated(not sure how to do)


I would very much appreciate any help. Thanks!

[June7]

I don't know what you need. If you want to remove the RoomID from the student record then do data entry to edit the record.

How should a query 'assign' something? - you need to do data entry or run a VBA procedure.

[malvenx]

so when graduationdate = todays date, then roomid is edited to be null, because they no longer need the room. The room(which is now available) would be assigned to the first student on the waitlist(the lowest waitlistnum)

any thoughts.
Thanks!

[June7]

An UPDATE sql action might do what you want. You can either build an Access query object and manually run it or can use VBA code to run the SQL UPDATE.

CurrentDb.Execute "UPDATE Student SET RoomID = Null WHERE GraduationDate=Date()"

[malvenx]

thank you, i have an idea how to do this if only Access had a record macro function of some sort like excel. Otherwise, im kind of lost when it comes to access query objects.

[June7]

Building query objects is basic Access functionality. Use the Access query designer.

Access does have some automatic coding capability, such as when command buttons are placed on a form, can invoke the Embedded Macro wizard. I never use the wizards.

[malvenx]

I was thinking about three queries:


1) Assign student a 'null' value for RoomID if GraduationDate <= Date()


2) Then I need a query that assigns the recently unallocated room to the first student on the waiting list


3) Then the final query would assign null values to ApplicationDate and WaitlistNum because they are now assigned a room.







This is an extremely introduction course to database management and we learned concepts without much practical use(which is a shame), so im hoping someone can help me with the query language. Thank you very much

[June7]

Doing items 2 and 3 with queries might be possible but would be tricky.

UPDATE Student SET RoomID=[enter room ID] WHERE WaitListNum=DMin("WaitListNum","Student")
Or
UPDATE Student SET RoomID=[enter room ID] WHERE WaitListNum IN (SELECT Min(WaitListNum) As MinNum FROM Student)

UPDATE Student SET ApplicationDate=Null, WaitListNum=Null WHERE RoomID=[enter room ID]

Options for entering value into [enter room ID]:

1. with a query object, query parameter input popup prompt

2. also with a query object, reference to a textbox on form

3. VBA code runs SQL action statement and references textbox on form
CurrentDb.Execute "UPDATE Student SET RoomID=" & Me.RoomID & " WHERE WaitListNum=" & DMin("WaitListNum","Student")
CurrentDb.Execute "UPDATE Student SET ApplicationDate=Null, WaitListNum=Null WHERE RoomID=" & Me.RoomID