Microsoft Access

1) Import the Transactions file to Access: a. Two field names are invalid (they have spaces). That’s OK …just change the names as you proceed (From Field1 to OrderNo and Field2 to OrderDate). b. Change the Quantity field to an Integer format. c. Change the Total field to a Currency format. d. Allow the system to add a Primary Key. 2) In Access, change the Date formatting: a. For the Transactions table, switch to Design View. b. The Data Type for the OrderDate field should be Date/Time. c. Down below, in the Field Properties, select Short Form. d. Switch back to Database View, and the dates should be reformatted. 3) Create a query (Query Design). Pull in all fields except the ID and OrderNo. a. Build a query that will present all Crime or Thriller sales for Paul Goodman and present them in ascending order. (It should be a list of 10 items.) b. Save the query as GoodmanCrimeThriller. c. Create a report of the information with Report Wizard: Include all fields, and click through to ‘finish.’ Do not worry about cleaning up the report. 4) Create a new query with the same fields. a. Add a Calculated Field that computes the average sale amount: i. AvgSale: [Total]/[Qty] 1. Note that the “:” is the key here …we can put any name on the field. b. Select Action or Comedy categories. c. Sort in ascending order for both Employee and Genre. d. Save the query as AvgSale. 5) Create a report from the AvgSale query using the Report Wizard and use the following steps: a. Include theGenre, Employee, and AvgSale fields (in that order). b. Add a Genre, then an Employee grouping. c. <next tab> For Summary Options, select an Avg summary option for Summary Only. d. Stepped layout is fine. e. This will create a report. Note that it’s hard to see and focus on the employee averages. f. In Design View, and in the Employee Footer: i. Right-click on the long box (“=Summary …’) and cut it out. ii. Click on the other two boxes, respectively, (Avg and =Avg..) and use the up arrow to raise them to the top of the section. iii. Right-click on the report title and return to Report View.