Access Unit Practice

Modifying the layout of a form or report: Insert a new row into the form, layout above the last name controls Switch to the appointments schedule report Apply the medium padding option to the entire report layout

A field in the last name row has been selected click the insert above button Switch to AppointmentsSchedule report Click the select layout button Click the control padding button Select medium

Adding numeric and date criteria to a query: Add criteria to the limit query to appointments longer than 75 minutes, run the query and review the results Return to design view and add criteria to show appointments for April 1, 2023 and later from the query and review the results

Click in the criteria row under the length field Type the criteria >75 in the criteria row press enter Click the run button Click the view button to return to design view Click the criteria row under the appointment date field Type the criterion >=4/1/2023 in the criteria row press enter Click the run button

Adding Text Criteria to a Query: Add a criterion to the query so the results will include only records were the value of the state field is equal to the text CO. run the query and review the results Return to design view and modify the query so the results will include any record where the value of the state field begins with the letter C, from the query, and review the results

Click in the criteria row under the state field Type the text criterion "CO" in the criteria. Press, enter Click run Click the View button to return to design view Click in the criteria row under the state field Type Like "C*" as the new text criterion press enter Click run

Adding a Calculated Field to a Query: Add a new calculated field named SuggestedTip in the first empty column to the right of the Location field. The new field should calculate a value that is .15 times the value in the Price field. Run the query and review the results.

Click in the empty cell in the Field row in the query grid to the right of the Location field. Click the Builder button. Double click the price field in expression categories Enter *.15 to finish the expression. Press Enter to submit your answer. Click ok Enter *.15 to finish the expression. Press Enter to submit your answer. Click Run

Specifying the sort order in a query: Set the sort order so the results will display records sorted alphabetically from A-to-Z by values in the service name field run the query and review the results

Click in the sort row under the service name field Click the arrow that appears in the sort row Click ascending run

Exporting a Report to Other Formats: From preview view export this report as a PDF file use the suggested location and file name From print preview view export this report as a word file use suggested location and file name

Click the PDF or XPS button Click publish Click close Click the more button Click word Click ok Click close

Adding fields to a report in layout view: Display the field list pane From the appointments table at the appointment, date field, and then the appointment time field From the customers table at the last name field, and then the first name field From the services table at the service name field, and then the price field

Click the add existing fields button Click the plus in front of appointments Double click appointment date Double click appointment time Click the plus in front of customers Double click last name Double click 1st name Double click service name and price

Creating a table in design view and setting the primary key Add a new table in design view Name the first field product ID, and set the data type to short text add the description, internal product ID and move to a new field Enter price in field name and currency for data type Set the ProductID field as the primary key field Save the table with the name: Products

Click the create tab Click the table design button Enter the field information Click the product ID field Select the primary key button

Creating and saving a table and data sheet view: Create a new table in data sheet view Allow access to create the first auto number field named ID enter data for the second field, bobs, mud shop in advance to the next field

Click the create tab Click the table button Enter data for first record Press, Ctrl S Name the table suppliers Click ok

Using a Parameter Query: Create parameter query where the user will enter value to use of the criterion for the last name field use the prompt Enter last name don't forget to close the prompt brackets run the query Test the parameter query using last name, Swanson

Click the criteria row under the last name field Type [Enter last name] in the criteria row. Press enter. Run Enter Swanson in the enter parameter value dialog Ok

Using compact and repair: Run the compact and repair tool

Click the database tools tab Click compact and repair database button

Enforcing Deletions and Updates in Relationships: Modify the relationship between locations and services tables so when a record in the locations table is updated related records, in the services table will also be updated Modify the relationship between the services and appointments table so when a record in the service table is deleted, related records the appointments table will also be deleted

Click the database tools tab Click the relationships button Double click the line connecting the Locations and Services tables Click the cascade update related fields check box Click ok

creating relationships: Open the relationships window Create a new relationship between the locationID field in the locations table and the location field, in the services table Enforce data integrity between the two fields

