Help with query for school project please! (1 Viewer)

malvenx

Registered User.
Local time
Today, 12:57
Joined
Dec 6, 2014
Messages
13
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!

 

namliam

The Mailman - AWF VIP
Local time
Today, 20:57
Joined
Aug 11, 2003
Messages
11,696
why update it in the first place?
Why have it in the first place?
IMHO it would be so much simpler to use a "entry date/time" and simply order you waiting list by that, unless there are reasons why the list needs a "special" thing like a priority group or something.
 

malvenx

Registered User.
Local time
Today, 12:57
Joined
Dec 6, 2014
Messages
13
Thanks for the response, I don't need to update it necessarily, i just need a way to assign students a room based on availability.

When a student GraduationDate=TodaysDate, I need a query to assign their room to the next student on the waiting list.

I am at a loss at how to do this.

Thanks
 

malvenx

Registered User.
Local time
Today, 12:57
Joined
Dec 6, 2014
Messages
13
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
 

namliam

The Mailman - AWF VIP
Local time
Today, 20:57
Joined
Aug 11, 2003
Messages
11,696
1)
By assigning a null value to your relationship between Student and Room, you lose your historic information as to who had the room when.
I would say this is undesiriable.... and IMHO is a design flaw in your database

2)
"Complex" updates or assignings like this you dont do by queries alone, you use atleast some VBA

3)
Again with this updating of the waitinglist you lose information, perhaps waitinglistnum I can imagine, however applicationdate to me would seem information you dont really want to lose.
Waitinglistnum is basicaly a dependance on how many students dont have a room yet and signed up before this student. A list like this that needs "constant" or regular updates normally wouldnt exist
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:57
Joined
Jan 23, 2006
Messages
15,364
malvenx,

You should start a database project (any project really) with a clear statement of the requirement.
The simpler you can describe the entire problem/opportunity, the better you understand the situation.
I see nothing in your post that suggests or explains the need for Employee, WorkOrder, DiniingHall,
Laundry or RecreationRoom.
They all could be in scope, but should be placed in context before building a model or database.

Here's an example from RogersAccessLibrary:

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information
about employees, the employee's job history, and their certifications. Employee information includes first name,
middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address.
Job history would include job title, job description, pay grade, pay range, salary, and date of promotion.
For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator).
Employees can also earn certifications necessary for their job.
 

Users who are viewing this thread

Top Bottom