ACCESS LNC

week_7_pdf3_1.pdf

Home >Computer Science homework help >ACCESS LNC

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!

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

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.

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

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

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).

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]

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

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.

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.

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.

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.

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.

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

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.

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.

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>.

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.

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.

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.

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]

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.

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.

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:

 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.

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.

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.

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.

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.

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.

11. Switch to Design View

12. Click the Property Sheet icon on the Design / Tools group.

13. On the Form's Format / Caption Properties dialog box, add the caption Supplier Data Entry.

14. Save and look at the form in Form View. Note the tab name change.

15. Return to Design View.

16. Click in the Form Header section and edit qrySupplierLookup to Supplier Lookup.

17. Switch to either Form View or Layout View.

18. Re-save and close the form. 19. Close the qrySupplierLookup query.

Documenting the Database Once you have created different types of queries, it's a good idea to add a description on the Navigation Pane to explain what the query does, provide a note of caution where needed, etc. Where Parameter and Action queries are concerned, in particular, this can be useful. You don't want users to run a query thinking it's just a Select Query, when it isn't. In order to be able to view descriptions on the Navigation Pane, the View By must be set to Details. The default view is List, which displays only the object icons and their names. The Details view, on the other hand, will provide Name, Description, Modification Date, Creation Date, and Type columns for each object in the window. To change the Navigation Panel view to Details, right-click on the All Access Object's group title bar, point to View By and choose Details.

To add a description to an object, you do as follows:

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

2. From the shortcut menu, choose Table Properties. Type the description that you want applied to the selected object.

3. Click OK.

Hands-On Activity: Documenting the Database

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

1. Right click on Queries group object on the Navigation Pane.

2. From the short cut menu, select View By followed by Details. (Be sure that the cursor is in the

Queries Object box and not in the area where the queries are listed.)

This view will display not only the name of the object, but also the Modification and Creation Dates,

and the type of object that it is. There is also a Description column available to you, so that you can

add notes on the different objects.

3. Right-click on the qryStartDateParameter query object.

4. From the shortcut menu, choose Object Properties.

The qryStartDateParameter Properties dialog box is displayed. This is where you type the

description for the object.

5. In the Description box of the Properties dialog box, type Parameter Query as the description. [Do not

click OK yet.]

6. Select the description that you just typed, and press Ctrl c to copy the description.

Note: You have to use the Control c keyboard shortcut to do this.

Since we will want to use the same description for the other two Parameter queries, we can copy and then paste it to save time.

7. Click OK, to record the description and close the properties window.

8. Right-click on the qryStartToFinishParameter query object, and choose Object Properties.

9. Leave the cursor in the Description text box, and press Ctrl v to paste the copied description.

10. Click OK.

11. Follow the same procedures to add the same description to the qryWorkOrdersByStateParameter

query object.

Descriptions have now been assigned to the three Parameter queries, so that you and others who use this database will know at a glance that when they try to open those queries, they will be asked to enter criteria.

Note: When viewing the details on the Navigation pane, all query objects are displayed as Query. But when you view the query’s property, the type of query is displayed.

On Your Own

1. Add the following description to the qrySupplierLookup query object:

When the Supplier ID is entered, data are looked up in the tblSupplierInfoLookup table.

2. Expand the Navigation Panel to see the complete descriptions.

When finished, the Queries window should look as follows:

Goals for this section:

 Creating an Update query

 Creating an Append query

 Creating a Delete query

 Creating a Make-Table query

