How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Herrod Your Own Question
Steve Herrod
Steve Herrod, Master's Degree
Category: Writing Homework
Satisfied Customers: 1219
Experience:  Masters in Literature and a Bachelors Degree in French with Management
65126503
Type Your Writing Homework Question Here...
Steve Herrod is online now
A new question is answered every 9 seconds

I wanted to know if you can assist me with my Access

Customer Question

Hello,
I wanted to know if you can assist me with my Access 2010 Case problem and project.
Submitted: 2 years ago.
Category: Writing Homework
Customer: replied 2 years ago.
Which is due Thursday, May 21, 2015. The assignment also has an excel case problem as well.
Expert:  SusanAthena replied 2 years ago.
Thanks for requesting me! Please post the whole problem and I'll see if I can assist. You can post a document to a file sharing site like sendspace.com and put the sharing URL here.
Kind regards,
Susan
Customer: replied 2 years ago.
Thanks Susan. There are three problems and a project.
Data File needed for this Case Problem: Always.accdb
Always Connected Everyday Chris and Pat Dixon own and manage Always Connected
Everyday (ACE), a successful Internet service provider (ISP) in your area. ACE provides
Internet access to residential and business customers and offers a variety of access
plans, from dial-up and DSL to wireless. Within each type of service—dial-up, DSL, and
wireless—ACE offers low-cost plans with either slower access speeds or fewer capabilities and more expensive plans with either higher access speeds or greater service and
features.
To keep track of their business, Chris and Pat have developed the Always database. The
database has two tables: the tblAccessPlan table contains data about the plans they offer
commercial and residential customers, and the tblCustomer contains data about their
customers. The database also contains several queries, forms, and reports.
Chris and Pat want you to add two new tables to the database to keep track of their
business’s service calls. Also, they want you to define a many-to-many relationship
and to create several new queries. To help them with their requests, complete the
following steps:
1. Open the Always database located in the Access3\Case5 folder provided with your
Data Files.
2. Designate the Access3\Case5 folder as a trusted folder. (Note: Check with your
instructor before adding a new trusted location.)
3. Use your first and last names for the first record in the tblCustomer table.
4. Create the following tables:
a. The tblService table includes a unique service ID (AutoNumber data type), a service description, and a service rate (Currency data type).
b. The tblServiceCall table includes a unique service call ID (AutoNumber data
type), the customer account number, service ID, and service date.
5. Define a many-to-many relationship between the tblCustomer and tblService tables,
using the tblServiceCall table as the related table. Select the referential integrity
option and the cascade updates option for the relationships.
6. Design test data for the tblService and tblServiceCall tables, and then add the test
data to the tables. Your tblService table should contain at least 10 records, and your
tblServiceCall table should contain at least 12 records.
7. Create a make-table query based on the tblCustomer table, selecting the
CustomerAcctNum, CompanyName, FirstName, LastName, City, and AccessPlanID
fields from the table. Select those records with a City field value of Blade or Drayton.
Save the table as tblSelectedCustomer in the current database. Run the query, and
then switch to Design view.
8. Modify the make-table query to create an append query that selects those records
with a city field value of Brunson and appends the selected records to the
tblSelectedCustomer table. Run the query, and then close the query without saving it.
9. Create an update query to select all records in the tblSelectedCustomer table in
which the AccessPlanID field value equals 4, changing the AccessPlanID field value
to 5. Run the query, and then close the query without saving it.
10. Create a delete query that deletes all records from the tblSelectedCustomer table
in which the AccessPlanID field value equals 7. Run the query, and then close the
query without saving it. Open the tblSelectedCustomer table, resize all columns to
their best fit, and then save and close the table.
11. Create an outer join between the tblAccessPlan and tblCustomer tables, selecting all
records from the tblAccessPlan table and any matching records from the tblCustomer
table. Display the AccessPlanID, AccessPlan, and PlanMonthlyCost fields from the
tblAccessPlan table, and the CompanyName, FirstName, and LastName fields from
the tblCustomer table. Save the query as qryPlanCustomerOuterJoin, and then run
and close the query.
12. Add an index that allows duplicates named Zip to the tblCustomer table, delete the
UserID index, and then save and close the table.
13. Make a backup copy of the database, compact and repair the Always database, and
then close the database.
Customer: replied 2 years ago.
Case Problem 5
Data File needed for this Case Problem: Always.accdb (cont. from Tutorial 9)
Always Connected Everyday Chris and Pat Dixon want you to create a user interface for
the Always database. To help them with their request, complete the following steps:
1. Open the Always database located in the Access3\Case5 folder provided with your
Data Files.
2. Design and create a form named frmQueries that has the following components and
characteristics:
a. Use the text Always Queries on the form’s tab.
b. Add a list box with a Name property value of lstQueryList that displays all the
query names contained in the Always database, excluding those queries that start
with a “~” character. To place the query names in the list box, use an SQL SELECT
statement to retrieve the query names from the MSysObjects table, and display
the queries in alphabetical order. Delete the label attached to the list box, and
widen the list box to approximately 2.25 inches.
c. Add the label Choose a query, formatted with an 12-point, bold font, above the
list box.
d. Add two command buttons below the list box. The left command button displays the Preview icon below the word Preview, and the right command button
displays the MS Access Query icon below the word Display. Double-clicking
a query name has the same effect as selecting a query name in the list box and
clicking the right command button. Both events cause Access to display the query
datasheet for the selected query. Clicking the left command button opens the
selected query in Print Preview.
e. Create macros for the form (when the form loads, move the focus to the first
query name in the list box), the list box (when a user double-clicks a query
name), and both command buttons (when a user clicks a command button).
f. Set the background color of the Detail section to Purple 1 in the Standard Colors
section and the background color of the list box to Maroon 1 in the Standard
Colors section, and then disable the form’s shortcut menu, record selectors, and
navigation buttons.
g. Test the form.
3. Create a navigation form named frmNavigation, using the Horizontal Tabs, 2 Levels
layout, that includes the following tab names and objects:
a. Use Forms as the name for the left tab, and place the following forms below it, in
order: frmCustomer, frmAccessPlans, and frmAccessPlansAndCustomers.
b. Use Queries as the name for the second tab, and place the frmQueries form
below it.
c. Use Reports as the name for the third tab, and place the rptAccessPlanCustomers
report below it.
d. Delete the navigation form’s picture and title, and change the tab name to Always
database.
e. Test the navigation form.
4. Make a backup copy of the database, compact and repair the Always database, and
then close the database.
Customer: replied 2 years ago.
Case Problem 3
Data Files needed for this Case Problem: Confections.accdb, Confections.xlsx
Kate’s Confections Kate Amundsen is the owner of Kate’s Confections, an online company that sells delicious candies and chocolates. Kate has been storing orders in an
Access database and wants to import a subset of its data into an Excel worksheet. The
database, named Confections.accdb, contains five tables. The Customers table lists the
names and contact information for customers who have ordered during the past several
weeks. The Orders table lists each order and the date it was submitted. The Products
table lists products sold by Kate’s Confections. The Customers_Orders table matches
each order with the customer who ordered it. Finally, the Orders_Products table matches
each order with the products on the order. You’ll import the data from the Customers
and Products tables into separate worksheets. You’ll also create a PivotTable that displays
details on each order. Complete the following:
1. Open the Confections workbook located in the Excel11\Case3 folder included
with your Data Files, and then save the workbook as Kate’s Confections in the same
folder. In the Documentation worksheet, enter your name in cell B3 and the date in
cell B4.
2. Insert a new worksheet named Customers at the end of the workbook. In cell A1,
enter Kate’s Confections and format the text with the Title cell style. In cell A2, enter
Customer List and format the text with the Heading 4 cell style.
3. Create a connection to the Customers table of the Confections database located in
the Excel11\Case3 folder. Place the data as an Excel table starting in cell A4 of the
Customers worksheet.
4. Edit the properties of the connection you just created. Name the connection
Customer List and add the description Data retrieved from the Customers table
in the Confections database. Refresh the connection whenever the workbook is
opened.
5. Insert a worksheet named Products at the end of the workbook. Enter Kate’s
Confections in cell A1 and Product List in cell A2. Format cells A1 and A2 to match
the title and subtitle in the Customers worksheet.
6. Establish a connection to the Products table of the Confections database located in
the Excel11\Case3 folder, importing the data as an Excel table starting in cell A4.
Format the Price values in column C using the Currency format.
7. Edit the properties of the connection, naming the connection Product List and entering the description Data retrieved from the Products table in the Confections database. Refresh the connection whenever the workbook is opened.
8. Insert a worksheet named Product Orders at the end of the workbook. Enter Kate’s
Confections in cell A1 and Product Orders in cell A2. Format cells A1 and A2 to
match the titles and subtitles in the previous two worksheets.
9. Use the Query Wizard to create a query based on the tables in the Confections database. The query should extract the following fields:
• Name from the Customers table
• CID and OID from the Customers_Orders table
• Date from the Orders table
• PID from the Orders_Products table
• Product and Price from the Products table
10. Save the query as a permanent file named Order Query in the Excel11\Case3 folder.
11. Import the data from the query into cell A5 of the Product Orders sheet as a
PivotTable Report.
12. Place the Name, Date, and Product fields as row labels in the PivotTable. Display
the sum of the Price field in the Values section of the table. Format the Sum of Price
values using the Currency format.
13. Name this connection Product Orders with the description Retrieves product orders
from Kate’s Confections customers recorded in the Confections database. Refresh
the connection whenever the workbook opens.
14. Kate wants to save the connections you’ve created as Office Data Connection (ODC)
files for use in other projects. To save the connections as permanent files, open the
Connection Properties dialog box for each of the three connections you’ve created,
and then click the Export Connection File button on the Definition tab. Save the
ODC files as Customer List, Product List, and Product Orders in the Excel11\Case3
folder.
Customer: replied 2 years ago.
15. Test the connection files you created in Step 14. Open a new blank workbook, click
the Connections button in the Connections group on the Data tab, and then click
the Add button. Click the Browse for More button in the Existing Connections dialog
box, and then locate and open the three connection files you created in Step 14.
16. Click the Existing Connections button in the Get External Data group on the Data
tab, and then verify that the three connections you added to this workbook are listed.
Select the Customer List connection and click the Open button. Confirm that you
can get external data from this connection and place that data into your workbook
as an Excel table. Close the workbook without saving; you do not need to submit this
workbook to your instructor.
17. Submit the completed files to your instructor, either in printed or electronic form, as
requested.
Customer: replied 2 years ago.
http://www.sendspace.com/filegroup/3E%2F7XIqxNxq%2BPeCjyRDuz66KoH8IZ8utu2m6BpkEFoUlZHKJlKmmsg
Customer: replied 2 years ago.
Susan,I submitted the assignments and the project. Please let me know if you need anything else. Any documents required for the assignments are in the url. :)I hope you can help!!Thanks!
Expert:  SusanAthena replied 2 years ago.
Thanks for that! I think it's more than I can take on, but I'll opt out. Hopefully another expert can assist.
Kind regards,
Susan