Access crosstab query multiple column headings

1

Week 7 - What We'll Be Working on This Week

In Access there is a lot more to queries than just creating Select queries that apply filters to the data in tables and other queries. It is possible to perform calculations in queries as a means of deriving new data from existing fields, and to summarize data using Aggregate functions. Crosstab queries, which are similar to Pivot Tables in Microsoft Excel, will save you hours of work in terms of summarizing table data, as well. In addition, Access provides a number of Special Purpose queries:

 Parameter queries will enable the user to specify criteria for the query when the query is run

 AutoLookup queries will enable you to enter one field value into the Datasheet View of the

query and have Access automatically look up and enter other values into the current record.

 Action queries will cause changes to be made to an object when the query is run:

o Update, Append, Delete and Make-Table queries will all potentially save you a

tremendous amount of time and make life very, very sweet.

Goals:

 Creating a calculation field in a query page 3

 Concatenating fields in a query page 6

 Summarizing with Aggregate functions page 9

 Adding other functions to the Aggregate Summary page 11

 Adding Captions page 13

 Creating a Crosstab query page 15

 Creating a Parameter query page 20

 Creating a Parameter query for a range of dates page 23

 Creating an AutoLookup query page 26

 Observing the impact of the AutoLookup query on the table page 28

 Documenting the database page 32

 Creating an Update query page 38

 Creating an Append query page 44

 Creating a Delete query page 48

 Creating a Make-Table query page 50

This is where we're headed this week. So let's get to it!

2

Goals for this section:

 Creating a calculation field in a query

 Concatenating fields in a query

 Summarizing with Aggregate functions

 Adding other functions to the Aggregate Summary

 Adding Captions

 Creating a Crosstab query

Calculated Data Type

Before we discuss setting up calculations in a query, I want to discuss the new Calculated data type that first appeared in Access 2010 (which some of you used in your last assignment). With the calculated data type, you can now store a formula or expression that will be updated automatically when the components of the expression change. The disadvantage of defining a field in a table as a Calculated data type is that the calculation can only use fields in that table. The advantage of using a Calculated data type is that it short cuts doing calculations. By putting the calculation in the table, you do not have to repeat the calculation where ever it is needed. All you do is just add the field to the form, report or query and it is done for you. Using a query to perform calculations helps to avoid the 'bulking up' of your table with unnecessary information. A query calculates the information whenever you need it. I strongly recommend that you put your calculations in your queries rather than tables as this allows for greater flexibility in your data design.

Performing Calculations in a Query

If you need to calculate any of the fields in your database tables, don't do it on a calculator and enter the results into the table manually, and don't export the data to Excel in order to get your answers. Instead, create a query, include the Number or Currency fields to be calculated, and create a formula in a blank column of the QBE Design grid. Calculation results may be based on any available fields in any of the objects on which the query is based. It is important to note that the fields that are calculated in a query do not need to be present in the recordset of the query. As long as the fields are present in the record source for the query, Access will recognize the data in those fields. Our objective is this: We'll use the qryWorkOrdersByCompletionDate query to calculate the total cost for each work order. This calculation will involve summing the curMaterialCost and the curLaborCost fields. To create a calculation on the Query Design window, place the cursor in the Field cell of the empty column in which you want to create the calculation. Type the appropriate formula, being sure to enclose all referenced fields in square brackets and spell the field names exactly as they are defined in the table. For example, the formula [curLaborCost]+[curMaterialCost] would sum the Labor Cost and Material Cost values for each record in the recordset. If you would like to work on a larger screen in order to better see what you're typing, you can press Shift F2 to display the Zoom box, and type the formula there. Once the formula has been created in this way, when you run the query Access will assign a temporary name of Expr with a sequential number (Expr1, Expr2, etc.) and a colon to the calculation field. You have the option of changing this label directly in the Field cell of the calculation column of the QBE Design grid. However, when you change the label in the

3