Creating Action Queries An Action query is a query that does something more than simply select a specific group of records and presents it to you in a recordset. Action queries are used for performing operations on one or more tables at once. They enable you to create new tables (Make-Table) or change data (Delete, Update, and Append) in existing tables. Action queries affect many records as a single operation. As a result, they are not to be taken lightly. When you run Select and Parameter queries the recordset of matching records is displayed, whereas when you run Action queries Access changes records or tables in accordance with the type of query that's being run. Be very careful with these queries! Action queries are destructive; before performing one, always make a backup of the underlying tables. You may also consider removing the action query from your database after the action has been performed if the query will not be used again. There are four types of Action queries in Access: Update Queries These queries will make changes to the data in groups of records, and as a result they're extremely valuable. You save time and eliminate many of those typos that crop up in manually edited records. Suppose, for example, you have a Customer table that includes handling charges for customer orders. New customers (that is, the ones who have placed orders since the first of this year) will be charged 5% of the order total, whereas older customers will continue to be charged the old amount of 4%. Let us assume you have 3,000 records in your table. You certainly don't want to have to go through the table record by record, looking for the new customers so that you can recalculate their handling charges. Even if you were to use a Select query to locate the new customers, you still don't want to have to recalculate the handling charges manually. The solution would be to create an Update Query that tells Access to increase the handling charge in all records where the first order date is on or after 1/1/2003. Append Queries This is another extremely valuable query type because it will enable you to add a group of records from one or more tables to the end of other tables. If you wanted to archive inactive records in a separate table, for example, this would be the easiest way to do it. Suppose one of your former customers, whom you haven't heard from for more than four years, wants to make a purchase; you need to bring the old information back into the active file from the backup files. Use an append query to add records from your backup tables to your active tables. Append queries are useful for adding information to another table on the basis of some scoping criteria. Even so, append queries are not always the fastest way of adding records to another database. For example, if you need to append all fields and all records from one table to a new table, the append query is not the best way to it. Instead, use the Copy and Paste options on the Home / Clipboard group when you're working with the table in a datasheet or form.

NOTE: You can add records to an open table. You do not have to close the table before adding records. However, Access does not automatically refresh the view of the table that has records added to it. To refresh the table, press Shift+F9. This action refreshes the table so that you can see the appended records.

When you are working with append queries, be aware of these rules:

 If the table you are appending records to has a primary key field, the records you add cannot have Null values or duplicate primary key values. If they do, Access will not append the records and you WILL NOT get a warning.

 If you add records to another database table, you must know the location and name of the database.

 If you use the asterisk (*) field in a field's row in Design View, you cannot also use individual fields from the same table. Access assumes that you are trying to add field contents twice to the same record and will not append the records.

 If you append records with an AutoNumber field (an Access-specified primary key), do not include the AutoNumber field if the table you are appending to also has an AutoNumber field and record contents (this causes the problems specified in the first rule). Remember that a single table can have just one field with a data type of AutoNumber. Also if you are adding to an empty table and you want the new table to have a new AutoNumber number (that is, order number) based on the criteria, do not use the AutoNumber field.

When an Append query is run, records are copied, not moved, from one table to another. Therefore, if your plan is to archive inactive records, you probably don't want the records to remain in the original table and will need to use a Delete query to delete them.

Delete Queries Of all the action queries, the Delete query is the most dangerous. Unlike the other types of queries, delete queries remove records from tables PERMANENTLY and IRREVERSIBLY. If you intend to delete related records from multiple tables, however, you must do the following:

 Define relationships between the tables in the Relationships Builder.

 Check the Enforce Referential Integrity option for the join between tables.

 Check the Cascade Delete Related Records option for the join between tables (for one-to-one or one-to-many relationships).

When working with one-to-many relationships without turning Cascade Delete on, Access deletes records from only one table at a time. Specifically, Access deletes the many side of the relationship first. You then must remove the 'many' table from the query and delete the records from the one side of the query. This method is time consuming and awkward. Therefore, when you are deleting related records from one-to- many relationship tables, make sure that you define relationships between the tables and check the Cascade Delete box in the Edit Relationships dialog box. By doing this, you can delete from all related tables by creating a single Delete query. This type of query will enable you to locate and then delete a group of records from one or more tables. For example, in your Customers table you might want to delete all of the cancelled orders. Make-Table Queries This type of query takes a selection of records from a database, and saves those records into a new table (it makes a new table) from the data in the recordset.

For example, you want to create history tables and then copy all inactive records to them. You consider a record inactive if a customer hasn't bought anything in more than two years. You decide to remove the inactive records from your active database tables. Use a Make-Table query to create the history tables and a Delete query to remove the unwanted records. It is another option for archiving old or inactive records in a new table, or for exporting Access data to Word or Excel.

