Building a database system to book students in to hotels

[SkillipEvolver]

Hello everyone,

I am wondering if anyone can help me.
Ive started a project to make a system where staff can use access to book students in to 'home stays' in the area.

The company has been going for a long time and has ~600 students and ~160 different homes.

The homes have varying number of rooms.

The data has no uniformity at the moment, been gathered thru the years and so for this to work I think I have to create a new table with clean data.

For the students, I have about 26 columns of info.
(First name, surname, dob, Tel no, email, nationality, gender, like pets? Allergies?

ThTs the most of it but I have tonnes of columns extra to those for YES NO answers to things like: has ADHD? Has asthma? Allergic to dog? Allergic to cat?

All in all, about 26 columns.

I have to zoom out very far to see it all. It's difficult to work with.

I believe this is what's called a Flat file.

Eventually I want to be able to have various forms.in access

1) one allowing stAff to find/cycle thru students
2) another for the homestay (with various properties like: address, number of rooms, owner, names of any children living there)

and to be able to place those students in the homestay.
The staff will.make.the decisions about who.is right for which home so I don't see. needing any complicated rules for making matches yet. (Maybe later)

But what.i eventually need is a booking form where they can choose students from a drop-down, the home from a drop-down (address) and a calendar of 28 days on the right side of a form to be able to place the student at that address for (usually a week at a time).

I don't know how to create this yet and I've been trying to think about it but I'm going through a very tough time at the moment and I'm not very clear.

How can I think about what relationships I need to set up between these 2 tables?

I'm sure to the experienced database maker, my excel table (currently entering student info data in to spreadsheet) looks ridiculous.

Is there any way I can spread the data across multiple sheets and still Import it all as one giant data source when I bring it in to.access?

Ie is it possible to say... Have 3 sheets and each sheet contains say only 8/9 columns instead of all on one sheet? If I flip between one sheet and the next is there a practical way I can keep track of which row I am.entering data on, so that I don't put the wrong data in the wrong field? Another way of putting it, am I able.to kind of 'preview/sneak peak' /refer back to.the first sheet to see the student name to make sure I'm.entering data on the right line?

I think I have a lot to.learn here and feel like an amateur putting all the data on one sheet. It's ardarous enough entering ~600 lines with the potential to enter the data wrong as it is. Is there a better way to do this.

It's all in the goal of being able to book students in to rooms.

Any help appreciate. I very new to this and currently having a very difficult time with anxiety, which makes it hard to concentrate and think logically through problems.

 

[Uncle Gizmo]

Well you've done the right thing in laying out an overall picture, however you've also got lots of questions in there, which is usually a problem, as it puts people off from answering! I think your first question should be for some general guidance on how to proceed. My answer to such a question would be:- I'm thinking that it's too big, it's too big a task for you to take on as a beginner. I mean, I think I would struggle with it myself!

One approach which is quite useful, is to find out what is the most labour intensive part of the process at the moment. In other words what improvement, what simplification of the current process would be most advantageous to everyone using the system? Find that, and if you can move that to, do that in MS Access, then you've got a good start. Then you can think about moving the rest of the data over as you go.

I get the impression that some of your data is in more of an Excel format, if that's the case then you might find this blog of mine useful. "Excel In Access" The tool I provide is available for free when you sign up for my newsletter.

 

[SkillipEvolver]

To be honest I don't really know what the most labor intensive part of the process is at the moment because they don't have a process in place yet really.

They just want to be able to select a student from the dataset and add it to the available slot /house and generate a letter from that saying... So and so student is going to be staying with you on... This date until this date.. is there any harm in me putting at the data in one Excel sheet to begin with? and then bringing it in to Access (creating tables from it all?)

Eg one table is. (student details: name, address, Tel, email). Next table.is: (next of kin info: NOK name, Tel, email add). Another table is :. (student medical info: allergies, dietary concerns)

I need to get starting on something tomorrow

 

[SkillipEvolver]

I'm going to continue bringing the student data together in one table tomorro and hope for the best for now. The way I at it, at least if I've got it all on one place I can turn begin to divide it up into different tables in Access

 

[isladogs]

Skillip
Your previous thread was cross posted on 3 different forums.
I provided a link to explain why it matters and gave links to the threads on each forum.
You didn't even acknowledge the point being made.

You've now done the same again, Cross posting at Access Forums.net with no link provided. If you do cross post, be upfront about it and please provide the links at each site yourself

I will copy this to the other forum but leave you to do the links

 

[SkillipEvolver]

Hi Colin, I'm very sorry. I didn't mean to offend or break rules. At first I didn't realise the forums were connected. I'm going through a very difficult time at the moment/possible breakdown and have a tendency to overlook things. I am just about keeping my stuff together in the real world. When I post again, I'll make sure I only post in one place, is that ok?

 

[isladogs]