Field cell, be careful not to delete the colon because Access needs it to know which the field label is and where the formula begins.

Hands-On Activity: Creating a Calculation Field in a Query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Display the qryWorkOrdersByCompletionDate query in Design View.

2. In the QBE Design grid, place the cursor in the next available (that is, empty) Field cell (to the right of

the last filled column in the grid).

3. Press Shift F2, to open the Zoom box for the field. When you're typing long entries such as formulas

in a field, you may find it helpful to zoom the window to make it easier to see what you're doing.

4. In the Zoom box, type the following formula:

[curMaterialCost]+[curLaborCost]

Note: Make sure you enclose each of the field names in square brackets, and that you type the field name exactly as it appears in the table on which this query was based. If you don't, the calculation won't work.

4

5. Click OK, to enter the calculation.

6. Widen the column that contains the newly created calculation, to view it fully.

7. Run the query and observe the results: Access has summed the values in the Material Cost and Labor Cost fields, it has formatted the result as currency (to match that of the two calculated fields), and it has assigned a column heading label of Expr1 to the calculation. This column heading may be

changed.

8. Switch to Design View.

9. In the Field cell of the calculation, select Expr1.

10. Type Total Cost as the new label for the calculated field.

Note: When creating a label for the newly created calculation field, there is no need to use the Leszynski Naming Convention, since it is only a field label. It is fine to use spaces. Also, make sure you leave the colon after the field name

5

11. Run the query again, and observe the new column-heading label. Total Cost is now reflected,

instead of Expr1.

12. Choose File / Save As /Save Object As /Save As command.

13. Type qryWorkOrderTotalCosts as the name of the query.

14. Click OK

15. Close the query.

At this point you should have 7 queries created in your database: qryBidInfo qryCACustomers qryCustomersAndWorkOrders qryMissingHelpers qryPostJuly2003WorkOrders qryWorkOrdersByCompletionDate qryWorkOrderTotalCosts

6

Concatenating Fields in a Query

The term concatenate means to join, and it is a procedure that may be used for combining the contents

of multiple fields into a single field. For example, in a table you might have Last Name and First Name

fields, but want to reflect that information as Last Name, First Name in a single field. Rather than typing

the same data again (which as you know is a big "No No"), you can simply create a calculation using the

& character as a concatenation operator. If you want spaces or any punctuation (such as commas) to be

included in the calculation result, they must be enclosed in quotation marks.

This is the reason why it is advisable to break field names down into the smallest possible component

parts, as we indicated in the first lessons of this course. It's very easy to combine multiple fields into a

single field in queries, but it isn't so easy to break fields down (such as the use of composite primary key

fields).

Our objective is to list the Total Cost for all work orders in a query, with the employee name reflected as

Last Name, First Name in a single field. Because we have already calculated the Total Cost in the

qryWorkOrderTotalCosts query, we will add the strFirstName field to that query so it's available to us, and

then build the new query on the basis of qryWorkOrderTotalCosts.

Remember: Queries may be based on tables or on other queries.

Hands-On Activity: Concatenating fields in a query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Display the qryWorkOrderTotalCosts query in Design View.

2. In the tblEmployeeHRData field list, double-click the strFirstName field to add it to the QBE Design

grid.

3. Move the strFirstName field to the immediate right of the strLastName column.

4. Re-save and close the query. We will now create a new query based on this query.

5. On the Create / Queries group, click the Query Design icon to create a new query object.

6. On the Show Table window, activate the Queries tab to see the available queries on which the new query can be based.

7. Select qryWorkOrderTotalCosts and click Add (or double-click the object name).

7

8. Once the qryWorkOrderTotalCosts query object has been added to the upper portion of the Query

Design window, close the Show Table window.

9. Expand the upper pane and the field list.

10. In the Field List, double-click each of the following fields to add them to the QBE Design grid:

strWorkOrderNumber memDescription Total Cost