Note: If you were to use this method to create an Access archive table initially, you could then use an Append Query to add other records to the same table in the future.

When the query is run, records are copied to the target table. In addition, a snapshot of the source records is created, so there is no dynamic link between the records in the new table and those of the source object.

Some Words of Caution about Creating and Using Action Queries Before doing any kind of action query, make a backup of the tables that will be affected by the query.

Remember: Things do go wrong with database files, so get your safety nets in place!

An additional safety precaution you can practice while designing an action query is to switch to Datasheet view to check your progress, instead of running the query. Showing the results in Datasheet view doesn't actually run the query and carry out the intended action, so no data is changed.

This is a very good strategy where Action queries are concerned. Check them before running them, to make sure you haven't made any potentially disastrous mistakes.

Caution: In File / Options / Client Setting / Editing there is a Confirm with three options. One of which is Action queries. By default, this option is checked, so Access always asks for confirmation before carrying out an action query. You can clear this option to prevent the display of the confirmation box, but doing so is risky. Running a query from the Navigation Pane (by double clicking the query) is so easy. If you clear the Confirm option, you won't even be warned when an action query is about to be run. I strongly suggest that you do not change the default setting.

Update Queries Update queries in general will save you a great deal of time because they will eliminate the need to make the same change to hundreds or thousands of individual records in a table. What we're talking about here is the difference between spending a couple of minutes to get the Update Query set up correctly, versus many, many hours to change each record individually.

Hands-On Activity: Creating an Update Query

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

currently open.

Our objective: Imagine its June 30th. A number of bids are about to expire, but can be renewed. However, before they can be renewed, the Material and Labor costs need to be increased by 5% to reflect inflation. In addition, a new expiration date needs to be set, by adding 90 days to the dtmExpires field. We will create an Update Query based on tblBidData and tblWorkOrders to locate all of the records that have an expiration date of 7/1/03 and beyond. Those records will have a 5% increase made to the Material and Labor costs, and 90 days will be added to the expiration

date of those records.

1. Open the tblWorkOrders table, and click the plus sign to the left of record 15 (for work order 015).

Because of the relationship between this table and tblBidData, you are able to see the work order

information and the corresponding bid information for that work order.

2. Observe the expiration date for this bid: 7/7/2003.

When we run the Update Query, this record will have 90 days added to its expiration date.

3. Observe the Material Cost ($420.00) and the Labor Cost ($100.00) for the work order. When the Update Query is run, these costs will increase by 5%.

4. Close the tblWorkOrders table.

5. Click the Query Design icon on the Create / Queries group to create a new query object.

6. Double-click on tblBidData and on tblWorkOrders in the Show Table dialog box, to add both tables

to the Query Design window.

7. Close the Show Table window.

8. In the tblBidData field list, double-click on the strBidNumber and dtmExpires fields, to add them to the QBE Design grid.

9. In the tblWorkOrders field list, double-click the curMaterialCost and curLaborCost fields to add them to the grid.

10. Save the query as qryUpdateCosts.

11. From the Design / Query Type, choose Update to change the query type.

12. Observe the changes that have occurred in the QBE Design grid: An Update To: row has been added.

This is where you'll specify the changes that you want to occur in the fields when the query is run. 13. Place the cursor in the Update To cell of the dtmExpires field, and enter the following:

[dtmExpires]+90

This will add 90 days to the expiration date of each record that's updated.

14. In the Update To cell of the curMaterialCost field, enter the following: [curMaterialCost]*1.05

This will add 5% to the current material cost of each record that's updated. 15. In the Update To cell of the curLaborCost field, enter the following:

[curLaborCost]*1.05

This will add 5% to the current labor cost of each record that's updated. 16. In the Criteria cell of the dtmExpires column, enter the following:

>=7/1/03

This will establish July 1, 2003 or after as the criteria for the update. If a record has an expiration date of July 1, 2003 or later, the expiration date will have 90 days added to it and the material and labor costs will be increased by 5%.

