1

Start Access. Download and open the file named Access_Ch03_Prepare_Spa_C.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

If you need to see a quick snapshot of statistics for a table or query, you can use the total row. The total row is a special row that appears at the end of a datasheet that enables you to show aggregate functions for one or more fields. The manager would like to quickly find the total of all unpaid invoices and count the number of unpaid invoices.

Using Query Design, create a query that returns InvoiceDate, InvoiceTotal, and Paid from tblInvoice, as well as, CustFirstName and CustLastName from tblCustomer (in that order).

Limit the results to only those records where the invoice has not been paid.

Sort the results in descending order by InvoiceDate.

After running the query, add the TotalRow to the dataset, sum the InvoiceTotal column and count the First Name column.

Name the query qryUnpaidInvoices
Save and close the query.

20

3

Aggregate functions can be used in queries to perform calculations on selected fields and records. One advantage to using aggregate functions in queries, rather than just a total row, is that you can group criteria and then calculate the aggregate functions for a group of records. The manager would like to see how many products are offered, the average product price, and the minimum and maximum product prices.

Using Query Design, create a query that lists the Price field from tblProduct four times. Apply the Count, Avg, Min, and Max aggregate functions on each of the Price fields (in that order from left to right).

Name the query qryProductStats
Save but DO NOT close the query.

20

4

Field names in aggregate queries are a composite of the selected aggregate function and the table field name. The field names assigned in an aggregate query can easily be changed either before or after the query is run. However, you must keep the original field name in the query design grid so Access knows on what field to perform the calculation.

Edit the qryProductStats query so that the Price field where the Count aggregate function is being applied is named NumberOfProducts

Edit the other fields in a similar fashion: AveragePrice LowestPrice and HighestPrice

Use AutoFit to resize the columns so that all fields names are visible.

Save and close the query.

10

5

Not only can you find statistical information for selected records using aggregate functions in a query or for all records using the total row, you can also calculate statistics for groups of records. Create a group to calculate statistics works the same way as an aggregate query but must include the field to group by. The additional field will not have a statistic selected for the total row, but instead, it will have the default Group By entered in the total row. The spa manager would like to see how the statistics calculated in the previous query for each product category.

Copy the qryProductStats query and name the copy qryProductStatsByCategory

Edit the query so that the statistics are grouped by each Category of product. Place the Category field as the first field in the query. Run the query, adjust the column widths so that all data are visible.

Save and close the query.

10

6

The manager tried to create an aggregate query to calculate the total number of items and average number of items purchased by different methods: phone, online, and in person. The results made no sense, and she has asked you to help her figure out why.

Open the qryPurchaseMethods query in Design view, make the necessary correction so that the query displays correct information.

Run the query, adjust the column widths so that all data are visible. Save but DO NOT close the query.

10

7

An aggregate query may give you the correct results, but the formatting may not be what you expected. The fields used in a query that come from a table use the formatting defined in the table design. However, when the field is aggregated, it must be formatted in the query design grid using the Field properties sheet.

In the qryPurchaseMethods query, change the formatting of the AverageSalesVolume calculation to be Fixed with 0 decimal places.

Save and close the query.

10

8

In addition to statistical calculations using aggregate functions, you can perform an arithmetic calculation within a query to create a new field. The result of the calculated field is displayed each time you run the query. However, this new field is not part of any other table.
The spa manager would like a query to show her the total value of the inventory, using the Quantity in Stock and Price fields for each product.

Using Query Design, create a query that returns ProductDescription, Category, QtyInStock, and Price from tblProduct. Sort the results in ascending order by ProductDescription.

Save the query as qryProductInventory

In the fifth column, use the Expression Builder to create a calculated field that multiples QtyInStock by the Price field. Name the calculated field TotalInventory.

Run the query, adjust the column widths so that all data are visible. Save and close the query.

20

9

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

1

Start Access. Download and open the file named YO19_Access_Ch06_Prepare_Golf1_Part_B.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary.

0

2

