Any good tutorials

[data808]

I'm looking to creating my first database and had a few questions:

1. Is a form where a person fills in information then when finished, they click a submit button or something to save it to the database?

2. If the person wants to look up a record would that be a query? And if it is a query, after they locate the record they want, will it show up in the view of the form? Or will it be in a database kind of view?

3. I understand the primary key is a field that cannot have duplicates. If duplicate data is entered into a form that is a primary key field, what will happen? Also can you have more than 1 primary key field?

That's it for now. I need more info to ask more questions.



Thanks.

[alansidman]

Start with this one:

http://www.accessmvp.com/strive4peace/

and be sure to read this first

http://www.studytonight.com/dbms/dat...malization.php

[Dal Jeanis]

There are two more good starting places in post #5 of this thread https://www.accessforums.net/access/...ess-38592.html

[ItsMe]

1. Is a form where a person fills in information then when finished, they click a submit button or something to save it to the database?

Yes. Information is usually "saved" as the user closes a form or moves to another record/row within a form. It is not necessary to execute special code to save changes or additions that a user inputs via a form.

2. If the person wants to look up a record would that be a query? And if it is a query, after they locate the record they want, will it show up in the view of the form? Or will it be in a database kind of view?

Just because you call a tissue a Kleenex does not make it so. Access provides a way to create a query via a GUI. This interface allows the developer to assemble tables and fields in a way to create joins and assemble fields. The result is something that can be saved as an object. This object can then be referred to as a query and referenced or called upon by using the name you assign it. These "query" objects use Sequential Query Language or SQL to query the necessary tables and fields.

Forms that you create in Access can do a similar thing. A form can be bound to a table to create a recordset. You create controls within the form that are "bound" to the table, in turn, creating a recordset. The form can then "query" the necessary data via the controls as the user navigates the database via the form, providing a recordset the User can view, edit, and add records to. Forms can be bound to table objects or query objects or even not be bound at all.

3. I understand the primary key is a field that cannot have duplicates. If duplicate data is entered into a form that is a primary key field, what will happen? Also can you have more than 1 primary key field?

If you enter a duplicate primary key into a field that is designated a primary key you will get an error. You can designate as many columns/fields as a primary key as your heart desires. There are special reasons why you would need more than one primary key. It will probably suffice to have only one primary key in any given table. When starting out, it would be advisable to have one PK and several Foreign Keys (FK) in one table. The FK's will represent PK fields in other relative tables.

You want to "Index" your FK's and allow for duplicates, while PK's will never have duplicates. Having FK's in a table is what allows a query to do its magic.

[Dal Jeanis]

Slight clarification -

Historically, in database lingo, the "primary" key is the one that uniquely identifies a record (also called a "row") in the table. A record can have many keys, and even many unique keys, but usually only one of them is referred to as the "primary" key. According to this page - http://en.wikipedia.org/wiki/Unique_key "Each table can have at most one primary key."

The primary key in the first table will be used as the "foreign key" in any other table when the other table needs to refer to one specific record in the first table.

.
Example: Suppose we are a shipping company that delivers products throughout the Southwest US.

Our company table has a primary key called CompID, and CompID number 666 uniquely refers to Acme Corporation.

Our Customer table has a primary key called CustID that uniquely identifies Acme's best customer, Wile E Coyote, as CustID number 54321.

On our Shipment table, on each record where we are delivering Wile E Coyote another ill-fated device from Acme, we use CustID 54321 and CompID 666 as foreign keys to identify the customer and company.

.

Now, I've hedged a bit here, because in the wide world, the "primary key" isn't necessarily one field - it could be a combination of three different fields. For example, social security number isn't guaranteed to be unique - the US government reuses them, and occasionally has issued two living people the same SS#, so you might need SS#, first four digits of last name, and birthdate to ensure that you've uniquely identified a historical person from the twentieth or twenty-first century.

We don't usually use those multifield *primary* keys in Access. In Access, common practice is to have the primary key be an autonumber field where Access is responsible for assigning a number to each record, a number which is guaranteed to be unique but not guaranteed to be sequential.