Click the database tools tab Click the relationships button Drag location ID to the location field in the services table Click enforce referential integrity button Click create

Understanding and viewing table relationships Open the relationship window Open the add tables task pane Show the services table Close the add tables task pane Close the relationships window, saving the layout changes

Click the database tools tab Click the relationships button Click the add tables button Double click the services table to add it to the relationships window Close the add tables task pane Close the relationships window Click yes

Modifying the field size property: Change the field size property for the state field to 2

Click the fields tab Type 2 in the field size box, and press enter Click yes

Creating a database from a template: Create a new database from the asset, tracking template, name the database assets

Click the file tab Click new Click the asset tracking icon Type assets in the file name box Click the create button

Previewing and printing database objects: Preview how the appointments by customer query would look if it were printed Change the orientation to landscape Change the view to show two pages at once Close, print preview without closing the table

Click the file tab Click print Click the print preview button Click the landscape button Click the two pages button Click the close print preview button

Backing up a database: Save a copy of the database to use as a back up, accept the file name, suggested by access

Click the file tab Click save as Click back up database Click the save as button Click save

Creating a new blank database: Create a new blank, database named contacts

Click the file tab Click the blank database icon Type contacts in the file name box Click create

Renaming fields, and changing the field caption: Rename the ID field to supplierID Use the enter field properties dialog to rename the field1 field to CompanyName, and change. The caption to company name do not add a description

Click the table fields tab Click the name and caption button Type CompanyName in the name box Tape company name in the caption box Click OK

Adding fields in datasheet view: add a new Yes/no field to the far right side of the table. Name the field: favorite.

Click the table fields tab Click the yes/no button Type favorite as the new field name press enter

Changing data type: From datasheet view, change the data type for the price field to currency

Click the table fields tab Expand the data type list Select the currency option Click yes

Formatting fields in a table: From datasheet view apply the short date format to the appointment date field Switch design view From design view apply the medium time format to the appointment time field

Click the table fields tab Expand the format list Select the short date option Click the view button The appointment time field has been selected, click in the format property box Click the arrow at the right end of the format, property box Select the medium time option

Applying a theme: Apply the integral theme to the support do not change the view

Click the themes button Select the integral theme

Organizing objects in the navigation pane: Change the navigation pane grouping option to object type

Click the top of the navigation pane Select the object type option

Adding totals to report: From Lauv, you add automatic totals to the price column to calculate the sum of prices

Click the totals button Select sum from the menu

Using undo and redo:

Click the undo button to undo the data entry

Previewing and printing a report: Switch to print preview view Change the view to show two pages at once

Click the view button Select print preview from the menu Click the two pages button

Switching between database object views: Switch to design view Switch to layout view

Click the view button arrow Click design view Click the layout button on the status bar

Exporting data to a text file: Export the appointment schedule query to a comma separated. Text file include field names in the export. You do not need to change the location of the saved file or save the export steps.

Select the AppointmentsSchedule query in the Navigation pane In the export group, click the text file, button Click ok Click next Click the include field names on first row check box Click next click finish Click close

Exporting data to excel: Exporting the AppointmentsSchedule query, to excel maintaining all data formatting and layouts. You do not need to change the location of the saved file or save the export steps

Select the AppointmentsSchedule query in the navigation pane In the Export group click the excel button The export data with formatting and layout check box Click ok Close

Moving fields in datasheet view: Move the favorite field, so it's the last field in the table

Select the favorite column Move favorite field to the last field in table

Deleting records: Delete appointments with appointment ID 342 it should be the second record in the table

Select the record with the Appointment ID 342 Click the delete button Click yes in the message that appears

Deleting Fields in Datasheet View: Delete the fax number

Click The table Fields tab Click the delete button Click Yes, in the message that appears

Filtering data using filter by selection: Locate a record where the value in The appointment date Field is 4/6/2023 and then use filter by selection to filter the query results to show only appointment dates on or after 4/6/2023

Click in the appointment date field for the first record where the appointment date field is 4/6/2023 Click the selection button Select on or after 4/6/2023

Combining AND and OR in a query: Add criteria to find appointments from April 1, 2023 through April 7, 2023 inclusive for any customer with the last name Swanson or where the location is salon run the query and review the results

Click in the criteria row under appointment date field Type the criteria >=4/1/23 AND <=4/7/23 in the criteria row press enter Click in the or field under the appointment date field Type the criteria >=4/1/23 AND <=4/7/23 in the OR row press enter Click in the criteria row under the last name field Type the criteria Swanson in the criteria row press, enter Click in the or row under the location field Type the criteria salon in the or row to complete the query criteria press enter click run

Using AND in a Query: Add the criteria to find appointments scheduled for April 1, 2023 or later where the location is salon run the query Return to design view The previous criteria has been removed. Add new criteria to find all appointments scheduled from April 1 2023 to April 7, 2023 include both April 1, 2023, and April 7, 2023 in the criteria run the query

Click in the criteria row under the appointment date field Type the criteria >=4/1/2023 in the criteria row press enter Click in the criteria row under the location field Type the criteria salon in the criteria row press enter run Click the view button to return to design view Click in the criteria row under the appointment date field Take the criteria >=4/1/2023 AND <=4/7/2023 in the criteria row. Press enter Run

Using OR in a query: Add criteria to find appointments for customers with the last name, Swanson, or Clauson run the query Return to design view Add new criteria to show all appointments for customers with the last name, Swanson or the location salon

Click in the criteria row under the last name field Type the criteria Swanson in the criteria. Press, enter Click in the or row under the last name field Type the criteria Clauson in the or row press enter Run Click the view button to return to design view Click in the or row under the location field Enter salon in the or row press enter Run

Adding fields to a form in layout view: display the field list pane. Add the appointment date filled from the appointments table to the form. Add the appointment time field from the appointments table to the form creating another layout column to the right of the appointment date control. add the last name and first name fields from the customer table so they appear below the appointment date controls hint click and drag the fields from the field list to control the placement. Add the service field from the appointments table, so it appears in the same column as the appointment time controls in the same row as a last name controls. Change the texting appointment date. Label to control to date. Change the texting appointment time label control to time.

Click the add existing fields button Double click the appointment date field Move the appointment time field to the right of the appointment date Click the plus in front of customers in the field list Include last name and first name field below the appointment date controls in the form In the field list, click service under appointments to select it Move service to the right under appointment time Double click the label control that displays the text appointment date Edit the label control text to just date instead. Press enter

Filtering data using auto filter: Use auto filter to filter. The query results to show only records where the last name is Bellows Remove the filter from the field

Click the arrow at the top of the last name column Click the select all checkbox Click the Bellows checkbox Click OK remove the filter from the field by selecting clear filter from the auto filter list

Working with attachment fields: Add an attachment field at the far right of the table Add the JPG image file, Bernice, James, to the customer record

Click the arrow in the column for click to add Header at the far right side of the table Select the attachment option Double click the attachment field in the first record Select photo bernicejames.jpg Click open Click OK

Using the simple, query wizard: Use the simple query wizard to create a select query, showing the details for every record in the results include these fields in this order last name, and first name from the customers, table, an appointment date and appointment time from the appointments table select option to open the query to view information use the query title customer appointments

Click the create tab Click the Query wizard button Simple query wizard selected by default. Click OK Expand the tables/queries list Select table: customers Double click the last name field to add it to the query Repeat with first name Appointment date and appointment time Next The detail shows every field of every record radio button is selected by default. Click next. Text customer appointments as the new name for the query press enter Finish

Using Quickstart application parts: Add database objects from the contacts quick start application part

Click the create tab Click the application parts button Click contacts

Creating a new blank report in layout view: Create a new blank report, so it opens directly in layout view

Click the create tab Click the blank report button

Create a single record form based on the table or query: Create a single record from the appointments table

Click the create tab Click the form button

Creating a multiple items form: create a multiple items form from the services table