The IIf function allows you to return different values based on a criterion. The form of the function is IIf(expression,true,false). The expression is a criterion that can be only true or false. For example, [Price]<=10. If the Price is less than or equal to ten, it will return the true argument. If it is greater than 10, it will return the false argument. For example, IIf([Price]<=10,”Low Price”, “High Price”). The true and the false arguments can be text, like the example, it can be a number, or it can be a calculation.

Create a query based on the tblEmployee table that will display FirstName, LastName, Salary, Raise Assessment, Raise Option 1, and Raise Option 2, where RaiseAssessment, RaiseOption1, and RaiseOption2 are new fields using the IIf function as described below and in the next steps, in that order. Management is trying to decide on raises and these calculated fields will help management make that decision.

RaiseAssessment should return Give Raise if the employee salary is $40,000 or less. Raise Assessment should return Maybe Raise if the employee salary is over $40,000.

7

3

RaiseOption1 should return a 4.5% increase in salary for any employee whose salary is $40,000 or less. All other employees should return $0.00. Format Raise Option 1 as currency.

6

4

If you have more than two possible outcomes, you can put an IIf function inside the false part of another IIf function. For example, IIF([Price]<=10,”Low Price”,IIf([Price]<=20,”Medium Price”,”High Price”)) This function would return Low Price when less than or equal to 10, Medium Price for values greater than 10 but less than or equal to 20, and High Price for anything over 20.

RaiseOption2 should return a 4.5% increase in salary for any employee whose salary is $40,000 or less. For any employee whose salary is greater than $40,000 but less than or equal to $75,000, Raise Option 2 should return a 2% increase in salary. All other employees should return $0.00. Format Raise Option 2 as currency.

Run the query, and then save as qryIIf. Close the query.

7

5

In Access, you can return records where values of a field are empty using Is Null as the criteria.

Create a query based on the tblEmployee table that will display FirstName, LastName, and Photo in that order. In the Photo field, use Is Null to display only employees who do not have a photo on file. Run the query, and then save it as qryPhotoIsNull. Close the query.

8

6

In Access, you can return records where values of a field are not empty using Is Not Null as the criteria.

Create a query based on the tblEmployee table that will display FirstName, LastName, and Photo in that order. In the Photo field, use Is Not Null to display only employees who do have a photo on file. Run the query, and then save it as qryPhotoIsNotNull. Close the query.

8

7

You can use the IsNull function inside an IIf as the expressions/criteria. For example, IIf(IsNull([Price]),”Price Omitted”,”Price Shown”) would return the true value of Price Omitted if the Price field value is empty and Price Shown for all prices that have any value – are not empty.

Use the data in tblEmployee to create a query that will display whether the employee has a photo on file or not. The query results should list FirstName, LastName, and PhotoStatus in that order, where PhotoStatus is a new field that displays whether a photo has been taken or not. If the Photo field in tblEmployee is empty, the PhotoStatus field should display Needs Photo. If the Photo field contains a value, the PhotoStatus field should display Has Photo. Run the query, and then save it as qryIsNullFunction. Close the query.

10

8

The DateDiff function allows you to determine the number of months or days and other intervals between two dates. See your book for a table of acceptable intervals. The DateDiff function takes the following form DateDiff(Interval,EarlierDate,LaterDate). For example, DateDiff(”D”,[TransactionDate],[ShippingDate]) would calculate how many days it took to ship after the transaction date.

Create a query based on the tblPayments table that will display MemberID, PaymentDate, and MonthsSincePayment where MonthsSincePayment is a new calculated field, in that order. MonthsSincePayment should calculate the number of months since the last payment using the DateDiff function. Run the query, and then save it as qryDateDiffMonths. Close the query.

15

9

