Urgent Help needed – I’m a novice

[PS65]

Hi everyone,

Basicly I am doing an Access Project for my school coursework, but I am really confused and my teacher isn't that great.

My coursework is based on a video rental company.
The database (in breif) has to be able to bring up a customers details, and add a loan to/under that customers details. (as you would expect from a video shop).

Now, to be honest, I dont know if I have the correct items in each table, but was wondering if someone would be so kind to have a quick look to tell me what to change and where to put the relationships for the database to work correctly.

I have taken a screen shot of my current database:


If you would like any other information, please let me know.
I am really grateful for this!

PS65

 

[jeremie_ingram]

First off, you have some major naming convention problems. You should not have spaces, reserved words, or any character but letters and numbers (best practice).

The tblLoanDetails will work as a junction and history table between the other two tables. Make sure that the CustomerID and FilmID in the tblLaonDetails are spelled the same as the primary keys for the other tables. Build your relationships between the Primary keys for Customer and Film to the foreign key in tblLoanDetails.

I hope this helps, let me know if you need more assistance.

 

[ScottGem]

You made a good start. However, I agree with jeremie about the spaces and symbols in field names. I would also break out the File data, Since a film can have multiple actors and producers, and might also cover multiple genres, you should have junction tables for those. You also need an Artists table to use to identify producers and actors. And a genre lookup table.

Finally, I would have a ReturnedDate field rather then a Returned flag.

 

[PS65]

Hi Guys,

Thanks for the advice, I will work on that tonight and get you a screen shot again.

In terms on User, Film and Loan ID's, these should all be AutoNumbers right?

 

[ScottGem]

There are some differing schools of thought on that. My opinion is that you should almost always use autonumbers for primary keys.

 

[PS65]

To make life a bit easy.....

 

[jeremie_ingram]

When you look at these three tables, you should think of them as separate databases. I agree that if you are looking to develop something that is going to incorporate massive film data (artists, producers) you should have separate tables for each. Otherwise, combine all of that info into one memo field to reduce the amt of fields you need to deal with. Call it FDetails and set it to memo.

The naming not only needs adjusting by eliminating spaces and reserved words, but keep them short and simple. If you were administrating a system, you would much rather have to deal with "HPhNum" rather than "HomePhoneNumber". It will save on typing.

Also, your foreign keys are set to text when they need to be set to number. You relationship will not work properly if they are no the same, and since you are linking to the P Key for the table (auto number) then the other end of the relationship needs to be set to number.

Let me know how it comes along.

 

[PS65]

Hi Jeremie,

Thanks for the advice, that did make it clearer!!

I will try and have a go on Friday as i'm away for a few days now.

I will get back to you ASAP.

Thanks

 

[PS65]

So Far.....

Hi Guys, So far this is what it looks like:




Now, few more questions

Basicly, at the moment, when you add a loan to a customer, it says he is hiring at 'FilmID #'

When a film is on loan to a customer is says 'Film is on loan to 'CustomerID #'

And in the loans table is says 'CustomerID is hiring FilmID'

Does anyone know how I can turn that into the actual names.

E.g. "John Smith is hiring out Layer Cake"
instead of " 1 is hiring out 2"

Could this be done through the form or even a querie, or does it have to be done through the tables/relationships?

Also, has anyone got any ideas on how todo the validation technique to stop a certain film being rented out to a certain age group.
E.G. A person of the age of 12 can not rent a film that is for people over the age of 12. 12 < 15 < 18 (if that makes sence).

 

[ScottGem]

There are many ways to do this depending on how you set things up. If you have a form using comboboxes to select CustomerID and filemID, you can use the Column property to add the names and then use them in your messages.

 

[PS65]

There are many ways to do this depending on how you set things up. If you have a form using comboboxes to select CustomerID and filemID, you can use the Column property to add the names and then use them in your messages.

Hi Scott,

So does this mean...for now my tables/relationships are correct?

Would you like me to upload the database?

Thanks

 

[RV]

So does this mean...for now my tables/relationships are correct?

