Ask a new question

hostel management system

i am creating a simple hostel management system, whereby a student books and pays for one or many rooms. Each room has its price. i am having issues with the query section . i want it to be that, when a student books for a room, it shows "booked", and when he/she pays for the room, it shows if the student made HALF PAYMENT(where amountPaid<roomPrice) or FULL PAYMENT(where amountPaid>=roomPrice). please crosscheck the ER diagram as well, thanks.

40 people found this helpful

Was this discussion helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this discussion?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this discussion?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

How is RoomPrice determined? tblRoom.CostSem?

That would be a mistake. Room prices change all the time so the room price must be copied to tblBooking, similar to the Northwind sample application's OrderDetails.UnitPrice.


After that it should be simple to compare the sum of the payments (DMax, or a Totals query) to tblBooking.RoomPrice.

-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

ok thanks, BUT  i'm still having issues with it, can i send you the access file itself for further assistance as i am still new to access.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I'm having a little trouble with the tables...

 

You have Rooms which Students can Book.  What does the Staff do?  Are they the people that handle the day-to-day stuff?  Why are they attached to the Payment table?  And since the Payments are actually for the Rooms shouldn't tblPayments be attached to tblRooms?  I'd also seperate the Room Rate to it's own table because the price could change between bookings (go up in price, specials could be offered in the off seasons) and you'll want to make sure the Student gets the rate they were told when the room was booked.

Gina Whipp
Microsoft MVP (Access 2010-2015)
https://www.access-diva.com/tips.html

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

yes the staff is in charge of the day-to-day stuffs. in this scenario,  he can check how many students booked/paid for a room(s) as well as the cost of the room and how much the student paid. i tried linking the payment table to the room table but it gave me problems when creating SELECT query.
 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

this is a class mini-project, that's why i didn't take the room price changes into consideration. my main aim to make a query such that when a student pays for the room, it shows if the student made HALF PAYMENT(where amountPaid<roomPrice) or FULL PAYMENT(where amountPaid>=roomPrice).

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Gina,

No, the way I understand it tblRooms is a stock table with the available rooms, while tblBooking holds all bookings for all rooms. Payments are for a booking. That part of the design looks correct from my perspective. If we want to record which staff member collected a payment, then that part is correct as well.

However tblBooking.RoomPrice is missing.

-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

No, this is YOUR class assignment. YOU have to learn something from it. We will nudge you in the right direction.


-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Tom,

 

Well, glad to know I'm not the only one!  To me Simply have a job to and should not be attached to anything... at least, not anything I see but that would be IMHO.  And, yes, RoomPrice is missing.

Gina Whipp
Microsoft MVP (Access 2010-2015)
https://www.access-diva.com/tips.html

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

LOL, true... although i'm done with mine, i'm assisting my classmates resolve theirs. As regards this particular database, i have done the suggested amends but still don't know how to go about the half and full payment aspect.

 

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Tom suggested a query... have you tried that yet?
Gina Whipp
Microsoft MVP (Access 2010-2015)
https://www.access-diva.com/tips.html

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Discussion Info

Last updated October 27, 2023 Views 2,997 Applies to: