attached 4 projects
iamnickok
project_2.docx· Topic of this lab is Microsoft Office Access and database (DB) management. Procedure
1. Start Microsoft Office Access and create Blank Database. Type DB name (CS102_Ex7_YourUsername.accdb) and browse for a location. Click Create. You start with a new table. Save it as Customers.
2. Double-click ID field name and change it to Customer ID. Click a drop-down arrow next to Click to Add, select Text and type Street. Press Enter and add more fields:
· Field name Data Type
· City Text
· State Text
· Zip Text
· Department Text
· First Order Date/Time
· YTD Orders Currency
· Top Customer Calculated Field – Yes/No
3. A Top Customer field should become true (Yes) if value in the YTD Orders field is greater than 10000.
4. Delete Department field and insert Customer field (Text) between Customer ID and Street. Right-click a field to perform any manipulations.
5. Save your DB and switch to Design View (View button in the Views group of the Home tab).
6. Change descriptions of fields Customer ID (make it Unique number assigned with the first order) and Top Customer (Yes, if YTD Orders exceed $10K).
7. Click State field and change Field Size to 2 in the Field Properties.
8. Save your DB and switch to Datasheet View.
9. Add 3 fictional customers from different states. Add yourself as one of the customers. Note that you cannot modify Customer ID and Top Customer fields, they are generated automatically.
10. Save your DB and close Customers table.
11. Switch to Create tab and click Query Wizard button.
12. Click OK to use a Simple Query Wizard and dismiss any security warnings.
13. Move Customer, City, First Order, and Top Customer fields to the right panel (Selected Fields). Click Next, select Detail query, click Next again. Select Modify the query design and click Finish.
14. Click the first empty cell after the Top Customer column in the bottom of the screen and change Field value to State. Type name of the state you saved for yourself in the Criteria row and hit Enter.
15. Press Run button in the Results group and see some records in the resulting table.
16. Create a table in MS Excel with the following columns: Customer, Street, City, State, Zip, First Order, and YTD Orders. Change format of the First Order column to Date and YTD Orders to Currency/Accounting.
17. Open the Excel template ( CS102_Access1_Ex_Template.xlsx ), add three fictional records to the table and save the file as IT101_Access_Ex_Data.xlsx.
18. Switch to the External Data tab inside MS Access and click Excel in the Import & Link group. Browse to the data file and opt to Append a copy of the records to the table Customers. You can go through all the import steps or click the Finish button whenever it becomes available. You may have to close your table.
19. You can sort your table by multiple values, but the last applied sorting will have the highest priority (level, in Excel terms). Try sorting your table by First Order date and then by State.
20. Create a Crosstab Query to display orders submitted by customers by state.
21. Click Query Wizard button in the Queries group of the Create tab and select Crosstab Query Wizard.
22. Select table Customers and click Next.
23. Select Top Customer field and move it to the Selected Fields list. Click Next.
24. Select State field as column heading and click Next.
25. Select YTD Orders field and Sum function to calculate orders submitted by customers.
26. Click Next, then Finish to see the result.
27. Create and run a query that displays name and amount of YTD Orders of all Top Customers (value in Top Customer field is Yes)
28. Save your work.
Results
. A table with specified fields
. All fields are properly formatted (text, date/time, currency, calculated yes/no)
. 6 records (after import)
. A query that returns customer name, city, first order date, and top status
. A query that returns customer names and YTD orders of all top customers
. Save your file and submit it to the appropriate area.
References