If you read the link I provided, you will see there is no objection to cross posting as long as you do what I asked. Be upfront & supply the links to other forum threads

People who persistently ignore the guidelines find that their posts get very few answers

Here again is the link for you to read if you didn't do so previously:https://www.excelguru.ca/content.php?184

The forums are NOT connected.
There are a number of people who are active on each forum but many others who won't be aware of answers given elsewhere.

 

[Frothingslosh]

To reinforce what Colin said, we really have no objection to cross-posting itself as long as you're upfront about doing it and explaining why you're doing it.

On the other hand, if I spend 3 hours coming up with a solution for someone, only to learn they got an answer somewhere else the day before, I'm going to get a tad testy, I'll remember their name, and I'll be far less likely to even look at their posts in the future, especially if it becomes a pattern.

 

[mike60smart]

Hi

Would you book a student in a specific address ONCE Only?

Or could the same student return to the same address for another booking period?

 

[bastanu]

Hi Skillip,

For quite a few years I worked on a database used to manage international student programs in Canada (ISPDatabase.com) before it went online. As uncle Gizmo said it is a big project you are about to embark on. You might be able to find some templates already available. I think most of your questions/issues are easily achievable in Access.

You will need a student table, a homestay table, a student-homestay linking (joining) table, potentially a homestay family table (to track criminal record checks, ages, etc.), homestay notes table, etc.

I am attaching a screen shot of a homestay module so you could get an idea how we organized it.

Good luck!

Cheers,
Vlad

 

[SkillipEvolver]

Hi

Would you book a student in a specific address ONCE Only?

Or could the same student return to the same address for another booking period?

Only once per student

 

[SkillipEvolver]

Hi Skillip,

For quite a few years I worked on a database used to manage international student programs in Canada (ISPDatabase.com) before it went online. As uncle Gizmo said it is a big project you are about to embark on. You might be able to find some templates already available. I think most of your questions/issues are easily achievable in Access.

You will need a student table, a homestay table, a student-homestay linking (joining) table, potentially a homestay family table (to track criminal record checks, ages, etc.), homestay notes table, etc.

I am attaching a screen shot of a homestay module so you could get an idea how we organized it.

Good luck!

Cheers,
Vlad

Vlad that looks brilliant. I think the company I work for would consider buying that design you made. I am just in too deep with this and bitten off more then I can chew I think. I can't think straight at all, I don't know Access well enough to design something like that and my job depends on it.

I've been up all hours of the night worrying about it, whether to even quit the job. My life has turned upside down since I found out about my mother's illness. I don't want to quit this job but my anxiety has gotten out of control. I don't want to sound like a bottom feeder but if there's any way at all you'd consider making your work available, just something to grip on to it would make a world of difference.

 

[SkillipEvolver]

Hi Skillip,
Vlad

That screenshot looks good Vlad.
So how does the room availability part work?
The way it looks to me.. if you have a student who is currently booked in to the home, your data will show AVAILABLE ROOMS=0 ?
I have no idea how you do it but I'm presuming that that box (available) updates itself depending on the date a student is booked in? Eg of their FROM and TIL dates includes the current date, it shows unavailable?

I like the layout, I would be comfortable displaying the h homestay information like you have at the top but how to actually do the booking functionality I am still very clueless about. Could you explain how your did it?

Is it all to do with a joining table?

In my setup we have eg :

1 house, it may have 2 rooms, each room may have a single bed in it.
So... Basically multiple beds possible in 1 homestay. I am really confused about how to design tables and relationships to make the ability to book students in to individual beds.

 

[Gasman]

Only once per student

Really !!
If I was a student and I found what I thought were decent digs, I'd be booking them again.

 

[bastanu]

Yes, the booking happens in the join table (attached). There is a student form where you add the bio-demographical infor for each student (name, gender, country, language, address, phone,email,,etc.). That form has a subform (linked via StudentID) showing the homestay placement history for that student (all the families where the student was placed) with the join/left dates. There is also a boolean field used to flag the "current" record (it could be automated using the today's date and the range of dates but some clients wanted the ability to mark a student "current" or "placed" for future dates). And then there is the homestay form I uploaded previously that shows the info about the families with the subform showing the student history (all the students that stayed with that family), linked via the FamilyID. Navigation between the forms was achieved by using VBA in the double-click event of the student name of family name in the subforms (double-clicking a family name would open the family form and go to that record).
Some versions also had some fancier matching modules (students and families can have preferences such as smoking, diet, religion, etc.) that would match students with potential hosts.

And yes, the available rooms was simply using a dcount (=Nz(dCount("[StudentID]","qryStudentHomestayLinkingSorted","[FamilyID] = " & Nz([FamilyID],0) & "AND [CurrentHomeStayFamily] = True"),0) to count the number of "current" students and subtracting that from the total number of available rooms.

Cheers,
Vld