Relational Database System

[Benn]

Hi guys, I'm fairly new to databases and am just creating sample ones at the moment and following tutorials from books. I tried to create my own cinema/movie database yesterday and there's something wrong.

I really wanted to work this one out for myself but after trying many different combinations of various relationships I just can't seem to get it working.

I have four tables..

A member table: MemberID
A booking table: MovieID, Screening Number, MemberID
A movie table: MovieID
A screening table: Screening Number


So the booking table is a composite key that links the member tbale (via the memberid), the movie table (via the movieID) and the screening table (via the screening number)

The problem occurs when I try to make a booking form for members to book a movie.

I use the form wizard and take the following fields:

The MemberID from the member table, the Screening Number and MovieID from the booking table. Then I add other relevent information from the Movie and screening table (for example, screening time, date, price etc from the screenig table and movie name, information from the the Movie table)

The MemberID is displayed on the main form with the other information in a sub form.

The problem I am having is when I enter a booking on the form, I get an error telling me the Microsoft Jet Engine cannot find the screening number in the screening table or something like that.

It lets me enter the other information but not with the screening number.

Thanks for any help.

Benn.

 

[stopher]

I have four tables..

A member table: MemberID
A booking table: MovieID, Screening Number, MemberID
A movie table: MovieID
A screening table: Screening Number


So the booking table is a composite key that links the member tbale (via the memberid), the movie table (via the movieID) and the screening table (via the screening number)

I'm not sure what the problem is. However, your model is slightly wrong. Basically you have too many fields in your booking table which causes redundancy. Your booking table only needs to be MemberID and Screening i.e. this person attended this screening. The screening table should be Screening Number and MovieID i.e. This movie was shown at this screening (nothing to do with the bookings!). I assume only one movie is shown at each screening?

For your form, the main form should be based on a query that joins the screening table and the Movie table so that when you enter a screening number, the details of the movie will automatically be displayed.

Then you can add a subform which will be based on a query which joins the booking table and the member table. When you enter records into this subform (the list of people attending the screening), the Screening number will automatically be pulled from the main for so you only need to enter the memberID. Again the member details will come automatically from your query.

If you get stuck I'll do an example.

One other thing, don't put spaces in the names of your fields or your tables as it will cause pain in the long run.
hth
Chris

 

[Benn]

Thanks for the reply Chris.

I should have explained myself better as I realise after you said that, that one screening will only show one movie.

It would be better to explain the type of database I want by using an example of a holiday booking database.

I just knocked up a quick txt file containing the fields and a bit of data. I believe it can be imported, so I'll upload it in case anybody fancies having a crack at it.

Here is what I was thinking though -

I need a Holiday table to store the holiday information (Let me just say for any Americans/non-uk people I am refering to vacations), I need a table to store the members, a booking table contianing the dates people will want to travel and then I believe I will need a table containing a composite key which will link the two.

So I will then be able to create a main form consisting of the members ID, with a sub form containing information for the member to enter in order to book a holiday. This way, the "Booking ID" is a unique number (could be an autonumber) and will contain the HolidayID and the MemberID (I think)

Thanks for any help. I don't know if this is considered a "basic" system or not.. I thought I was doing rather well but I'm really stuck

Attached is file containing fields and is ready for import

 

[stopher]

Take a look at the attached example. Have a look at:
- the tables
- the relationship window
- the query
- the form and subform

Open the main form (frmBooking) and you will see the member details and lower down where you enter the booking detail (in the subform). You only enter the BookingID, HolidayID and BookingDate in the booking section. You will see the holiday details will appear automatically.

In this example the booking ID is a manual entry but you should really change this to an autonumber I think

hth
Chris

 

[Benn]

Thank you Chris.

The database really helped me understand things. When I am normalising data I always seem to overcomplicate things but I can see from your database that in terms of structure/relationships using three like that works fine.

Thank you.

 

[tjred9262]

Hi All,

I am new here, and I have access 2010 I have in my customer's but now I need to add in the cost, there enventory that pertains just to them, and it does not let me. What am I doing wrong here?

Thank you,
tjred9262