Primary Key Question

[data808]

I'm looking to creating a movie database of my collection. What would I need to set the primary key to if I don't want to be adding duplicate titles of movies? I do not want to add the same movie more than once in other words.



Would it be correct to set the primary key to the title of movie field? Or would I have to go as far as to enter the UPC bar code of each movie because that would act more like an identification number to each movie which is never the same?

Any help would be appreciated. Thanks.

[ItsMe]

Most all of your tables will depend on the "AutonNumber" data type. Your PK should be the first field you create in your table. You assign it a name that helps to remember that it is the PK. For example, MoviePK for the PK field in your tblMovies. You don't type or enter anything into this field in form view, query view etc. The Users should not usually have a reason to see it. It acts as a serial number the DB depends upon to create relationships with other tables.

You can definitely add barcodes to your table. You will still, most likely, want an autonumber field to act as your PK.

[data808]

Well yeah that would be a good idea. But I thought about it a little more and even the title would not be a good idea for movies such as Alice and wonderland. They have a 1951 version and 2010 version so I have both and that would be a duplicate if the primary key was set to the title. UPC bar code would take too much time. I don't know what I could set it the primary key to so that I don't enter the movie again by accident. Any ideas?

[ItsMe]

As mentioned, the PK is for the benefit of Access, not necessarily the User.

In order to prevent the User from entering duplicate records/fields you need to verify the data before the input is complete. There are a few ways to go about it. You can place code in the before update event of a field or form that automates the data verification. However, this may miss some nuances in recors may appear as duplicate to you vs. the DB's interpretation.

If your verification code is looking for an exact match of "The Green Mile" it will not consider "Green Mile, The" a match. When automating, you need to plan all contengencies well in advance.

Another approach is to use a search form. You can take some of the automation out or add a search form as another layer, in addition to complex automation. The search form can use wildcards and search for key words. Then the user can determine if the record is a match or not. For example, before allowing for a record to be enetered the user must search for two keywords. In each field they type a word, Green and Mile. The DB will search twice, once for each field and offer a result that the user can review.

[orange]

Here is a tutorial that leads you through steps to identify your tables and relationships. It will also gyide you on Normalization and the selection of keys.It also has solution available. It is an excellent source of database design concepts.

http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[data808]

I think that would be the most logical thing to do. Sometimes its just too hard to automate everything. I guess I'll search for the movie to see if it was entered previously before I enter it as a new record. How do I go about password protecting or making a table, query, or form for viewing only and how do I make those things for editing? I would like to make certain forms, queries, or tables able to update/edit and others I would like to just view/read-only.

[orange]

Experience shows you should get the basics of database design understood before getting into the nitty gritty of Access or any data base software.
I repeat my recommendation given in post #5.
Good luck with your project whatever you decide.

[ItsMe]

I think that would be the most logical thing to do. Sometimes its just too hard to automate everything. I guess I'll search for the movie to see if it was entered previously before I enter it as a new record. How do I go about password protecting or making a table, query, or form for viewing only and how do I make those things for editing? I would like to make certain forms, queries, or tables able to update/edit and others I would like to just view/read-only.

Forms and controls have various properties. Adjusting the properties is what allows or restricts the User's ability to manipulate the data within the DB. You can further restrict the User's access to tables queries and other objects by adjusting how the application opens. More info on how to restrict things like the "Navigation Pane" is available in the other thread you started.
https://www.accessforums.net/queries...tml#post194163