Click the create tab Click the more forms button Select multiple items

Creating a split form: Create a split form from the services table

Click the create tab Click the more forms button Select split form

Creating a query in design view: Begin a new query in design view and add these tables, services, appointments, and customers Add these fields to the query grid in this order State from the customers table appointment, date from the appointments, table and service name and price from the services table Run

Click the create tab Click the query design button Double click the services table to include it in the query Click the X in the upper right corner to close the task pane Double click, the state field to include it in the query Quick run

Finding Unmatched Data Using a Query: Create a new query using the unmatched query wizard to find records in the customers table without corresponding records, in the appointments table include the customer ID first name, and last name fields from the customers table in that order accept the suggested name for the query

Click the create tab Click the query wizard button Select find unmatched query wizard Click OK Select table: customers Click next Table: appointments is already selected click next Click next Double click the customer ID field Double click additional fields Click next Click finish

Find the duplicate data using a query: Create a query using the find duplicates, query wizard to find appointments on the same day for the same customer. Choose the appointment schedule query to search for duplicate field values find duplicate values in the appointment date first name and last name fields include the appointment time service name and length fields to provide more information about the appointment, accept suggested name for the query.

Click the create tab Click the query wizard button Select find, duplicates, query wizard Click OK Click the queries radio button Select query: AppointmentsSchedule Click next Double click the appointment date field Double click remaining fields Click next Click next Click finish

Creating a basic report based on a table or query: Create a basic report from the services table

Click the create tab Click the report button

Creating a report using the report wizard: Start the report wizard Use report wizard to create a new report include the service name field from the services table, the appointment date field from the appointments, table and last name field from the customers table view the report data by the services table, create subgroups by the appointment, date field sort each subgroup by appointment date use the stepped layout and portrait orientation for the report. Name the report service monthly summary and preview it in print preview view

Click the create tab Click the report wizard button Double click the service name field Click the next button By services is already selected in the how do you want to view your data box click next Click the add button the first button in the middle with the > symbol Next Sort each sub group continue to next Type the title Finish

Creating a form using the form wizard: Start the form wizard Use the form wizard to create a new form include these fields in this order from the services table, service ID, service, name, price and length and then add these fields from the locations table location ID and location is the data by the locations table with related data from the services table as a sub form, use the data sheet layer options for the sub form name the main form locations with services in the subform services subform finish, creating the form, allowing access to open the form inform view

Click the form wizard button Expand the tables/queries list Select table: services Double click the service ID filled Expand the tables, queries list again Select table : locations Select fields Click next Select by locations in the how do you want to view your data box? The data sheet radio button is already selected as a layout for your subform click the next button Enter form and subform title Click finish

Formatting Controls: All the label controls in the form have been selected for you bold them With the label controls still selected, apply the dark blue font color Select only the appointment date bound, text box control, and apply the long date format Switch to the AppointmentsSchedule report Apply the comma number format to the price bound text box control

Click the format tab Click the bold button Click the font color button arrow Click the dark blue color from the font color palette The appointment date field has been selected, expand the number format list Select the long date format Switch to the AppointmentsSchedule report A value in the price column has been selected click the format tab Click the comma number format button

Grouping records in a report: From layout view display the group sort and total pane Add grouping by the appointment date field Change the grouping level for the appointment date field to by month

Click the group and sort button Click the add a group button Select appointment date from the list Click the by quarter button Select by month from the menu Click anywhere to the left of the menu in the group sore and total pain

Applying an Input Mask: Start the input mask wizard for the selected field in the phone field, select the phone number input mask format, and then go to the next step Do not change, default, format, or placeholder characters, store the data without the symbols and finish the wizard

Click the input mask box in the field properties pane Click the build button The phone number field is already selected click next Click next The second option is selected by default click next Click finish

Controlling the page set up of a report for printing: From the print preview view change the orientation so the report page is wider than it is tall Change the margins setting to the wide option

Click the landscape button Click the margins button Select the wide option

Navigating records:

Click the last record button Click the previous record button Click the first record button Click the next record button Take 22 in the current record box and press enter

Adding design elements to form and report headers: add the image spalogo25 to the form header as a logo. Do not switch views. Add a title to the form header and change the text to appointments. Press enter when you are finished. Switch to AppointmentsSchedule report. from layout view add the date and time to report Header using the date format similar to Monday, June 10, 2022, in the time format similar to 1:58 PM.

Click the logo button Select the image named SpaLogo25 Click OK Click the title button Change the title to appointments, and then press enter Switch to appointmentsschedule report Click the date and time button Click the radio button with the time format similar to 1:58 PM Click OK

Creating a new record in a form and entering data: Begin a new record Enter the following information in the form in order. Enter 8/23/2023 and time 1:00pm for the appointment. Select the customer mrs. Deborah Stetson and the service hair salon cut short from the lists for those fields. When you are done entering data in the last field to start a new blank record.

Click the new blank record button at the bottom of the form Type 8/23/2023 in the date box press tab to advance to the next field Type 1:00 PM in the time box press tab to advance the next field Click the drop-down arrow next to customer Select Mrs. Deborah Stetson Click the drop-down arrow next to service Select hair salon, cut short

Creating a new record in a table and entering data: Begin a new record Enter the customer ID BBA - 200 and advance to the next field Select ms. as the title from the look up list in advance the next field

Click the new blank record button to add a new record to the end of the table Type BBA-200 in the customer ID field and press enter Expand the look up list in the title field Select a ms. from the look up list

Adding page numbers to reports: Add automatic page numbers, centered at the bottom of the page footer on every page use the format page N of M

Click the page numbers button Select the page N of M radio button Click the bottom of page footer, radio button Click OK

Resizing controls: Use the property sheet to resize the service name column to exactly 1.5 inches wide. Do not switch views

Click the property sheet button on the design tab in the tools group Type 1.5 in the width the box. Press, enter

Hiding and Showing Fields in a Query: Hide the location field, so it does not appear in the query results run the query

Click the show box under the location field to remove the checkmark Click the run button

Inserting, deleting, and moving fields in design view: Use the current view insert an new field between productID and price fields Using the current view, name the field ProductName and set the data type to short text. Do not add a description for this field save the table. Using the current view in the locations table, delete the building field when you are done save the table Using the current view in the customers table, move the phone field, so it is immediately above the phone type field, save the table

Click the table design tab Click the insert rows button Name the new field, ProductName, and press tab Save the table The building row has been selected click the delete rows button Click yes Click and move the phone row

Setting the default value property: Set the default value property for the state field to CA

Click the table fields Tab Click the default value button Use CA as the default value for the field Ok

Using Quickstart to add related fields: Add the phone Quickstart fields to the end of the table

Click the table fields tab Click the More fields button Select phone in the Quickstart section of the list

Adding a look up field from another table: modify the customer field data type to use a look up list. The look up data will come from the customers table. Continue to the next step when you have selected the table. Include the customerID first name and last name fields in the look up list in that order. Sort the look up list by the last name field in alphabetical order. hide the key column as suggested. enforce data, integrity, and do not allow deletions in the customers table that would violate the integrity of records in the appointments table save the changes.

Expand the data type list for the customer field Select the look up wizard option The I want to lookup field to get the values from another table or query radio button is selected by default click next. Select table: customers Click next Select the fields you want from available fields Click next Specify sort order Next Next Click enable data integrity Finish Yes

Adding a Lookup Field from a List: Modify the PhoneType field to use a lookup list with values you enter. Enter the following values for the list home work mobile other. Limit the lookup list to values in the list.

Expand the data type list for the phone type field Select the look up wizard option Click the I will type in the values that I want radio button Click next Enter the values Click next Click the limit to list checkbox Click finish

Adjusting Table Column Widths: Modify the width of the customer ID column to exactly 12 Modify the width of the street column to best fit the data

In records group click the more button Click the field width Type 12 in the column width box Double click the right column border for the street field

