Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2014, 08:57 PM   #1
malvenx
Newly Registered User
 
Join Date: Dec 2014
Posts: 13
Thanks: 0
Thanked 2 Times in 2 Posts
malvenx is on a distinguished road
Help with query for school project please!

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!


malvenx is offline   Reply With Quote
Old 12-06-2014, 04:04 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 799 Times in 786 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Help with query for school project please!

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.
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 12-06-2014, 02:32 PM   #3
malvenx
Newly Registered User
 
Join Date: Dec 2014
Posts: 13
Thanks: 0
Thanked 2 Times in 2 Posts
malvenx is on a distinguished road
Re: Help with query for school project please!

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 is offline   Reply With Quote
Old 12-06-2014, 10:40 PM   #4
malvenx
Newly Registered User
 
Join Date: Dec 2014
Posts: 13
Thanks: 0
Thanked 2 Times in 2 Posts
malvenx is on a distinguished road
Re: Help with query for school project please!

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
malvenx is offline   Reply With Quote
Old 12-07-2014, 02:49 AM   #5
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 799 Times in 786 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Help with query for school project please!

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
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 12-07-2014, 05:54 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,560
Thanks: 55
Thanked 1,872 Times in 1,822 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Help with query for school project please!

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help on BD for school project...please FNG General 7 02-26-2013 04:54 PM
school sports selection project zyzor General 1 02-07-2009 06:36 PM
Question Need help with a school project!!! dbay General 6 08-29-2008 02:08 PM
Help with School Project... MattD23 General 10 11-19-2007 04:05 PM
Project for school Help! Noorman84 Forms 2 04-14-2006 06:20 AM




All times are GMT -8. The time now is 02:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World