11. Place the cursor in the 4th Field cell, and press Shift F2 to display the Zoom box. 12. In the Zoom box, enter the following expression, being careful to type the field names exactly as they

are reflected in the record source: Supervisor:[strLastName]&", "&[strFirstName]

8

Note: The only space in this formula is between the comma and the close quotation marks. There should not be any spaces anywhere else in the expression.

13. Observe the entry:

 The & concatenation operator tells Access to join the next element in the string to the previous item.

 The quotation marks are used because all spaces and punctuation must be indicated as special characters.

 Supervisor: is the label we're assigning to the new field.

Note: You don't have to wait for Access to assign a temporary name of Expr with a number to the field and then change it.

14. Click OK.

15. Run the query and widen the Supervisor column to fully see its contents.

16. Observe the results: The first and last names are joined in a single field and there is a space after the

comma.

17. Save the query as qryCostsByEmployee.

18. Close the query

9

Summarizing with Aggregate Functions

Sometimes you aren't interested in each and every row in your table. You would rather see calculations across groups of data. For example, you might want the total product purchase amount for all companies in a particular state, or you might want to know the average of all sales for each month in the last year. To get these answers, you need a Totals query. A Totals query groups the fields you specify. Every output field must either be one of the grouping fields or the results of a calculation using one of the available aggregate functions. Because all fields are calculated, you cannot update any fields returned by a totals query. The available aggregate functions are:

Function Description

Sum Calculates the sum of all the values for this field in each group. You can specify this function only with number or currency fields.

Avg Calculates the arithmetic average of all the values for this field in each group. You can specify this function only with number or currency fields. Access does not include any Null values in the calculation.

Min Returns the lowest value found in this field within each group. For numbers, Min returns the smallest value. For text, Min returns the lowest value in collating sequence, without regard to case. Access ignores Null values.

Max Returns the highest value found in this field within each group. For numbers, Max returns the largest value. For text, Max returns the highest value in collating sequence, without regard to case. Access ignores Null values.

Count Returns the count of the rows in which the specified field is not a Null value. You can also enter the special expression COUNT(*) in the Field row to count all rows in each group, regardless of the presence of Null values

StDev Calculates the statistical standard deviation of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.

Var Calculates the statistical variance of all the values for this field in each group. You can specify this function only with numbers or currency fields. If the group does not contain at least two rows, Access returns a Null value.

First Returns the value for the field from the first row encountered in the group. Note that the first row might not be the one with the lowest value. It also might not be the row you think is "first" within the group. This is because First depends on the actual physical sequence of stored data. It essentially returns an unpredictable value from within the group.

Last Returns the value for the field from the last row encountered in the group. Note that the last row might not be the one with the highest value. It also might not be the row you think is "last" within the group. This is because Last depends on the actual physical sequence of stored data. It essentially returns an unpredictable value from within the group.

Our objective is to create a query that lists Customer ID's with the Material and Labor Costs for the customers' work orders. Initially, some of the customers will be listed multiple times, because they have multiple work orders assigned to them. But then we will tell Access to group the records by the Customer ID field, and calculate the total and average Material Labor costs. In this way, each customer will be listed only once, including those with multiple work orders. Because the qryPostJuly2002WorkOrders query contains all of the fields we need for our new query, we will make a copy of that query object, give the new query a different name, and then make the necessary changes to it. This will save time.

10

Hands-On Activity 1: Summarizing with Aggregate Functions

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. In the Navigation Panel, right-click on the qryPostJuly2003WorkOrders object.

2. From the shortcut menu, choose Copy.

3. Right-click anywhere in the white area of the Database window, and choose the Paste command

from the shortcut menu.

4. Type qryAggregateTotals as the name of the new query.

5. Click OK.

6. Display the qryAggregateTotals query in Design View.

7. Select the strWorkOrderNumber column in the QBE Design grid, and delete it.

8. Also delete the dtmCompletionDate and memDescription fields. You should now have just three fields in the QBE Design grid:

