Accommodation Database - Availability With Shared Room Capacity
Feb 6, 2007
Hi,
I have been reading throguh various articles and learning a lot from you guys, so thank you very much. This place is a fantastic source of information, by far the best of anything i have found online.
I have a question which i would be grateful if someone could advise upon.
Access 2003. I have 4 tables:
list_accommodation (holding host family address details who offer accommodation to students)
id (autonum)
name (text)
address (text)
etc.....
list_rooms (table of rooms offered by host families)
room_id (autonum)
host_id (number) - joined to list_accommodation.id
room_name (text)
capacity (number)
students (student contact details who want accommodation!)
id (autonumber)
name (text)
dateofbirth (date)
address (text_
etc.....
bookings_accommodation (to hold the accomm bookings)
booking_id (autonum)
room_id (num) - joined to list_rooms.room_id
student_id (num) - joined to students.id
start_date (date)
end_date (date)
This structure therefore enables me to query if rooms are booked between dates:
SELECT bookings_accommodation.room_id, bookings_accommodation.start_date, bookings_accommodation.end_date
FROM list_rooms INNER JOIN bookings_accommodation ON list_rooms.room_id = bookings_accommodation.room_id
WHERE (((bookings_accommodation.start_date) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d",-1,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR ((([end_date]-1) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d","0",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR (((bookings_accommodation.start_date)<DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date])) AND (([end_date]-1)>DateAdd("d","-1",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date])));
and therefore, using the 'booked' query i can therefore find out rooms are available.
SELECT list_rooms.room_id, list_rooms.room_name, list_rooms.capacity, list_rooms.description, list_rooms.host_id
FROM list_rooms LEFT JOIN qry_rooms_reserved ON list_rooms.room_id = qry_rooms_reserved.room_id
WHERE (((qry_rooms_reserved.room_id) Is Null));
----
so, now i have found out that there is a necessity to enable students to share a room (the bookings for a room may not happen at the same time). This means that i have to determine the capacity of a room and therefore if
the capacity is not full, then show as available.
I was wondering what the best approach to this may be?
I have added 'capacity' to list_rooms, but i am unsure as to how to go from there....
Any thoughts and advice woudl be really appreciated.
Hi Everyone, hope someone out there can figure this one out.
I am developing an access database and forms based on a hotel booking system. There is a tblRoomState table, which holds the roomID, the date and the state of the room, (so for a 3 night stay, this table holds three entries). I have a query which returns any roomID's from the tblRoomState table which have a state value of 1 at a specified date. I want to run another query which returns roomID's from the tblRoom table which are not part of the recordset of the first query.
Since there is no records held in the database for rooms which are not booked, the only way to access "availability" is to use a recordset of rooms which are booked and test against that.
I am trying to create a database that will record the availability of volunteers for different events.
So far, I have used access to create an availability table, that contains fields for event, date, name, shift 1, shift 2, shift 3.
I now want to create a form that will filter this table for a given event, then layout the form so that names appear down the left, date across the top, and then the shift 1 to 3 fields (which are yes/no fields) to appear in the body of the layout.
This is so the organising team can quickly enter this information (received by phone, email, fax) into the database. I have had a go with subforms within subforms but so far drawing blanks.
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
Im having some problems sharing a database. I need multiple users to be able to open the database at the same time. I have check TOOLS - OPTIONS - ADVANCED and my option is set to shared, but for some reason it will still only allow 1 user to open it at a time. Does anyone know why this could be?
hello everyone... with the help i have gotten from this site I embarked upon producing a online booking databse for my workplace ...
since the work enjoys having an intranet i simply placed the database in a shared folder and things have worked great for nearly a year ...
Ive tinkered and modified and everyone is happy till today ....
while an administrator was deleting a booking things went haywire ....
a message came up about couldnt delete because someone else was deleting ...things froze and a message about path problems came on the screen..
the only thing we could do was delete the database and replace it with the backup copy we have.... everything worked great for an hour or so and then things went wonky again with same error messages...
also I noticed that the database grew in size from 1.6md to 2.5 mb ....
again I replaced it with the backup copy and did a repair and compress ..and it seems to be ok ..
but im dreading tommorrw since i anticpate it happening again ..any suggestions appreciated... ps ..the databse is a booking form for booking ict suites in a school and I am the schools technician
Have created a database that will be placed on a shared drive, giving access to others to add, delete, modify records. That aspect of it is fine -- but how to I prevent someone from deleting a field?
I have a problem related to shared access to a MDB file:
- I designed an Acccess Database and need shared access to everybody for read forms , and also I need (just for me) to do some "real time" modifications in the tables. I work in a huge company and cannot make changes in the Access of each user, just can work on my laptop. The file is placed in a shared folder.
- I can assign policies to the folders (for example I made a read only folder, with admin rights for me, but when somebody opened the mdb, I got an 'open only advice, and couldn't update any table; It's curious because the users coudn't generate the LDB file but still the access denied me the chance to modify any table when the file is opened by anybody).
- I tried playing with tools-security, but I cannot change every user settings, it didn't helped me.
I've been looking for related links to this problem but still I couldn't solve it. And at this point I wonder if it's just possible to do that?
I need securing an Access database. I am about to put the database on a shared drive. I want myself to be able to do anything (of course), my co-worker Paul to be able to ONLY enter information (along with being able to view everything like forms and reports), and my boss to ONLY be able to view everything (form/report wise).
I found this post [URL] .... about how to secure a database. I spent all day doing it, but it didn't work at all! I just got a bunch of errors whenever I tried to do anything, so I deleted the database and re-used an earlier version.
I have recently been experimenting with adding an Access database to a shared folder (in a network HomeGroup) and have it working on two networked PCs. Then tried the same with the database split but I get a path error on the PC joined to the HomeGroup (where the database resides) whenever the backend (tables) are accessed by a form or from the Tables listing. If I however open the backend file directly from the file directory, the tables all open as expected.
The path stated as being invalid in the error message starts with "C:Usersetc..... instead of LAF-PCUsersetc..... so it looks (to me at least) that it is attempting to load the file from the local hard drive rather than the host computer.
Why a split database works differently in this situation than a non-split.
I am going to put my access database on a network drive so that four different computers can have access it. However will user-level security work if I implement it? For example, from one of the computers I implement 2 different usernames and passwords. If one of the other computers tries to access the database will they also be presented with the logon screen?
I have made this database for a company that I work at, and I seem to be haveing some problems. I split the Database and placed the "'back-end" on the company's shared drive, however it seems that only when I log in as myself, on any computer, that it works. On other people's profiles, when a form is opened, there is nothing there, it is blank, no textboxes, labels or anything. However, if you use the database window to open one of the linked tables, you can see all of the existing data and cells just fine. I am only 17, and I have used Access for only two weeks, so I may have missed some setting in the splitting, or a database property that is affecting it. Please help me, as I need to finish this DB ASAP.
PS: the folder settings for the shared drive (at least as far as i know...) allow full access, so there should not be an issue there.
We have a database on a PC located on our network. The database is located in this computer's shared folder. Another database grabs data from this database occasionally. We're running into a problem where we cannot open the DB located in the shared folder (permissions granted) without it being a read only instance even with no users accessing the DB.
Basically what im trying to do is a database which checks to see if something is available.
The database is a booking system for services. I need the database to check a "booking" table to see if there is a booking on a specific date. If there is, then you cannot make a booking. If there isn't then you can.
In my "booking table" there is a list of dates of bookings. Each booking lasts a week. So if someone wants to make a booking, it cant just check to see if the first day is available, it needs to check for 6 additional days. (i.e. a booking on 01 Jan 2006). If someone wants to make a booking, the database needs to account for a whole week, so the next available booking would be on 07 Jan 2006).
I have 6 tables and these are similar. In the table is three fields. These are ID, barcode and scandate.
How much data records Access can save to per table? about...I will be about 1000-3000 data records to field per table in day.
Is possible do a real time backup copy of database? Like that Access doing a backup archive every day and cleans old fields same time from tables. I have Loops which adds a barcodes from serial ports to tables all times...
hI I JUST WANTED TO FIND OUT THE MAXIMUM CAPACITY OF THE ACCESS 2003 AND WHAT WOULD YOU ADVISE B/4 IT GET THERE. I HAVE A DATABASE AND I WOULD LIKE IT TO HOLD INFORMATION FOR AT LEAST 1YEAR 6 MONTHS THANKS FOR YOUR RESPONSE IN ADVANCE
I plan to create a multi-user application on access 2000. There would be more than 20 users simultaneously logging in the database. The database would be kept in Server with high config. The users would work on the database (server) from their workstations. Forms/Queries/Reports ... are in the workstation (client). The database may growup to 70 MB every 3 months. The application will run on the database for 3 months which will be replaced with a new one after that period. Is it a good plan to do so on Access 2000. Am worried about the network traffic. Will the users face it as a very slow application ???
I'm new to Access and was wondering what the storage capacity was for a table.
I am presently working with a table that's approx. 70 columns and about 6,000 rows (and growing). Does anyone know at what point I'll be hitting a wall? It's already too big for me to import into Excel (which is another problem).
What I have noticed, I maybe wrong, the record is a available for the "group_header" section closest to the detai section, in my case, "Client_GroupHeader". However, I need information in the record to format goup header information in the "Customer_Groupheader" and "State_Groupheader. Can anyone help me with this.
I have a listbox from where i can make selections and generate a report for the selected values , the problem is the selections are limited to 28 selections, if i try to select even one more after that i get an error:
" Run time error '7769' " " The filter operation was cancelled. The filter would be too long."
Now is there anyway to make this limited selection changed to a higher value or unlimited??? so i can select as much choices as i can , even all of them if i want? Does anyone have an idea , i looked throught the properties of the listbox but no help.
I have a project database where I keep track of the work in progress on monthly bookkeeping for our clients. I have a form where I use a ComboBox to select the client and another ComboBox to select whether a sales tax report must be filed Monthly, Quarterly or Annually. I have 17 CheckBoxes one for every month, quarter and year-end. I want the selection in the sales tax reporting ComboBox to trigger which CheckBoxes are available to check.
That is, if "Monthly" is selected in the ComboBox, then only the 12 monthly CheckBoxes are available to check (the 4 quarterly and 1 year-end CheckBoxes will be disabled). If "Quarterly" is selected in the ComboBox then only the 4 quarterly CheckBoxes are available to check. Of course, "Annual" is the third option. The disabling of CheckBoxes must be on a record by record basis. "Monthly" could be selected for one client (one record) and "Quarterly" for another client (another record).
I want to build a room scheduler. Before I get too far in to my first attempts, does anyone know if there any examples out there I can use to give me ideas?
If not I wonder can anyone advise me as to a difficulty I currently can see with what my attempts so far. I have built a very basic database with fields for each room and time slots through each day. This works ok. The problem I see is finding a way of setting a room booked for say "every Thursday between 14.00 and 15:00". I have considered using a query to do it but cannot see how to formulate it.
I am in the process of creating a Room Bookings database using the limited graphical tools of Access 2003. I intend to break up the day from 0700 to 1900 into 48 X 15 minute chunks (the minimum bookable time) and have these graphically represented on the form as boxes whose colour change depending on whether it is booked and who by. It would be normal to have up to 8 colours available for this as any one room would be unlikely to be booked by more than 8 people in one day. I would envisage that each day would be covered by a separate subform.
Has anyone else tried a graphical representation of a booking form?
It would be interesting to hear others views or experiences...
