Booking Database

[Magikroom]

Ok, here's the scenario: I have a training room with three workstations in for the purpose of E-learning (Microsoft Courses etc). I've set up an Outlook template (in a shared folder) for staff to choose a course that they want to do, what date they want to do it and for how long...basically a booking form.

I've started on the database to allow two people to input the Outlook results into a form (see attached) What I want to do is two things:

1. As the room will be available only to a maximum of 3 people at one time, I want a way of validating the database so that when someone enters the details of a booking, if three people have already booked the room at say 10:00 until 12:00 - that a message box will open up and say "no more booking's allowed" - something like this anyway.

2. I managed to find on the net a report that looks like a Calendar and incorporated it into my database, but it will only report out one record...If I have 10 people booked on one day I want to be able to show it, but in the calendar format.

Sorry to ask so much, but this has been puzzling me for ages and I'm ashamed to say that I'm just not clever enough to work it out...can you help?

You will have to unzip and then UnRAR to use the Access 2000 Database as it was slightly over the limit.

 

[aleb]

You can go the other way here.
You can do it with MS Outlook, you would need to set up in address for the conference room in your corporate mail box.
See attachment how it is done in our company

 

[Magikroom]

Thanks for the reply

You can go the other way here.
You can do it with MS Outlook, you would need to set up in address for the conference room in your corporate mail box.
See attachment how it is done in our company

The reason why I want to keep it in access is that inside the shared folder (on the network) I will be outputting the reports (Calendar) so that any Satff that are interested in booking a course can first look at the Calendar (Report) and see if a certain time is booked...then they can complete the Outlook Template.

All workstations have now had an Access Viewer installed so that they can see reports...Ideally I would have like to have done it over the Intranet, but the main Intranet Team don't want to get into dynamic stuff...they're stuck in 90's with static html...I once put together a contacts database using ASP and uploaded it to the Internet so that they could demo it...basically I wanted their enthusiasm etc...they saw it and wanted to report me for breaking publishing laws...great IT Teams eh!

Anyway, I could probably get away with doing the ouput to a calendar, a different way...but I need to sort out how to allow only three bookins at one time...cheers

 

[aleb]

Magikroom,
I am sorry but your design seems to lack some vital features. I will look at your db and correct them, then you will review corrections and except/discard the changes.

 

[Magikroom]

Cheers...

Thanks Aleb, any help you can offer, would be great...

 

[aleb]

Open main form and inquire on the 8th of October.
The rest you will figure out
Your report works now.

 

[Magikroom]

Thanks Aleb

It will take me some time to figure out how (and what) you've done it, but thanks a lot...maybe a simple breakdown of your process would help...sorry if I'm asking too much.

 

[aleb]

Booking date is now unbound field since it is not used anywhere but selecting of the booking date.
Main form is unbound as well because I needed to have a list of courses AND at the same time a list of assigned employees. if I bind the main form to the assigned courses table it would give me only one record if I also put a subform of assigned to the course employees on it. Since the timeslots are on the form and visible there is no need to worry about overlapping of times.
Also the number of people assigned to the course is also visible I didn't set any rules on it. if you want you can actually set up a field at the footer of the Assigned employees subform to count the number of records ...
=iif(count(feeid)>3,"The number of people exceeds allowed value","").
May be in future you would also get rid of the "timslot" table, when you realize that the course length can be any time period not just the ones you have defined in a drop down list.

Now your dbase structure is:

Courses one-to-many to assigned courses
Employees one-to-many to assigned employees
Assigned courses one-to-many to assigned employees.

Now about the report: you have laid out the report to print the values across then down, but you didn't consider that all the values in the query behind that report would also follow the same rule. Instead you wanted the report to have mixed rules - the main report would print values accross then down and your subreport would print values down then accross. That is why I have deleted the values I didn't need and added the subreport. This report is kind of nice ... i am going to use it and post as well as an example I hope you don't mind

What you would need to do is to reassign the buttons on your switchboard so that they would do whatever you needed to do. And to add some fancy coloring, borders ... at your own taste.