View the step-by-step solution to:

Question

I need assistance with answering the questions on the Access Exercise (Week 2 only).  Also please save any

changes made to the Access Database.  I listed some  tutorials just in case.

http://www.gcflearnfree.org/office2010/access2010

http://office.microsoft.com/en-us/access-help/design-and-build-tables-for-a-database-access-basics-part-1-RZ104119996.aspx?

 Access Exercises_4.xlsx

List the names of 3 tables
List the names of 3 queries
List the names of 3 forms
List the names of the 2 reports.
Open the Orders table in datasheet view (Note: Datasheet view is the default view)
How many orders were placed by the Customer with an ID of 44?
What dates were the orders picked up?
Change to design view for the Orders table. (Note: Make sure all other tabs are closed)
What data type is the Notes field?
What type and how much data can be entered in the Notes field?
What is the primary key for this table?
How did you identify the prmary key?
What data type is the Customer ID field?
What data type is the Paid field?
What type of data can be entered into the Paid field?
What is the data type of the Pickup Date field?
Change back to data sheet view for the Orders table.
Try to change the data value in the Paid field for one of the records to OK.
What happens and why?
Try to change the data value in the Customer ID field for for Customer ID 44 to 44a.
What happens and why?
Close the Orders table.
Open the Products form. for Categories and list all the current
Click the dropdown arrow
Categories.
Close the Products form
Open the Categories table in design view
Add a new record to the table called Muffins
Close the Categories table.
Open the Products Form
Click the dropdown arrow for Categories and verify the Muffins has been
to the list. What is the relationship between the Products form and the
Categories table?
Close the Products form.
What is the name of Customer with ID 44?
How did you find this name?
Create a new table called Employees with three fields...
Last Name (text), First Name (text), Wage (currency)
Add 3 records as follows:
Adams Mary
$12.50
Baker
Charlie $18.00
Carter Sam
$7.50
Note: Make sure the data fields are assigned the correct data type
Save the table with the name Employees
Close and save the database with the file name Week2_lastname_Access.accdb. Start with last week’s MG3042 Access Database
(Week2_lastname_Access.accdb)
Open the Relationships Tool
What is the relationship between the Menu Items table and Order Items tables?
Which table is the parent and which is the child?
Which fields are linked in this relationship?
Explain what this relationship means related to the relationship between the
records in each table?
What is the relationship between the Menu Items table and Sales Unit tables?
Which table is the parent and which is the child?
Which fields are linked in this relationship?
Explain what this relationship means related to the relationship between the
records in each table?
Open the Edit Relationship dialogue for the relationship between the
Menu Items and the order items table.
Is referential integrity enforced?
What does referential integrity imply about adding an order record to the Order
Items table?
If referential integrity was not enforced an orphaned record could be created by
deleting a record in one of the tables. From which table would a deletion cause
an orphaned record and which table would have the orphaned record. Why?
Create a new table called Work Shifts with three fields...
Shift Name (text), Start Time (ate/Time), Stop Time (Date/Time)
Add 4 records as follows:
FT1 5:00AM 1:00 PM
FT2 8:00 Am 5:00 PM
PT1 8:00AM 1:00 PM
PT2 12:00 PM 5:00 PM
Make the Work Shifts field the primary key for the table. (Note: You
need to use Design View to do this.)
Save and Close this table
Open the Employees table from last week. Add a new field called
Assigned Shift (Text).
Enter data in the Assigned Shifts field as follows
Mary Adams – FT2
Charlie Baker – FT1
Sam Cater – PT2
Save and Close this table.
Go back to the Table Relationships Tool
Add the Employees Table and the Work Shifts table to the view
Link the tables on the Shift Name and Assigned Shift fields.
Explain the relationship that Access created as the default.
Open the Edit Relationship dialogue for the relationship between the
Employees and Work Shifts table.
Enforce Referential Integrity if it is not enforced by default.
Enable Cascading Updates.
Close the Edit Relationships dialogue.
Open the Employees Table in datasheet view.
Try to change the Assigned Shift for Sam Cater to PT3. What happens and
why?
Open the Work Shifts Table in datasheet view.
Change the Shift Name for shift PT2 to PT4.
Save the table.
Go back to the Employees table
What is the name of the Assigned Shift for Sam Carter? Explain.
Save all open tables and layouts. the file name
Close and save the database with
Week3_lastname_Access.accdb. Start with last week’s MG3042 Access Database (Week3_lastname_Access.accdb)
(Note: The Orders Query returns all the orders entered in the database for Songbird Bakery)
Open the Orders Table in datasheet view
Open the Order Query in datasheet view
What is different between these two datasheets?
How many records are included in each datasheet?
Change to design view for the Orders Query
How many tables does this query look at to get the required data?
Which ones are they?
The query displays 6 fields. Which ones come from which table?
Since these two tables used in this query are not linked a join is
necessary to
query the data from both tables. Which fields are joined between these
two
Open the JOIN Properties dialogue for this JOIN.
tables?
What type of JOIN exists between these tables?
Explain what this JOIN accomplishes when the query is run.
A source of data for any query can be one or more tables and can also can be another query.
In the last step we examined the Order Query which used two tables as its source of data.
Now we look at a query that uses the Orders Query as its data source.
Open the Decembers Orders Query in datasheet view.
In what month(s) were all the order records returned by this query picked
up? many records are included in the datasheet?
How
Change to design view for the December Orders query.
What is the source(s) for this query? How do you know?
Does this query sort the records in any way? How do you know?
Does this query use any criteria to limit the number of records? If so what
is
the criteria? (Note: You may have to expand the field column width to see the full
criteria expression.)
Still in design view for the December Orders query
Change the sort criteria from sorting on the Pickup Date to Sorting on the Last Name field
Run the query (Note: Click on the run button in the ribbon.)
How do the record appear to sorted?
Change back to design view for the December Order query
Change the criteria to only return records picked up in March of 2011 and run the query
How many records are in the recordset?
Change back to design view for the December Order query.
Add criteria to return only unpaid records picked up in March of 2011 and run the query
(Note: Remember Yes/No fields are actually number fields where 0 is NO and any non-zero value is YES.)
How many records are in the recordset?
Save the December Orders query.
Use the Query Wizard to create a query from the Employees and Work Shift tables that shows the following fields.
Last Name, Start Time, Stop Time
Preview the query’s datasheet view to verify results
Save the query as Work Schedule
Save all open tables and layouts
Close and save the database with the file name Week4_lastname_Access.accdb. Start with last week’s MG3042 Access Database (Week4_lastname_Access.accdb)
Open the Customers form and change to design view
Add the Corporate Account field from the Customers table to the form.
(Note: use the ‘Add Existing Fields’ button in the ribbon.)
Save the Customers form and return to form view
What control is used to represent the new Corporate Account field on the
form.
Using the Customer Form in form view, check the Corporate Account checkbox for the following customers –
Newkirk, Jameson and Whitman.
(Note: Be sure to save the record for each customer changed.)
Open the Customers table in datasheet view.
Verify the Corporate Account field is set appropriately for the 3 customers
above.
Open the December Orders report.
Change to design view and add the zip code field to the report.
Note: Working with forms and reports in MS Access is detailed and tedious. Here are some tips to complete this
task.
• Add the form from the “add Existing Fields” button.
• Delete the label filed that gets inserted with it
• Position the Zip Code field in the detail section of the form.
• Use the format painter (Home Tab Clipboard Section) to copy the format from any of the existing fields to the
Zip Code field.
• Add a label (not a text box) in the Page Header section above the new Zip Code field.
• Add the text Zip Code in the text box and use the Format Painter to copy the format from any of the existing text
boxes in the
Save the December Orders report and return to Report View
Page Header section.
Verify the Zip Code is now included in the report
Use the Report Wizard to create an Employee Time Schedule report based on your Work Schedule query created las
Preview the report in report view to verify results.
Open the Employee report in design view and change the report header to Employee Time Schedule.
Change back to report view.
Preview the report in report view to verify results
Save all openStart Time, Stop Time
Last Name, tables and layouts. It is also possible and quite common to export Access tables and/or queries into Excel for further analysis.
Open Excel and create a new workbook
Use the ‘From Access’ import feature to import the December Orders query recordset from the
Week5_lastname_Access.accdb into an Excel worksheet. Import as a table into an existing worksheet and only
refresh when opening the worksheet.
How many rows are in the spreadsheet?
Save the Worksheet as MG3042_lastname_Excel.xlsx and close the worksheet.
Open MS Access and the Week5_lastname_Access.accdb database file.
Open the December Orders query in design view and remove the criteria that limits the recordset to unpaid orders
only. Verify the query performs as expected. Save and close the query, the database and MS Access
Open the worksheet you created in the previous steps.
How many rows are in the spreadsheet?
Note: If the results are not as expected, make sure Data Connections are
enabled (yellow button above the worksheet grid if visible) and if
necessary click the refresh button in the Data > Connections section of the
ribbon.
Close and save the workbook

AccessDatabase-2.accdb
Sign up to view the entire interaction

Top Answer

View the full answer
Access_Excersie.xlsx

List the names of 3 tables
Categories, Customers, and Menu Items
List the names of 3 queries
Cakes & Pies Sold, Customers that Ordered, and December Orders
List the names of 3 forms
Customers,...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online