9. Run the query and observe the results: Some of the customers are listed multiple times. If we tell Access to group by the contents of the Customer ID column and sum the contents of both the curMaterialCost and the curLaborCost fields, it will list each customer only once with a total cost for Material and Labor for each record.

10. Switch to Design View.

11. On the Design / Show/Hide group, click the Totals icon

A Total: row is added to the QBE Design grid, with Group By specified for each field.

11

You will need to leave Group By for the field whose contents are to be grouped (in this case, the Customer ID field) and change the Group By entry to a function to tell Access how to summarize the data in the two currency fields.

12. In the curMaterialCost column, click in the Total cell.

13. Click the v to display the drop-down list, and choose Sum to request a total of the Material Costs for customers.

14. Do the same for the curLaborCost field.

15. Run the query and observe the results:

 Each customer ID number is listed only once.

 Access has summed the values for multiple customer records and reflected the total in both the Material Cost and the Labor Cost columns.

 Access has also added SumOf to the beginning of the field name. You have the option of adding captions to change the column headings.

But suppose you want to see not only the totals, but also an average of the Material and Labor costs for each customer. The good news is that you can add the same field to the QBE Design grid as many times as necessary, and simply change the function in the Total: row.

Hands-On Activity 2: Adding other functions to the Aggregate Summary

Before beginning: The qryAggregateTotals query is displayed in Datasheet View.

1. Switch to Design View.

2. In the Field List, double-click the curMaterialCost field, to add it to the grid a second time.

3. Double-click the curLaborCost field to add it to the grid a second time.

4. Change the Total: cells of the two newly added fields from Group By to Avg.

12

5. Move the second curMaterialCost field to the immediate right of the first curMaterialCost field.

6. Run the query and observe the results: The Sum and Avg of both the Material Cost and the Labor

Cost fields are now calculated.

7. Re-save the query. Leave it open.

Applying Captions to the Summary Fields

As we have seen, Access automatically named the summary fields by simply adding SumOf and AvgOf

to the beginning of the field name. If you want to assign a different heading to the Summary fields, you

need to display the query in Design View, display the Properties window for each field, and add a caption.

13

Hands-On Activity: Adding Captions

Before beginning: The qryAggregateTotals query is displayed in Datasheet View.

1. Switch to Design View.

2. Place the cursor anywhere in the first curMaterialCost column (that is, the one that will provide a Sum of the Material Costs).

3. On the Design / Show/Hide group, click the Property Sheet icon.

4. Place the cursor on the Caption line of the Field Properties window, and enter Total Material as the caption.

5. With the Field Properties window still open, click to place the cursor in the second curMaterialCost

column.

Notice that it is not necessary to close the Field Properties window after entering each caption. As long as the Field Properties window remains open, clicking on a different object will enable you to set the properties for that object.

6. Place the cursor on the Caption line of the Field Properties window, and enter Avg Material as the

caption.

7. Using the same technique, add the captions of Total Labor and Avg Labor to the curLaborCost fields.

8. Once all of the captions have been added, close the Field Properties window.

9. Run the query. The field headings have been changed.

14

10. Re-save and close the query.

On Your Own 1. Create a query that calculates the total number of work orders for each Supervisor. Include the

following fields in the query: Supervisor Full Name Work Order #

2. Sort by Supervisor Name in Ascending order.

3. Change the caption of the Count column to Total Work Orders.

4. Name the query qryWorkOrderCount.

Hints: 1. The Supervisor Full Name field is in one of the queries that you've already created. 2. You'll need to use the Count function in order to calculate the number of work orders.

5. When you have finished, save and close the query.

Creating Crosstab Queries An additional option available to you for summarizing data is a Crosstab query, which is very similar to Pivot Tables in Excel. You simply select from 1 to 3 fields to serve as column headings, from 1 to 3 fields

15

for row headings, and one or more fields whose data are to be summarized. Then you specify the type of calculation to be performed in the summary (Sum, Avg, etc.), and Access does the hard work for you.

