Before you can create relationships between tables, you need to have another table! You already created a table manually so this time you will use a template, which Access 2010, 2013, and 2016 call Application Parts.
Application Parts are table templates but some of them actually create matching forms and reports, too. This can be a great help or a waste of time, depending on what you really need. Versions of Access before 2007 had a table wizard with more sample tables.
Rarely will the template be exactly what you want! It can still be helpful. You can always make changes in Design View afterwards.
Exactly where Office stores things depends on the version and possibly on your version of Windows. It can be confusing. Two places to look are where Office is installed and where user data is stored.
Access 2010 stores the templates and application parts in the AppData folder.
Access 2013 and 2016 store the application parts with the installed program.
Access 2010: C:\Users\<username>\AppData\Roaming\Microsoft\Templates\Access
C:\Program Files\Microsoft Office\Templates\1033\Access\
Access 2013, 2016: C:\Program Files\Microsoft Office 15\root\Templates\1033\Access\Part
C:\Users\<username>\AppData\Roaming\Microsoft\Templates
The folder number 1033 in the examples above is for English. Other languages have different numbers.
Unfortunately, the AppData folder is hidden by default. To see it, you must change your Folder Options to show hidden folders.
Folders Options dialog > View tab > Hidden files and folders section > Show hidden files, folder, and drives
Windows 8 and 10 both have a check box on the View ribbon tab in File Explorer, Hidden items. Check it to show; uncheck to hide.
It is easy and usually painless to add or delete records from a table. But, changing the design of a table that already has records can be more of a problem. It depends on what kinds of changes you make. Let's look at some of the complications.
Example 1: Reduce field size safely
In the Star Wars Collectibles table, the field size for the field Condition allows up to 50 characters. The values in
this field come from a Lookup list: Mint, Excellent, Good,
Fair, Poor. The longest value you can chose is the word "Excellent,
which has 9 characters. You could safely change the length of the Condition field to 9. You would not lose any data. If you decided later
to add other values to the list, you might need to increase the field's size.
Example 2: Reduce field size and lose data
The field HowAcquired also allows up to 50
characters. The longest entry for this field that is actually used in the
records is "Bought retail", which has 13
characters. (You must count spaces also.) If you reduce the field size to
9, some entries will lose those last 4 characters. That's a "whoops!" You
really want to avoid throwing away your data!
Example 3:
Available but not used
You must look carefully at what the choices are, not just what you see in the records. For the field HowAcquired, one of the choices is "Bought at auction", which
has 17 characters. No record is using this value yet. Looking at the
records, you would not know that you need at least 17 characters. You
cannot reduce the field size to the 13 needed for the existing data after
all.
Example 4: Predicting the future
The field ItemName allows 50 characters but does
not have a list to choose from. How many characters do you really need??
The longest entry now has 27 characters. But what about the future? You must be a prophet to know how long future entries might be!
Better to leave this kind of field with lots of space.
Sometimes changing a field's type is no problem. Sometimes it is disastrous. The data for that field may be cut short or even completely lost. Some changes are just not allowed at all. Designing your tables right the first time works better!
Things to consider about changing Field Type:
Problem: Not allowed to
change a field's data type
Cause: The field is part of a relationship with another table.
Solution: Delete the relationship first in the Relationships window [Database Tools ribbon tab > Relationships], make your changes, then
recreate the relationship. But be careful that the new data type can be related to the field in the other table.
![]() |
Step-by-Step: Use Application Parts |
![]() |
What you will learn: | to create a table using Application Parts to view Caption property to add records with a form to attach a photo to create and use a Lookup field to delete a field to correct errors in forms and reports created by deleting a field to view subdatasheet in related table |
Start with: mytrips-Lastname-Firstname.accdb from a previous lesson
The Trips table has a Memo/Long Text field TravelAgent for information about the travel agent. You must enter this data for every trip, even if you used that travel agent previously. It would be better to have that information in its own table instead. If you use the same travel agent again, all the information will be there, waiting for you. If you need to update an email address or phone number, you can change it in one place instead of having to find all of the trip records that show that agent and edit each one.
Access has a template that will create a table with fields for standard contact information, like a mailing address, phone numbers, email address, as well as the parts of the agent's name. This template is different from the one in Access 2007 in that it creates more than just a table.
Click the button Application Parts to open the list of templates.
The top section shows different layouts for blank forms.
A message box tells you to wait while the template is prepared for use. Be patient. It will take a while. This template is going to create more than just a table!
The Create Relationship dialog opens. This dialog offers to create a relationship between the Trips table and the new Contacts table. You are NOT offered the chance to name the table yourself.
The default is to show the field name as the column heading for a table in Datasheet View. But you can change this behavior by setting the Caption property for the field. The template you just used did that automatically for most fields.
Click on the field FirstName and look at the value for the Caption property (at the bottom of the window).
It's the Caption value that shows in the Datasheet View as a column heading
When the Caption property is blank, the actual field name shows. Another small mystery explained!
Enter the following record. Many fields will be blank for
now.
(Remember that these are not real people, the companies are not a real
companies, and the email addresses are not real either!)
You can use the TAB key to move from field to field or just click on the field you want to edit.
FirstName | Jorge |
---|---|
LastName | Martinez |
Country/Region | USA |
CompanyName | World Travel Inc. |
Title | Agent |
WorkPhone | 615-555-1234 |
EmailName | jmartinez@worldtravelinc.net |
To attach a photo: The default image at the left in the form can be replaced with one or more files. It is an Attachment field. A photo of this person would be the obvious first choice. But you could also attach files like the job offer letter, contract, or other documents.
Navigate to the resource files that are part of your local copy of these lessons or where you downloaded those resources, and find the file martinez.jpg.
Be careful to select the correct version of this file. The resource files contain a much larger BMP version for lessons that use older versions of Access. You want the JPG version.
Now you can add more records to practice your navigating and typing skills.
Copy
previous value: You can copy the value "World Travel Inc." into later
records by using the key comb CTRL + ' (the apostrophe) when your
cursor is in the field CompanyName. This key
combo copies the value of the field in the previous record into the current record. Of course, once you enter a record that has a different name in the Company field, this tip will not be helpful!
FirstName | Hector |
---|---|
LastName | Chavez |
Country/Region | Argentina |
CompanyName | World Travel Inc. |
Title | Agent |
WorkPhone | 555-9876 |
EmailName | hchavez@worldtravelinc.net |
Photo | chavez.jpg |
FirstName | Sheila |
---|---|
LastName | Landers |
Country/Region | Australia |
CompanyName | World Travel Inc. |
Title | Branch Manager |
WorkPhone | 5554-8890 |
EmailName | slanders@worldtravelinc.net |
Photo | landers.jpg |
FirstName | William |
---|---|
LastName | Gardner |
Country/Region | Argentina |
CompanyName | World Travel Inc. |
Title | Agent |
EmailName | bgardner@worldtravelinc.net |
WorkPhone | 555-9876 |
Notes | Nickname = Bill |
Photo | gardner.jpg |
Evaluate: Which is easier for this table - using a form or entering data directly in the datasheet?
Did you make many mistakes in typing in the data?
Was the data given to you in the best order? Not really! That was done to show you how useful it is for the order of fields on the form to match the order that you receive it.
Sometimes you need to change the order of the controls on a form to match the order that the data comes to you. Sometimes you can adjust the data first so that the order you see it in matches the order on the form.
The template you just used created a relationship between the tables Trips and Contacts and then added a Lookup field to Trips for the travel agent.
The advantage to using a Lookup field is that when entering a new trip, you can now pick a travel agent from the list.
The awkward part is that you must first add a new person to the Contacts list before you can pick them in the Trips table.
Now that the Contacts table has some records, let's see how the Lookup list the template created might be helpful and how it might present problems.
You already had a Memo/Long Text data type field with a very similar name, TravelAgent, with no space in the name. You will remove one of these fields shortly to avoid confusion.
Can you figure out what the difference is between the fields? Since Travel Agent is blank, there are no clues here in Datasheet View. Let's look at the Design View.
That's a major difference!
The records you entered earlier used the field to hold the travel agent's name and contact info. All of that information (and more) is in the Contacts table now.
Click the down arrow to open the Lookup list for this cell.
All of the agents you entered in the Contacts table are showing.
No more guessing about spelling! Once the data is in the Contacts table, you can just pick from a list for a record in the Trips table.
Point of Confusion: Field Data Type does not match Lookup list entry
The new Travel Agent field has Number data type. But the drop list shows a name, not a number. How can that work?
Explanation: The template created a simple Lookup list that shows only one value but stores the related primary key value, ID. Surprise! Nothing in the interface tells you that what you see is not what is stored!
In fact, a Lookup list can display several fields, for example Lastname, Firstname, and Middlename, to help you choose the right entry, but the table can only store one value. The value that is stored does not have to show in the drop list at all! That's helpful, but more than a little confusing!
Warning: Lookup Fields on a Table
Many database designers avoid using Lookup fields on tables. It is easy to forget that what you see in the table (like a name) may not be what is stored in that field (like an AutoNumber). Joining tables together using fields of different data types makes a mess when you try to create subforms and subreports. It can be hard to figure out where the problem is!
Let's look at what the template created for the Lookup list for Travel Agent.
In the bottom pane of the window, click on the tab Lookup.
Many properties are filled in now.
Display Control shows how the field will display, as a Combo Box, List Box, or Text Box. A combo box can show several fields to help you pick.
Row Source is a query that is written out in SQL. The results of this query form the Lookup list. The next properties control what columns in the query results will show in the Lookup list and what value will be stored.
Bound Column tells which column from the Row Source query will be saved in the Trips table. It is usually the first column, but does not have to be.
Column Count tells how many columns there are in the drop list.
Column Widths sets the width for each column in the drop list. A value of 0" here means that the list will use the width of the column itself. If the query has several columns, you usually want to set each width separately, like 0",0.5", 0.33",1".
List Rows sets the number of rows to show before showing a scroll bar.
List Width sets the overall width of the list. This needs to be equal to or larger than the total of the column widths.
Later we will look closely at the properties shown here.
Set the Travel Agent for the Ireland trip to Martinez.
Set the Travel Agent for the Italy trip to Chavez.
As you edit a record, a pencil icon appears beside the record. The record is dirty, meaning that you have made a change that has not yet been saved.
Point of Confusion: When is a change to a record saved?
Changes to records are not saved automatically until you move on to a different record. Just changing to a different field in the same record does not save the change.
To save changes without leaving the record, use the Save button on the Quick Access Toolbar or the Save Record button
on the Home ribbon tab.
Now that you have selected travel agents in the new column Travel Agent, do you need the original field TravelAgent (no space)?
You lose the comments about the trips arranged through a company hosting the convention. Only the name and company and email address are in the Contacts table. Not much of a loss! You could even add an entry in the Contacts table, like No Agent, to cover the trips without a travel agent.
So removing the old column has some important consequences. It's better to get the fields right at the beginning!
Because the TravelAgent field was deleted, one of the textboxes in your Trips AutoForm now shows an error, #Name?. This code means that Access is looking for a named object that it cannot find. Either the object has been deleted or renamed or the name is mis-spelled. In this case, you deleted it!
Next you will fix the forms to use the new field. The same method can be used for reports also. For this lesson the goal is to show you how awkward it can be to change tables after other objects have been created based on those tables. Plan well first!
In later lessons you will learn how to insert controls yourself and how to use a Control Wizard to set up the control.
Hover over the Error Options button.
A screen tip tells you that the source for the control does not exist.
In many cases you could just edit the Source property for the box to the correct field, Travel Agent. But Access stored an ID number in that field, not a name. On the form we want to see the agent's name.
There is a simple work around. You will create a new form to get the text box you need and then copy and paste it to the existing form. This lets Access do the heavy lifting for creating the combo box for the form.
To correct a report, you can use a similar method. Use the Report Wizard to create a temporary report, which will automatically include the new field. Then copy and paste the new control to other reports. Do not use the AutoReport for this. It does not include a label for the field.
Check for formatting issues: Be careful to check what pasting a control changes in the way a form or report prints. A wide control can automatically widen the form or report so that it won't fit on one page any more. A tall control might change what fits vertically on a page.
Open Trips Report Wizard by double-clicking it in the Navigation pane.
A Parament dialog opens. What is that?
Access cannot find the field that you deleted. It is asking you to provide a value.
Similarly, repair your other reports that were based on the report wizard.
Make sure your report does not get too wide to fit on a page!
Do not bother to fix your other autoreports. Those are easy to recreate!
You now have two tables in a One-to-Many relationship. One record in Contacts is connected to Many records in Trips. The table on the One side (Contacts) will show a subdatasheet of the related records from the Many side (Trips). A relationship must be in place for subdatasheets to be available.
Open the Contacts table again.
A tiny + in a box, , shows at the left of each record because this table is on the One side of a One-to-Many relationship.
Click on the beside the record for Martinez.
A subdatasheet appears below the record and shows the records of the two trips that Martinez handled.
The subdatasheet does not show the column Travel Agent because that is the field on which the tables are joined. Remember that this Lookup field in the Trips table actually stores the ID value, even though you see the LastName value.
Unfortunately, the subdatasheet does not show the name of the table that the record comes from. If you know your tables well, you will recognize the TripID field as the primary key for the Trips table.
Expand the subdatasheet for each of the other agents.
Martinez and two records and Chavez has one record in the subdatasheet.
The records for Landers and Gardner show only a blank record in the subdatasheet. There are no trips in the Trips table yet for which they were the travel agents.
Having several subdatasheets showing can make a table very hard to read. It is not normally a good plan to work directly with the table. Forms are much easier to use for data entry and review.