Importing data from an access database: Import the departments table from the staff database, do not save the import steps

In the import and link group, click the new data source button Click from database Click access Click the browse button Select the staff database Click open Click OK Click departments Click OK Close

Linking to data in an access database: Create a linked table to link to the staff table in the staff database

In the import and link group, click the new data source button Click from database Click access Click the browse button Select the staff database Click the open button Click the link to the data source by creating a linked table radio button Click OK Click staff Click OK

Importing data from excel: Import data from the inventory, Excel file to a new table in the current database import all of the fields from the inventory worksheet. Use the first row as column headings do not change any field information. Use the item ID field as the primary key accept the new table inventory, do not save the import steps

In the import and link group, click the new data source button Click from file Click excel Click the browse button Click the inventory file Open OK Click next Click, the first row contains column headings Click next Click next Click the choose my own primary key radio button Click next Click finish Close

Importing Data from a Text File: Import data from the vendors comma-delimited text file to a new table in the current database import All of the fields from the vendors file use the first row as field names do not change any field information use the vendor ID field as the primary key accept the new table vendors, do not save the import. .

In the import and link group, click the new data source button Click from file Click text file Click the browse button Click the vendors file Click open Click OK Click next Click the first row contains field names check box Click next Click next Click the choose my own primary key radio button Next Click finish Close

Deleting and Renaming Database Objects: In the customers group, delete the query Appointments without Matching Customers In the customers group, rename the form Customers to: Master Customer Form

Right-click the query named Appointments Without Matching Customers Click delete Click yes in the message that appears Right-click the form named Customers Click rename Rename the form Master Customer Form and press Enter

Adding records to a table by importing: Import data from the purchase order details text file and append the records to the PO details table the data fields in a text file are separated by tabs. The first row in the text file is the header row. save the import steps, use the name suggested by access.

In the import and link group, click the new data source button Click from file Click text file Click the browse button Select the purchase order details file Click open Click the append a copy of the records to the table radio button Click the arrow to expand the list Select the POdetails table Click OK Click next Click the first row contains field names checkbox Click next Click finish Click the save import steps, checkbox Click the save import button

Sorting records in a data sheet: Sort the records by values in the last name field from A-to-Z Clear the sort Sort the records to the results of sorted by date with the newest date first and then by time within each date with the oldest time first

In the sort and filter group, click the ascending button Click the remove sort button The appointments table has been opened click the arrow at the top of the appointment time Column Click sort oldest to newest Click the arrow at the top of the appointment date column Click sort newest to oldest

Designing a table: Open the appointments table and explore the fields in the table Click a record in the field that uses the Autonumber data type Open the customers table and explore the fields in the table Close all open tables and open the relationships window to review the relationship between the Appointments table and Customers table

Open the appointments table Set the field that uses the autonumber Data type Open the Customers table Click the Database Tools tab Click the relationships button

Introduction to access: Open the appointments table Open the new appointment form Close the navigation pane Click the tab to move to the appointments table Close the appointments table Close the new appointment form Open the navigation pane

Open the appointments table The new appointment form has been opened close the navigation pane to make more room to work Move to the appointments table Close the appointments table Close the new appointment form Open the navigation pane again

moving and arranging controls: Select the service name column and then move it to place it between the appointment time and last name columns Switch to the appointmentdetails form Move the service label control and it's bound control down one row in the layout to the area to the right of the first name controls

The service name column has been selected click the arrange tab Click the select column button Move service name column Switch to appointment details form Move service control and its bound control

Finding and replacing data: Use the search box to begin searching for a customer with the first name beginning with Vivi Use the find and replace dialog to find the record for the text swanson matching the case exactly. Replace all instances of swanson with Swanson. Be sure to match the case exactly.

Type Vivi in the search box at the bottom of the datasheet Click the find button Type swanson in the find what box Click the match case checkbox Click the find next button Click the replace tab in the find and replace dialog Type Swanson in the replace with box Click the replace all button Click yes, in the message box