As with Pivot Tables in Excel, this feature can save you hours of work! The easiest way to create a Crosstab Query is with the Crosstab Query Wizard. This is the method we are going to use. Our objective is to see a breakdown of the total number of hours worked by all of the employees in the different specialties. Our record source will be tblEmployeeHRData; we'll use the strLastName field in the column headings, the strSpecialty field in the row headings, and the sngHours field as the data to be summarized. Our finished Crosstab query will look as follows:

Note: The specific numbers of hours you end up with in the Crosstab query may not be exactly the hours indicated in the above graphic. This is meant to just give you a general idea of how the query results should look.

Hands-On Activity: Creating a Crosstab Query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. On the Create / Queries group, select the Query Wizard.

2. On the New Query window, select the Crosstab Query Wizard option and click OK.

16

3. The Crosstab Query Wizard starts. The first question deals with the name of the source object for the

query.

4. In the list of table names at the top of the Crosstab Query Wizard window, select Table:

tblEmployeeHRData as the source object.

5. Click Next> to move to the next window. Now the Wizard asks you which field will provide the data for

the row headings.

6. Select the strSpecialty field and click the > button to add it to the Selected Fields list.

17

7. Click Next>. Now the Wizard asks you which field is to be used for the column headings.

8. Select the strLastName field.

9. Click Next>. The Wizard asks you which Number field is to be calculated, and which function you

want to use in the calculation.

10. In the Fields List, select the sngHours field. [Do not click Next>.]

11. In the Functions list, select Sum.

12. Click Next>.

18

13. Type qryEmployeeCrosstab as the name of the query object and click Finish. The Crosstab query

is created.

14. Switch to Design View.

15. Place the cursor anywhere in the Total Of sngHours column.

16. Click the Property Sheet icon on the Design / Show/Hide toolbar.

17. In the Field Properties window, enter a Caption of Total Hours.

18. Close the Field Properties window.

19. Re-save the query.

20. Run the query again, to see the results. Now the heading on the aggregate function field has been

changed, but the other data has not changed.

21. Close the query.

19

Goals for this section:

 Creating a Parameter query

 Creating a Parameter query for a range of dates

 Creating an AutoLookup query

 Observing the impact of the AutoLookup query on the table

 Documenting the Database

Special Purpose Queries

So far, we've created Select Queries as a means of retrieving information from Access tables. There is no question that Select Queries are great and that they will do a lot for you, and, as a result, you will use them a lot. But there is a great deal more to Access queries than this, and we're about to see how fabulous this program truly is. Special Purpose Queries, which is the category of queries that includes Parameter and AutoLookup Queries, will take you to a higher plane in querying your database, because they provide greater flexibility.

Parameter Queries One of the problems you will inevitably face when setting up a database for you and others to use is knowing in advance which criteria to build into Select queries. Suppose, for example, you plan to query a table based on the Start date of different projects. You can't possibly know now which start dates you will want to use in your query in the future. And the last thing you want to have to do is display the Query Design window and enter the criteria before running the query each time. Not only is this a waste of time, but it can also be confusing to (maybe even impossible for) a user who has little or no Access experience. What is the solution? Define the query as a Parameter Query, so that when the query is run, Access can stop and ask you for the criteria that you want to use in the query. Once the criteria have been specified, Access will apply the criteria to the data. In this way, you can change the criteria as many times as you want, and generate a different result each time. To create a Parameter Query, you start by creating a normal Select query. But instead of pre-defining the criteria as part of the query design, you use the Criteria cell of the QBE Design grid to store a prompt for the query. The text that you type in the Criteria cell needs to be enclosed in square brackets, so that Access will know to stop and ask you to specify the parameters for the query. For example, the criterion [Enter the start date] would ask the user to specify a date as the parameter for the query. Then, when the query is run and you have specified the criteria that you want to use, Access applies the indicated criteria to the data and generates the recordset. You can include as many parameters as you want in a single query. Just be aware of the fact that every time Access finds a set of square brackets in the Criteria cell of the QBE Design grid, it will stop and ask you for the parameters to be applied to the data in the record source.