The DateAdd function allows you to add a specified number of days, weeks, or other interval to a date. See your book for a table of acceptable intervals. The DateAdd function takes the following form: DateAdd(interval,number,date). For example, DateAdd(“yyyy”,4,#5/1/2020#) would return the date of 5/1/2024.

Create a query based on the tblMember and tblPayments tables that will display FirstName, LastName, Amount, PaymentDate, DueDateUsing“y” and DueDateUsing“ww” where DueDateUsing“y” and DueDateUsing“ww” are new fields using the DateAdd functions as described below, in that order.

Due Date Using “y” should display the date the payment is due using the “y” interval and 365 days.

Due Date Using “ww” should display the date the payment is due using the “ww” interval and 52 weeks.

Run the query, and then save it as qryDateAdd. Close the query.

18

10

The DateSerial function returns a date based off giving the year, month and day. The DateAdd function take the following form DateSerial(year,month,day). For example, DateSerial(2019,4,5) would return the date of April 5, 2019. You can also use the Year, Month, or Day function with the DateSerial. For example, Year([ShippingDate]) returns the year of the shipping date.

The DatePart function can also return part of a date. The DatePart function takes the following form DatePart(interval,date). For example, DatePart(“yyyy”,#5/1/2020#) would return 2020.

Create a query based on the tblEmployee and tblPosition tables that will display FirstName, LastName, HireDate, BenefitsStart, and 5-YearAnniversary where BenefitsStart and 5-YearAnniversary are new fields using the DateSerial and DatePart functions as described below, in that order.

Add the PositionType field to the last column of the query. Restrict the results to only Full-Time employees. Do not show the PositionType field.

BenefitsStart should display the date for when each employee’s benefits start – which is 91 days after the HireDate. Your calculated field should use the DateSerial function.

5-YearAnniversary should display the year that the employee reaches five years of employment. Your calculated field should use the DatePart function.

Run the query, and then save it as qryDateSerialAndPart. Close the query.

17

11

The Round function will round to the value to the nearest whole number. For example, round(12.51) would return the value of 13.

Open the existing qryRound query. Add a new field after the New Salary field. Name the new field FinalSalary. Final Salary should display the New Salary rounded to the nearest dollar using the round function and formatted as currency.
Run the query, and then save qryRound. Close the query.

4

12

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

1

Open the downloaded Access file named Access_Ch05_Prepare_golf.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary.

0

2

An input mask creates a typing template that helps data to be consistently entered. This also makes entering data easier and faster by providing common symbols such as parentheses.

Open the tblMember table in Design view and create an input mask for the Phone field to make it easier to enter data. Select the appropriate setting so that the data is stored with the symbols in the mask.

It is very important to always test that any changes work correctly. Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes:

Close tblMember.

3.5

3

Open the tblEmployee table in Design view and create a short date input mask for the HireDate field.

Switch to Datasheet view of the tblEmployee table, save it, and enter the following record to test the changes.

Close tblEmployee.

3.5

4

Input masks help ensure data consistency. However, not all of the input masks you may need are provided in the wizard. Sometimes a custom input mask is needed.

Open tblMember in Design view and create a custom input mask for the State field so that data entered is required to be 2 capital letters. Refer to the table below for specifics.

Description: State (2-character abbreviation)
Input Mask: >LL
Placeholder: _
Sample Data: NM
Mask/Type: Text/Unbound

Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes.

Close tbMember.

3

5

ID numbers can be confusing when you have several different ID numbers and they all look like plain numbers. Adding a prefix to the number can help make the data more easily discernable.

Open tblEmployee in Design view and apply a custom format to the EmployeeID field so that all employee IDs entered appear with the letters EMP before the ID number.

3

6

Color can also help differentiate a field and make data easier for users to understand.

Create a custom format for the Position field, in the tblEmployee table, so that data in this field appear with a red font color.

Apply a Medium Date format to the HireDate field, in the tblEmployee table.

4

7

Switch to Datasheet view of the tblEmployee table, save it, and enter the following records to test the changes.

2

8

Validation rules help verify that entered data is accurate given a business rule. When the user enters a value, it evaluates it against the rule. If the data does not follow the rule, then the data is entered. If the data does not follow the rule, the users receives an error with the text in the Validation Text property.

Switch to Design view of the tblEmployee table and modify the Validation Rule property of the HireDate field so that when employee records are added to the table the hire date cannot be entered after 14 days from the current date.

3

9

The Validation Text property is the text for any data that does not meet the validation rule. If no validation text is entered, only a generic – and not very helpful – error message is given.

Modify the Validation Text property of the HireDate field, in the tblEmployee table, to display the following message if a hire date entered is more than 14 days from the current date: This employee began working more than 14 days ago. Please call the Corporate Office at (800) 555-4022. (include the period).

Save the changes and click No when Access prompts you to confirm whether you want to test the existing data against the new rules.

2

10

Modify the Validation Rule property for the Salary field, in the tblEmployee table, so that no salary can exceed $200,000.

3

11

Modify the Validation Text property of the Salary field, in the tblEmployee table, to display the following message if an amount entered exceeds $200,000: The maximum salary an employee can earn is $200,000. Please re-enter this employee’s salary.(include the period).

Save the changes and click Yes when Access prompts you to confirm whether you want to test the existing data against the new rules.

3

12

Switch to Datasheet view of the tblEmployee table and enter the following records to test the changes to the properties.

2

13

Field names are the proper names seen by Access. The caption will show to the user but not change the actual field name used by Access.

Switch to Design view of the tblEmployee table and modify the caption properties for the following fields.

Save and close tblEmployee.

3

14

An index is automatically created for Primary Keys. When a field is an index it helps increase speed of searching and sorting – similar to a page in a book and the index in the back of a book. If too many fields are indexed, then it will slow down adding and updating fields. Thus, you should only index commonly searched fields and where there are more different entries than duplicate entries. The purpose of indexing is only to increase efficiency.

Open the tblPosition table in Design view and create a single-field index using the following information.

Index Properties:
Primary: No
Unique: No
Ignore Nulls: No

Save and close tblPosition.

3

15

Open the tblMember table in Design view and create a multiple-field index using the following information.

Index Properties:
Primary: No
Unique: No
Ignore Nulls: No

3

16

Records with empty values may be ok depending on the data. However, many fields – such as Last name – should be required at all times. When set to Yes, the Required property will prevent a field from being blank when entered for the first time. If that is the only property set, then after a record has been entered the field could still be erased. Access then stores an empty set – or Zero String Length. Thus, when the Allow Zero String Length property is available, it also needs to be set to No to make the field required at all times.

In Design view of the tblMember table, make the following fields required by setting the Required property to Yes and the Allow Zero Length property to No: FirstName, LastName, Address, City, State, and PostalCode.

3

17

The Default value property puts an initial value into the field. A user saves time when that is the correct value. If the value is different, the user can override the value and type in the correct value. If the value will be the same 50% of the time or more, a Default value is appropriate and increases data entry speed.

Using the following information modify the appropriate property of the tblMember table so that when new records are added, data automatically appears in the fields.

3

18

Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes.

Close tblMember.

2

19

Particularly with foreign field keys, the person may not know the value for the data entry – such as a MemberID number. A Lookup data type creates a drop-down list of the ID values and other fields – such as first and last name – to make selecting the proper member easier.

Open the tblMemberLessons table in Design view. Modify the MemberID field to make it a lookup field that will display the MemberID, LastName, and FirstName fields, in that order, from the tblMember table. Sort in Ascending order by LastName an

• Facebook
• Twitter
• Google+
• LinkedIn

Menu

• Home
• About Us
• Services
• Prices
• Guarantees
• Contact Us

Free resources

• Free Essays
• Essay tips
• Essay types
• Plagiarism Checker
• Free Essays

 

Dissertation help

• Free consultation
• Essay examples
• Buy essay
• Dissertation assistance
• Free dissertations
• Coursework help

 

Email

[email protected]

Office Address: 2066 N Capitol Ave, San Jose, CA 95132, United States

eminencepapers.com ©2023 All rights reserved. Terms of use | Privacy Policy | Revision Policy | Money Back Guarantee | Plagiarism Free Guarantee