What you have told Access thus far is this:

 Locate all records in which the Expiration date is on or after July 1, 2003.

 In the found subset of records, add 90 days to the Expiration date and increase both the Material and the Labor costs by 5%.

17. On the Design / Results, click the Datasheet View icon.

[Do not click the Run icon!] This is the strategy that was mentioned under Some Words of Caution earlier in my notes. Test the query by checking the View first, to make sure you haven't made a serious mistake. This can be especially helpful when you're designing Update queries, because once you run the query, there might be no turning back. 7 records are displayed for all of the bids that are scheduled to expire after July 1st.

18. Observe the last record:

 The expiration date is still 7/7/2003.

 The Material Cost is still $420.00.

 The Labor Cost is still $100.00.

These values haven't changed yet because we haven't run the query. Checking the View to see the results of a query is NOT the same as running the query.

19. Switch to Design View. 20. Run the query. Access displays an Alert Box to warn you about what you're about to do.

21. Click Yes.

22. Re-save the query.

23. Close the query and observe the symbol to the left of the query object icon in the Navigation Pane.

24. Add the following Description to the qryUpdateCosts query object:

Caution: Increases costs by 5% for bids with expiration date >=7/1/03.

25. Open the tblBidData table, and observe the changes for record 25:

The Expires date is now 10/5/2003 26. Close tblBidData.

27. Open tblWorkOrders and observe the changes to the material and labor costs in Work Order 015:

 The Material Cost is now $441.

 The Labor Cost is now $105. 28. Close tblWorkOrders.

A Special Note of Caution This is where you need to be especially careful. What do you think would happen if you were to run the qryUpdateCosts query again?

Hopefully you realize that every time you run this query the dates and costs will once again be increased for all of the records in which the Expires date is >=7/1/03.

Danger, danger, danger! As a result of this potential danger, once you create and run an Update query you might want to assess whether or not you'll need to use it again soon. If not, you can delete the query so it isn't inadvertently run when it shouldn't be. We will leave the qryUpdateCosts query in our database, but just keep this in mind for future reference in your own database files.

Append Queries In my opinion, Append Queries are the best of this group of action queries, mostly because of our seemingly continuous need to archive old or inactive records. Once you have a table set up as your archive table, all you have to do is locate all of the records in your current tables to be archived, and then shoot them out (so to speak) to the archive table by means of the Append Query. The term append means, quite simply, to add to the end of something. And that is exactly what the query will do. All of the records in the found set will be dumped into the target table and added to the end of the existing records. This way, you won't be left high and dry if down the road you realize that those records you destroyed contain information you really need. Remember, the greater the number of safety nets you put out for yourself, the happier you will be when something goes wrong... and things will go wrong.

Hands-On Activity: Creating an Append Query

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

Our objective is to locate all of the records in the tblBidData table that have "Not Awarded" in the Award Date field, and copy them into an archive table. In order to do this, we'll need to create our Archive table first.

1. Open the Tables object on the Navigation Panel.

2. Right-click on the tblBidData table object.

3. From the shortcut menu, choose Copy.

4. Right-click anywhere in the white area of the Database window and choose Paste.

5. In the Paste Options section of the Paste Table As dialog box, select the Structure Only option.

6. In the Table Name text box, enter tblLostBidArchive as the name of the new table.

7. Click OK. The new table is created, and its name is added to the list of Table objects.

8. Click on the Query Design icon on the Create / Queries menu to create a new query object.

9. In the Show Table dialog box double-click on the tblBidData table object, to add that table to the QBE Design window.

10. Close the Show Table window.

11. From the Design / Query Type group, choose Append Query to change the query type.

The Append dialog box is displayed.

12. Display the drop-down list for the Append To Table Name text box, and choose tblLostBidArchive.

This is the name of the target table in which the matching records will be appended.

13. Leave the Current Database option selected, since we're copying records from one table to another within the same database file.

14. Click OK. Observe the changes to the QBE Design grid:

An Append To: row has been added so that Access will be able to keep track of the name of the target table to which the matching records are to be appended.