20

Note: Access will also do this if a field name is misspelled in a query. This is, in fact, a common problem that users have in creating Select queries. They enter a calculation into a blank Field cell, but misspell the name of one of the fields being calculated. Whenever that query is run, Access will think it's a Parameter query and will therefore ask for the parameters of the query. If this ever happens to you when you know the query you're running isn't a Parameter query, go back and check the field names in the Query Design screen.

When you define Parameter queries, be careful to make the prompt text in square brackets clear to everyone who will potentially be running the query. Whatever text you enter in square brackets will be displayed in the dialog box when the query is run. Our objective in the Home Tech Repair database is to list the work orders for customers by state. Because we will want to be able to list the work orders for different states at different times, we will define the query with a parameter so that Access will ask us for the state we want to use as the criterion each time we run the query. In the recordset, we will want to see the following fields:

Customer ID Company Name Last Name First Name City State Work Order # Completion Date

The first six fields are in tblCustomer, and the last two are in tblWorkOrders. Therefore, the query will need to be based on these two tables.

Hands-On Activity: Creating a Parameter Query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. On the Create / Queries group, select the Query Design icon.

2. The Show Table window is displayed.

3. On the Show Table window, double-click on tblCustomer to add the field list for that table to the

upper pane of the Query Design window.

4. Double-click on tblWorkOrders, to add that field list to the window. A join line should be connecting the strCustomerID field in the two tables. As a result, the records will display correctly.

21

5. Click Close, to remove the Show Table window from the screen.

6. Resize the upper pane, and then resize the two field lists so that the contents are fully visible.

7. In the tblCustomer field list, double-click each of the following fields, to add them to the QBE Design

grid: strCustomerID strCompanyName strLastName strFirstName strCity strState sngZipCode

8. In the tblWorkOrders field list, double-click each of the following fields to add them to the grid:

strWorkOrderNumber dtmCompletionDate

9. In the Criteria cell of the strState column, enter the following: [Enter the State]

22

10. Run the query. The Enter Parameter Value window is displayed.

11. Enter ma as the criterion.

12. Click OK or press Enter. The 14 work orders for MA customers are listed.

23

13. Save the query as qryWorkOrdersByStateParameter.

14. Close the query.

Using Parameter Queries to Locate Records within a Range of Dates

As we mentioned earlier, you can include as many parameters as you want in a query. The parameter can pertain to different fields (for example, all work orders started after x date and completed before y date) or to a single field (for example, all work orders started between x and y dates). In the case of assigning a parameter to a single date or number field, as in the case of locating all records with values between value x and value y, you need to use the following syntax: Between [Parameter Statement1] And [Parameter Statement2] For example, if you wanted to locate all records in which the order date was between date x and date y or all records in which an invoiced amount was between value x and value y, this type of parameter would be appropriate and useful. Our objective is to display all work orders that were started between x and y dates. We will include the following fields in the recordset:

strWorkOrderNumber strCustomerID strCompanyName dtmStartDate

Hands-On Activity: Creating a Parameter query for a range of dates

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. On the Create / Queries group, select the Query Design icon.

2. On the Show Table window, double-click the tblCustomer and tblWorkOrders tables, to display

their field lists in the upper pane of the Query Design window. Because of the relationship that has

been established between these two tables, a join line is automatically displayed between the two

field lists.

3. Close the Show Table window.

4. In the tblWorkOrders field list double-click each of the following fields:

strWorkOrderNumber strCustomerID dtmStartDate

5. In the tblCustomer field list, double-click the strCompanyName field.

6. Move the dtmStartDate field to the right of the strCompanyName field.

24

7. In the Criteria cell of the dtmStartDate field, enter the following parameter statement:

Between[Enter beginning date]And[Enter ending date]

8. Press Enter, to move the cursor to the next cell. Access adds the necessary spaces between words and checks for errors in the statement.

9. Run the query. The first parameter window is displayed.

10. Enter 6/1/03 as the beginning date and click OK. The second parameter window is displayed.

11. Enter 6/30/03 as the ending date and click OK. The 5 work orders that were started during the month

of June are displayed in the recordset.

12. Save the query as qryStartDateParameter.

13. Close the query.

On Your Own

Create another Parameter query to pull out the work orders that were started and finished within a range

of dates.

In the query, do the following:

25

 Include the following fields:

strSupervisorIDLookup strWorkOrderNumber memDescription dtmStartDate dtmCompletionDate strCustomerID strCompanyName strLastName strFirstName

 Design the query so that when it's run, you're asked to enter the Start Date and the Completion Date. [Use July 1 - 31, 2003 as the test parameters.]

 Name the query qryStartToFinishParameter. When the query is run successfully using the July 1 - July 31, 2003 test dates, there should be 4 records in the recordset.

WARNING: Be careful with the criteria you specify regarding the dates. If you just use a statement telling the user to enter the start date, for instance, Access will only be looking for that specific date, not a range of dates beginning at that point.

 Close the qryStartToFinishParameter query when you have finished testing it.

AutoLookup Queries

This type of query will save you an incredible amount of data-entry time, in addition to ensuring accuracy. The principle is this:

1. You create a One-to-Many relationship between two tables, one of which is empty and the other contains all of the information to be "looked up" and entered into the first table. For example, you could enter a Customer ID and have Access look up the corresponding Name and Address information for that customer. Or, you could enter a Zip code, and Access could look up the corresponding city and state for that Zip code.

2. The "One" side of the relationship is the Lookup (parent) table, and the "Many" side is the child table in which the data are being entered.

3. In order for this to work, there needs to be a match of one field in the two tables. In the parent table, this matching field has to be defined either as the primary key field or as a unique index. An ID field that contains unique data in both tables is ideal. There should be no primary key defined for the "Many" side table.

4. Once the relationship has been established between the two tables, you create a Select Query based on the parent and child tables of the relationship. Include in the query all necessary fields from the "Many" (child) table, and just the Lookup field from the "One" (parent) table.

26

For example, if you were planning to enter the Zip code and have the City and State information be looked up, the Zip code field would have to be added to the QBE Design grid from the Lookup (parent) table.

5. Finally, when you run the query, you'll be entering the data that will "trigger" the lookups (for instance, the Zip code in the above example). All other data will automatically be looked up and entered into the other fields of the datasheet.

Hands-On Activity 1: Creating an AutoLookup query

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

We will create a Supplier table, which will be used for data-entry purposes. We will then establish a One-to-Many relationship between this table and another table that contains supplier information and will therefore serve as our Lookup table. To enter the supplier information, we will just enter the Supplier ID into the query that is based on the Supplier table. Access will look in the other table, match the ID's of the two tables, and fill in the Supplier Name and Address information for us.

Step 1: Create the Child table 1. In the Home Tech Repair database, create a new table with the following 7 fields:

Field Name Data Type Field Size Caption

strSupplierID Short Text Double Supplier ID

strSupplierName Short Text 35 Supplier Name

strSupplierAddress Short Text 30 Supplier Address

strSupplierCity Short Text 15 Supplier City

strSupplierState Short Text 2 Supplier State

sngSupplierZip Number Long Integer Supplier ZIP

strSupplierContactName Short Text 30 Supplier Contact

2. Save and name the table tblSuppliers.

3. Please note the following:

 There will be no records in this table, because it is the "Many" side of the relationship and it's the table into which we will be entering data.

 In addition, there will be no primary key field in this table so that the user could potentially enter the same Supplier ID multiple times.

