I have a college assignment that I need to get done before the end of the week. Unfortunately I am not allowed to use VB and SQL Server or PHP - its got to be fucking MS Access, which does my head right in because its a piece of shit. Also, databases not exactly my strong suit

On the face of it, its fairly simple. Task is as follows:

1: Draw a class diagram for a hotel system using the classes Room, Guest, Reservation, identifying datafields and methods (done this bit)

2: Map the class diagram from task1 to a relational set of tables using MS Access, considering Primary and Foreign key fields (done as well, i think)

3: Make up and insert, using forms 20 records for each table to test the database (done this bit)

4: Develop Queries, Reports and Forms for the following requirements (this is where I am getting problems).

a: Initial booking process, in which the customer gives their details and recieve a reservation number

b: The check in process - customer arrives at hotel, gives the receptionist the reservation number. The receptionist checks details, discusses type of room required and issues a key. All details are recorded in the database

c: Create a report for all customers that stayed in the hotel on 25th January last year for marketting purposes

d: Reports also required for guests that are currently in the hotel, and guests that have booked but not checked in.


My problem is that they never actually taught us relational databases yet (next term), and they only showed us the basics in MS Access. I have done similar things in VB and PHP in the past, but Access seems to fuck me about at every turn - I have already spent 4 complete days on this shit, and am getting nowhere.

So, things I am having grief with:

Getting Access to allow me to use information from more than 1 table on a form - used the wizard, won't let me update anything afterwards. What I want is a form taking details from all 3 tables, and update tick boxes that show if a room is reserved or occupied (got two true/false fields on the room table)


Using date features on Access. My reservation table has a start date and end date field from the booking, but I have no idea how to get access to check within a range of dates - so if someone registered on 21st jan, and left on 28th, I don't know how to make it realise they were present on 25th.

Any help greatly appreciated, cos if I can't get this sorted, I am going to fail the course (or rather, shall bin the assignment, failing the year and dropping out of the HNC).

Mate, if I knew any of that there lingo you know I'd help, sounds as tho it's pickling your head a bit there chap

Hope someone can help

cheers Mickle

unfortunately, Access only allows you to use 1 argument after the where clause or it gives a "sql statement too complex" error.

piece of fucking garbage it is.

If I had paid money for it, I would be most upset

I've made an example SQL query to test BETWEEN

This is using the northwind orders table,

SELECT *
FROM Orders
WHERE (((Orders.ShippedDate) Between #7/16/1996# And #7/16/1996#));

It pulls off all the orders for the 16/7/1996 (sorry its american in the query...)

cheers mickle. Just got to get the damn forms working properly now.

It seems like I may have to do something with a feature called subforms.

I hate this program. I ended up learning VB because it was 10x easier to use access to create a table structure and then access it from VB using ADO controls than try and use Access. Its got to be one of the most unfriendly applications I have ever had the misfortune of attempting to use

I don't agree with the comments on MS Access. I do MS Access Develpment for a living.

Firstly have yo setout your tables containing the different information and related them. 1:1 and/or 1:Many.

its ok mate. with some help from peeps on this (and other) boards, I managed to get the sodding thing working.

Half my probs were coming from Access getting upset if you built forms from more than 2 tables. Once I redid the structure, and simplified the layout, I got it working, and got it sent in this morning.

wooooo! Now I never have to use Access again for anything

You should get used to it asas today small business r taking it up bigtime and it is a well paid occupation -MS Access Database Developer.

There is a between date function & if you simplified it you would only use queries to find the days rented the work the cost out from that per day/week/month.

Good to hear you got it sorted.

One of the problems with access is it mixes the database with the logic and presentation, this unfortunately is the same thing that makes it attractive to small businesses. I can see the attraction to a degree but I do not personally believe its a good way to work.

I started using mysql where ever possible (in some cases used MS sql server) and moved some existing applications from access to mysql and was never dissapointed, it improved existing performance and allowed better flexibility in clients accessing it.