15. In the tblBidData field list, double-click the asterisk (*) to add all of the fields in the table to the grid.

Whenever you want all of the fields in a table to be included in the query, this is the easiest way to do it, rather than adding each field individually. Notice that the fields are not displayed in the grid but Access knows that Field: tblBidData.* means all fields.

16. In the field list, double-click the strAwardDate field to add it to the grid.

Note: If you use the asterisk to add all fields to the QBE Design grid, you will still need to add individual fields for sorting purposes or for, applying criteria to the query.

17. In the Criteria cell of the strAwardDate column, specify "Not Awarded"

Note: The double quotation marks are necessary because without them, Access would interpret the word Not as a Logical Operator.

18. Click the Datasheet View button on the toolbar, to check the results. 6 records should be displayed.

19. Scroll to the right in the Datasheet and observe the last field in the recordset: Field0 contains Not

Awarded, which is the same value that appears in the Award Date field.

This field is here because the Award Date field was added to the QBE Design grid twice: Once through the asterisk, which added all of the fields, and a second time by itself, so that we could specify a criteria for that field. We will need to fix this so that Field0 doesn't display in the recordset, since it's superfluous.

20. Switch to Design View.

21. In the Append To cell of the strAwardDate column, remove strAwardDate so that the field won't be

appended twice.

Note: Do not remove the field from the Field cell, just from the Append To cell.

22. Save the query as qryAddToArchive.

23. Run the query. Access tells you that you're about to append 6 records.

24. Click Yes.

25. Close the query.

26. Open the tblLostBidArchive table. The 6 appended records should be there. This table could be used from now on to append other un-awarded bids.

27. Close the table.

Delete Queries This type of query is next in line as being an extremely important one to use, because it complements the Append Query. Once you have located all of the inactive records in a table, you will then want to delete those records from the table they had been in previously. Running an Append query does not automatically delete the appended records from their previous location. Therefore, if you don't delete them yourself, you will end up with the archived records in two locations, thereby defeating the purpose of having run an Append Query in the first place.

Hands-On Activity: Creating a Delete Query

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

Our objective is to delete the 6 archived records where "Not Awarded" appears in the Award Date field from the tblBidData table.

1. Using the Query Design, create a new query based on tblBidData.

2. Add the asterisk to the Field cell, to add all of the fields to the grid.

3. Add the strAwardDate field, so that you can specify the criterion.

4. Choose the Delete icon on the Design / Query Type group.

5. Observe the changes to the QBE Design grid:

6. Enter "Not Awarded" (in double quotation marks) in the Criteria cell of the strAwardDate column.

7. Switch to Datasheet View, to check the results. 6 records should be displayed.

. 8. Return to Design View and run the query. Access warns you that you're about to delete 6 rows from

the specified table.

9. Click Yes.

10. Save the query as qryArchiveDelete.

11. Close the query.

12. Observe the icon that appears next to the Delete query object:

13. Add the following descriptions (on the Object Property dialog box) to the Append and Delete queries that you have just created:

qryAddToArchive: Appends old records qryArchiveDelete: Deletes Not Awarded bids

Make-Table Queries This query type is also a good one. If you need to put Access data into Word or Excel, you will need to convert the data in your Access table or query into a table format, so that it will be readable in Word or Excel. You can also simply generate query results based on multiple related tables, and then convert those results into a new table. The possibilities here are endless. The bottom line with Make-Table queries is this: The found set of records in the query is converted into a new table that will function as if it had been created as a table object from the beginning. One of the shortcomings of a Make-Table query is that it propagates only the field name and data type to the resulting table. Running the query does not set other property settings such as Caption or Decimal Places in the target table. This is why you see only field names instead of the original captions in Datasheet view. In addition, the sequence of rows in the new table does not match the sequence of rows you see when you look at the Datasheet view of your make-table. This is because the data in a table created with a make-table query has no primary key, Access returns the rows in the order that they are stored physically in the database.

Hands-On Activity: Creating a Make-Table Query

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