This type of primary key is technically called a "surrogate key", because it's a replacement (a surrogate) for some other combination of fields that is considered too unwieldy (or too sensitive) to use in practice. In Access, it's usually just called an autokey or autonumber key field.

GLOSSARY OF KEY TERMS:

"primary" - unique, indexed, and used in different tables as foreign key to this one. Cannot be Null.

"unique" - there can only be one record with each key value. Nulls could be allowed.

"indexed" - the database is responsible for being able to find efficiently records by the value of this field, so it keeps an "index" to records by the value in the key.

"foreign" - the primary key for a different table.

"natural" - consists of data that exist in the real world.

"candidate" - any one of a number of fields (or combinations of fields) that exist in the real world data, that could potentially be selected for the primary key.

"surrogate" - a replacement key, unique, not a natural part of the data

[data808]

Thanks for the detailed reply.

For question one. If the clerk entering new data into a form and makes a mistake but already hit the next button so the record was saved into the table, to go back to delete this table would they have to exit the form and go into the table to delete? Or would they be able to delete the record from the form view?

For question two. It sounds like you can use a form to enter data into the table but also use the form to do queries by bounding it to a table. So would I be able to create two different forms? One I could call Data Entry Form and the other I could call Search Form? So that the clerk would know which form to use for whatever task they are looking to do.

For question Three. Ok so I am going to make a database with people's social security numbers and driver's license numbers. Also there full name, maybe address, and some other bits of information on each person. Would it be a good idea to make the primary key the social security number and also the driver's license number since these two numbers are unique and I would never want to create a duplicate of those numbers in the system? I assume the foreign keys would be things like their name and address since people have the same names and live at the same addresses?

Thanks for all the help.

[ItsMe]

For question one. If the clerk entering new data into a form and makes a mistake but already hit the next button so the record was saved into the table, to go back to delete this table would they have to exit the form and go into the table to delete? Or would they be able to delete the record from the form view?

Because the form allowed them to edit, it should be as simple as going back to said record and editing the mistake. You can program a form to allow edits, add records, delete records, or any combination thereof.

For question two. It sounds like you can use a form to enter data into the table but also use the form to do queries by bounding it to a table. So would I be able to create two different forms? One I could call Data Entry Form and the other I could call Search Form? So that the clerk would know which form to use for whatever task they are looking to do.

If a form allows a user to enter data, it is because it is bound to a table or query (of course there are ways around this.) The easiest way to enter NEW records into a table is to bind a form to a table or query and designate it as "Data Entry" in the form's properties. When the form is first opened, it will only display one new record. If the user types info into the fields, they can navigate to the next record to save the new record and create a subsequent new record. The user will only see the new records they created in this "session" as they use the form for data entry and navigate records within the form.

You can create a second form by copying the first. by changing the properties of the new form, you can control the user's ability to edit, add, etc. This second form can be used to only "view" records if the properties are adjusted correctly.

For question Three. Ok so I am going to make a database with people's social security numbers and driver's license numbers. Also there full name, maybe address, and some other bits of information on each person. Would it be a good idea to make the primary key the social security number and also the driver's license number since these two numbers are unique and I would never want to create a duplicate of those numbers in the system? I assume the foreign keys would be things like their name and address since people have the same names and live at the same addresses?

Many databases will have a contacts table. Although your tblContacts will have columns that contain fields designated for data such as SS#'s, you do not want to use these fields as a primary key. It is ideal to have a field with an Autonumber data type designated as the table's PK. You want a unique identifier the database can use to locate specific records and locate them quickly. Databases prefer number data types over most other types because of the amount of memory/resources required to index these fields.

Just because a field is not the PK, does not mean you can not index that field. You can go to the properties in design view of your table and index the SS# field. You can specify whether or not you want Access to allow duplicates. There is a difference between the PK and other fields. Just because you need to index a field our make sure duplicates are not allowed is not reason enough to have it designated as a PK.