New Access tutorials from Microsoft

[HansV]

Microsoft has published a series of video tutorials about Microsoft Access. See What is Access for the first one. You'll find links to the other installments on the left hand side.

[Rudi]

Thank you.
That is a very handy link to know about.

[BittenApple]

Hans,
Thanks a lot for this link. I am on vacation and I couldn't thank you earlier than this. Today, I thought it is a good idea to go back to my posts and databases to educate myself and amuse myself a little bit.
Regards,
BittenApple

[BittenApple]

Hello Hans,
Thank you for the training. There is a point that I would like to bring it up.
In the training, the trainer says that join the tables by unique key rather than duplicate keys. That can be followed when we create relational database system; but not in all cases.

It happens times and times that I need to join two lists in which the unique values are duplicated. It is a business, I can not take the duplicate keys off and change them to unique values and then join two tables, if I do so, I have ignored some of the information.

My method: I join two tables by duplicated values to find the matching records. Is this system correct?

Regards,
BittenApple

[HansV]

If a field contains duplicate values, it is not suitable to be used on the "one" side of a one-to-many relationship. The table on the "one" side should always have a primary key. A primary key is by definition unique; Access won't allow you to create a primary key with duplicate values.

If your table doesn't have a field with unique values, you have two options:
Either create an AutoNumber field in the table, and use this field for the primary key. An AutoNumber field is guaranteed to contain unique values.
Or find two (or more) fields whose combinations are unique. Each of the fields by itself may contain duplicate values, but there shouldn't be more than one record with the same combination of values. Set the primary key to the combination of these fields: a so-called composite primary key.

A composite primary key is often more complicated to work with than an AutoNumber primary key. For example, instead of one join line between tables, you'll have to create multiple join lines. It's easy to make a mistake and create a mismatch between the properties of the join lines. For that reason, many professional database designers prefer to ALWAYS use an AutoNumber field as primary key.

But in some situations, a composite primary key is more natural because the key values have a meaning in the database. An AutoNumber is always a meaningless sequence number.

[BittenApple]

Hello HansV,
If I create a primary key (composite primary key) or auto number, on what basis make that primary key to be used on second table as foreign key?

What I did for my project (as I always do that), I create multiple join lines. This method has worked for me and it makes sense (I learned this technique from you and I succeeded in my projects). In the past, I used VLookUp and that was such a hassle and incompetent way.

Regards,
BittenApple

[HansV]

If the primary key in the table on the "one" side is an AutoNumber field, the corresponding field in the table on the "many" side should be a Number field, with field size set to Long Integer. The join line will be between the AutoNumber field and the Number (Long Integer) field.

If the primary key is a composite key, for example on a Date/Time field and a Number (Long Integer) field, the corresponding fields in the other table should be of the same type - in this example also a Date/Time field and a Number (Long Integer) field. There will be a join line between the Date/Time fields and a join line between the Number fields.

[BittenApple]

Hans,
A composite primary key is often more complicated to work with than an AutoNumber primary key. For example, instead of one join line between tables, you'll have to create multiple join lines.

-if composite primary key is the same as multiple joins (If They are same things), I have done so and I think it is more convenient than AutoNumber. We can always look inside of the joins to make sure correct fields are joined in together

The objection to AutoNumber is that when we generate AutoNumber in one side of the relation, how can I use these primary keys as foreign keys in the many side of the relation? If AutoNumber 1 relates to Member_ID 1 and AutoNumber 2 relates to Member_ID 1 as well in one side of the relation, and on the many side of relation, we have Member_ID 1 repeated 3 times, how can assign AutoNumber 1 to this memberID on the many side which is repeated 3 times?

I am on the side of creating multiple join lines for situations where we receive tables from outside; while, if we create a database then we should and we are mandated to create primary key. This is what I think.

Regards,
BittenApple

[HansV]

Data entry for One-to-Many and Many-to-Many relationships is usually done through a form with a subform. The subform will be linked to the main form by way of the foreign key vs the primary key. In new records in the subform, Access will automatically set the value of the foreign key to the value of the primary key of the main form. So if the primary key of the main form is an AutoNumber field, you don't have to worry about anything: the AutoNumber field is given a value automatically, and the foreign key too.

[BittenApple]