Our objective is to create a query based on the tblEmployeeHRData and tblWorkOrders tables, and to list all of the work orders currently assigned to Rick O'Brien. Once the recordset has been generated, we will convert it to a table.

1. Using the Query Design, create a new query based on tblWorkOrders and tblEmployeeHRData.

2. From tblEmployeeHRData, add the following four fields:

strEmployeeID strSSN strLastName strFirstName

3. From the tblWorkOrders table, add the following three fields: strWorkOrderNumber memDescription dtmCompletionDate

4. In the Criteria cell of the strLastName column, enter O'Brien.

5. Switch to Datasheet View, to check the results. 5 records should be displayed.

Now that you know that you have defined the parameters of the query correctly, you're ready to convert this Select Query into a Make-Table query and generate a new table from this found set of records.

6. Switch back to Design View.

7. From the Design / Query Type group, choose Make Table

The Make Table window is displayed so that you can specify the name and location of the new table.

8. In the Table Name text box, enter tblO'BrienWorkOrders.

9. Leave the Current Database option selected.

10. Click OK.

11. Save the query as qryMakeTable.

12. Run the query. Access warns you that you're about to paste 5 records into a new table.

13. Click Yes.

14. Close the query.

15. Open the tblO'BrienWorkOrders table. The 5 copied records are in the table.

Notice that the fields have not been assigned Captions, and the strSSN field doesn't have an Input Mask as it did in the source table. The Input Mask and captions can easily be assigned to these fields in Design View. We don't need to do this now.

16. Close the table.

Assignment for Week 7

1. Review chapters 8, 9, 10 of the textbook. 2. Do Final Database Project Assignment #4. Due by midnight Eastern Standard Time on

Sunday March 6, 2016.

This is the fourth of the 8 individual assignments that you'll be doing to create your own

database.

In this assignment, you're going to create a series of Select queries. These are not all going to

be as simple as they might appear at first glance. I have specified a Field and Sort order for the

queries, so please make sure you follow these precisely. They are intended to address specific

Access query issues.

Here's the assignment:

Query #1

1. List all active customers. Include the following fields, in the order in which they appear below:

Customer ID

Customer Name

Contact First Name

Contact Last Name

Contact Phone

2. Sort by Customer Name, then by Contact Last Name, then by Contact First Name.

Note: All 3 of these Sorts need to be done at the same time. In other

words, the Customer Name field is the primary Sort key. When there are

duplicate Customer names, the list will be sorted by the Contact Last

Name field, and when there are duplicate last names the list will be sorted

by Contact First Name.

3. Name the query qry#1ActiveCustomers.

Query #2

1. List all customers in territories 1 and 5 who have bought Standard lumber, and all customers in territory 3 who have bought 3+ Ash.

2. Include the following fields in the query, in the order in which they appear below: Customer ID

Customer Name

Territory Name

Sales Rep ID

Sales Rep Last Name

Product Code

Product Description

3. Sort by Product Description, then by Customer Name, in Ascending order.

4. Name the query qry#2TerritoriesAndProducts.

Query #3

1. List all customers with the products they have ordered since and including 1/1/03.

2. Include the following fields in the query, in the order in which they appear below: Customer Name

Product Description

Product Code

Quantity Ordered

Date of Order

3. Format the Quantity Ordered field to display with commas and zero decimal places.

4. Sort by Product Description, then by Customer Name, in Ascending order.

5. Name the query qry#3CustomersAndOrders.

Query #4

1. Create a query that calculates the total product cost and the discounted price for all products.

2. Include the following fields in the query, in the order in which they appear below: Product Description

Product Code

Customer Name

Quantity Ordered

List Price

Discount

Base Cost (=Quantity multiplied by the List Price)

Discounted Cost (=Base Cost minus the Base Cost multiplied by the Discount)

3. Format the Base Cost and Discounted Cost fields to Currency with 2 decimal places and commas.

4. Format the Quantity Ordered field to display commas with 0 decimal places.

5. Format the Discount value to Percent with 1 decimal place.

6. Sort in Ascending order by Product Description, then by Customer Name.

7. Name the query qry#4TotalProductCost.