A customer can loan multiple videos at the same time.
That's not reflected in your table structure.

Would you like me to upload the database?

We're not going to build your database for you...
It's your project so you'll have to build your database structure yourself.
I suggest you start with what's called normalization.
Basically that comes down to putting your daily operations in the shop on paper, identifying your occurances uniquely and translating your entities into tables.

Search in the forum or on the Internet for Codd or for normalization.
Surely your teacher and / or your fellow students can help.

RV

 

[PS65]

A customer can loan multiple videos at the same time.
That's not reflected in your table structure.RV

I thought that is working, as a customer has more than 1 loan here:

We're not going to build your database for you...
It's your project so you'll have to build your database structure yourself.
RV

I didn't ask you to, I was trying to make it easy for someone to visualise, as looking at a few tables with relationships probably takes longer to understand.

 

[RV]

I thought that is working, as a customer has more than 1 loan here

Not so.
As a customer, I could pop in and hire multiple videos on one and the same loan.

I didn't ask you to, I was trying to make it easy for someone to visualise, as looking at a few tables with relationships probably takes longer to understand.

In fact that does come down to you asking us to build your applic.
No offence but the goal of this forum is not supplying anyone with a full course on main principles.
You really should get familiar with normalization and the principles of relational databases first.

RV

 

[ScottGem]

I think you need to go back to your assignment to judge whether what you have done meets the requirements of the assignment. In a real life situation, RV is correct that you would have a Loan table with a Loan Detail table. This would allow for renting mutliple videos with one Loan transaction.

However, given this is a schoolwork assignment, a more simple arrangement where each video is a loan might satisfy the assignment.

 

[PS65]

I think you need to go back to your assignment to judge whether what you have done meets the requirements of the assignment. In a real life situation, RV is correct that you would have a Loan table with a Loan Detail table. This would allow for renting mutliple videos with one Loan transaction.

However, given this is a schoolwork assignment, a more simple arrangement where each video is a loan might satisfy the assignment.

Hi Scott, you are quite right, the assignment is simpler.

RV, I will look into normalisation, thanks for that.
Also....my teacher and other student have no idea.....which is rather annoying, and most books seem to be to advance for me.

Can anyone recommend any good access books, which will give me enough information to make a successful database?
I understand very basic access, however other parts such as:
Data dictionary, normalisation, calculated fields, multiple table queries, queries using relations links and parameter queries. None of which I understand.

Please don’t think I am asking you to-do my assignment, because I need to understand every think, as I am not marked on that database, but the actual write up.

If anyone could recommend any really good books for beginners that would be great!

Also, I’m on a tight schedule (mid November completion date) so any books that get straight to the point are also good!



Thanks everyone!!

 

[jeremie_ingram]

Sorry to have been out of the loop, I have been swamped lately. As for books, I have founs nothign to surpass New perspectives. They take you form left click right click (explaining a mouse) all the way to full DB develppment.

http://www.course.com/catalog/title...ft Access 2003&series=New Perspectives&sort=4

They can be founs at course.com

 

[PS65]

Sorry to have been out of the loop, I have been swamped lately. As for books, I have founs nothign to surpass New perspectives. They take you form left click right click (explaining a mouse) all the way to full DB develppment.

http://www.course.com/catalog/title...ft Access 2003&series=New Perspectives&sort=4

They can be founs at course.com

Hey Jeremie, thanks......Books on order. But waiting for delivery times!

Database seems to be coming on a bit better now.
However, the 'copy' table isnt working.

I made a table called 'tblFilmCopy'. It should have how many copy's are availble to rent..
And also, when a film is rented, the number should go down by the correct interval.



At the moment, this table 'FilmIDCopy' is doing nothing. The relationship is also doing nothing.

 

[jeremie_ingram]

Looks to me as if you set the FilmID in the copy table as the primary, cant do that. You should have 3 fields in the table copy 1 A PKey, 2 The Foriegn key, 3 the actual field to contain the # of copies (number format). Link it to the Film table via the tblFilm pkey and the Forign key in the copy table.

That clear???? Hope it helps.