Hello Hans,
How about when I have received two tables/ lists and I need to find the matches between them?
I can't match on one field only, I have to match on multiple fields to create unique values?

Is Multiple joins considered same composite primary key?

Regards,
BittenApple

[HansV]

A join is not the same as a key.

But it's fine to use multiple joins in the situation that you describe.

[BittenApple]

Hello Hans,
I had to get back to this post:
by Hans: "If a field contains duplicate values, it is not suitable to be used on the "one" side of a one-to-many relationship. The table on the "one" side should always have a primary key. A primary key is by definition unique; Access won't allow you to create a primary key with duplicate values.

If your table doesn't have a field with unique values, you have two options:
Either create an AutoNumber field in the table, and use this field for the primary key. An AutoNumber field is guaranteed to contain unique values.
Or find two (or more) fields whose combinations are unique. Each of the fields by itself may contain duplicate values, but there shouldn't be more than one record with the same combination of values. Set the primary key to the combination of these fields: a so-called composite primary key."

If I create AutoNumber PK for the left table, how can I map these autonumber as a foreign key to record on the right hand table? Should I update the second table? If yes, how?

On data entry form, the autonumber on the form for second table is created automatically. I agree with this. The situation is not data entry all the times.

When I receive tables from external world with full of duplications, if I create AutoNumber to guarantee the uniqueness of the record, how should I apply this primary key as a foreign key on the second table (right hand table)?

Regards,
BittenApple,

[HansV]

If necessary, split the table into multiple tables. For example, if you receive a table in which each patient may occur multiple times, create a new table in which each patient occurs only once. In this new table, PatientID (or whatever you name it) can be the primary key. Next, create a table which contains repetition. In this table, don't include the name, gender etc. of the patient, only their PatientID, plus the other data.
Let's take an example: patients with visits.

S1441.png

Personal information (patient name, gender and date of birth) is repeated. You would NOT use this table as is.
In the first place, create a table with unique patients, and assign a primary key PatientID in this table:

S1442.png

Next, create a table with PatientID and visit data. The patient's personal information is not included in this table. The patient is identified by PatientID only. We assign a primary key VisitID in this table:

S1443.png

[BittenApple]

Hello Hans,
This is a very great response....! This is like a discovery for me.
I have 8 tables, should I split all the tables as you instructed?
Regards,
BittenApple

[HansV]

If a table contains repeated data (such as the patient name, gender and date of birth in the example above), then it makes sense to split it. One of the goals of relational database design is to avoid storing the same data more than once.

[BittenApple]

Hello Hans,

My approach to splitting table is to select query and Distinct and make table query. Could this be right? Is there any other way to do so? Should I use table Analyzer? I don't want to end up with lots of queries. How about table Analyzer?

I have 8 tables, should I normalize/ split all of them?

I split a table: the first table has MemberID, MemberName, MemberDOB. If I user MemberID in the first table as a primary key, then this primary key would be foreign key in right table.
This is fine. How about when I use autonumber in first table as PK? How should I populate the right hand table with AutoNumber PK from the left hand side table? Should I join them thorough MemberID and enforce the referential integrity to get AutoNumber PK from first table?


I have 12 visit types, I think I should create AutoNumber As PK for right hand side table. Could this be right?

Regards,
BittenApple

[HansV]

If you have, or create, a table in which MemberID is unique, you can use MemberID as Primary Key in that table, and you can use MemberID as foreign key in other tables that link to that first table.
If you have a table with MemberID, MemberName, MemberDOB, but with duplicates on MemberID, you should first use a make-table query that creates a table in which MemberID is unique.
You'll have to analyze each of the tables to see if they should be split or not. It depends on the data in the table. As I mentioned, the goal is to avoid storing the same data in multiple records.

[BittenApple]

Hello Hans,
I came to use table analyzer even for a one time, but no success; it takes for ever to finish normalizing and several times I had to close the database and it gives errors such as: "File Sharing lock count exceeded. Increase MaxLockPerFile Registry Entry. What is wrong? shouldn't it do the work as it is a tool embedded in MS Access?

I am using select query and make-table. So far I have split only one table since morning.

Thanks,
BittenApple

[HansV]

Here are some articles about how to design the table structure of a database:

For the basics:
Database design basics

For more details:
Microsoft Access Table Design

[BittenApple]

Hans,
thanks,
BittenApple