Step 2: Create (or import the data for) the Parent (Lookup) table 1. Import (from Excel) the file named Supplierinfo.xls, into a new table called tblSupplierInfoLookup.

This will give you 11 records of supplier data, which will be used to look up the name and address of each supplier when you enter the Supplier ID into tblSuppliers.

This will be the "One" side of the relationship.

The strSupplierID field must be defined as the primary key field of the table.

2. Rename the fields following the Leszynski Naming Convention, and assign captions to the fields.

3. Change the Data Type of the strSupplierID field from Number to Short Text.

27

4. Reduce the field sizes of the Text fields from 255 to something more reasonable.

5. For the Supplier Zip field, add a format of 00000 so that the Zip codes are displayed with 5 digits (and therefore have their lead zeros). [There should be 11 records in this table.]

Step 3: Create the relationship between the two tables 1. Create a One-to-Many relationship between the two tables, using the Supplier ID field as the

matching field.

2. There is no need to enforce Referential Integrity between the two tables. Step 4: Test the Lookup 1. In the Home Tech Repair database, create a new query based on the tblSuppliers and

tblSupplierInfoLookup tables.

2. Include in the design the following fields:

tblSuppliers strSupplierID

tblSupplierInfoLookup strSupplierName strSupplierAddress strSupplierCity strSupplierState sngSupplierZIP strSupplierContactName

3. Name the query qrySupplierLookup. 4. Run the query. A blank grid is displayed. 5. Enter 1234 as the first Supplier ID, and press Tab to move the cursor to the next field of the grid. The

Supplier Name and Address information for Ace Masonry Supplies should be automatically entered into the other 6 fields.

6. Press the on the keyboard to move off the focus and save the record. 7. In the Supplier ID field of record 2, enter 3456. 8. Press Tab. The information for Boston Building Supply is entered. 9. Enter the following Supplier ID's into the table:

4567 6789 2345

At this point you should have 5 records in the datasheet:

10. In the Supplier ID field of record 6, enter 1234.

28

11. Press Tab to move to the next column. The information for Ace Masonry Supplies is again entered.

Because the Supplier ID isn't the primary key field in tblSuppliers, you're able to enter an ID multiple times.

12. Move off the focus to save the record. 13. Save & close the query.

Hands-On Activity 2: Observing the impact of the AutoLookup query on the table Now let's take a look at tblSuppliers to see what happened (and what didn't happen) to it as we were filling in the query datasheet.

Before beginning: The Home Tech Repair database is open, and there are no objects currently open.

1. Open tblSuppliers in Datasheet View and observe the fields: Only the Supplier ID has been filled in

because Lookup data are not filled back into the table on which the query was based. Therefore, if you want to be able to use the contents of the finished Supplier Info table for a form or report, you'll have to use the query rather than the table as the record source.

2. Close the table. 3. Open qrySupplierLookup in Datasheet View. 4. On the Create / Forms group, select the Form icon.

5. A new form is created in "‘layout view" based on the query. This is a basic form that was previously known as AutoForm. Data cannot be entered in this view.

29

6. Switch the Layout View to Form View by clicking on the Home / View icon and selecting Form View.

Note the difference between the 2 views. Layout view shows outlined boxes as you move the cursor from field to field where as Form view allows you to edit the data.

30

7. Add a new record to the form, by clicking the button at the bottom left corner of the Status bar. A blank form is displayed.

8. Enter 4444 as the Supplier ID, and press Tab. The information for North Shore Builders is entered into the other fields of the record.

9. Move off the focus, to save the record.

10. Save and enter frmSupplierDataEntry as the form name and click OK.

Notice that the name on the Form Header remains qrySupplierLookup (the name of the source object). The form is given the same name as the object on which it was based. Even after you give the form a new name (with Save command), the name on the title bar (the tab) of the window doesn't change. This can be confusing and disorienting for you and others who might be using the database. To change the name on the title bar of the window, display the form in Design View, display the Properties window, and add a caption.