Lab 7 – Access Data Entry and Queries

1. Lab 7 – Access Data Entry and Queries MBAC 611

2. Lab 7 Preparation Click on the My Computer Icon Open your private network directory Create a new folder named lab7 Copy your lab6Access file to the lab7folder Rename the copied lab6Access file to lab7 Double Click on the lab7Access file

3. You should now be in Access. If you see the following warning you can select This warning is prompted by the fact that programs can be created and run from within Access. So when you don’t know where a database originated from it is a good idea to let Access block these programs (what is called active content).

4. Access Tables You should see the three Access table names on the left hand side of the screen. These are the three tables you created during your last lab.

5. Table Data Entry Double-click on the tab. This allows data to be entered in the fields.

6. We are going to enter the following data into the Customer table. Unfortunately, you cannot copy/paste whole records in Access. However, you copy/paste an individual field value – this may be useful for the Street_Address field. Enter the above data into the Customer table.

7. Once entered, we need to save the data. Right-click on the tab and then select . Right-click on the tab and then select .

8. Purchase Table We will now enter data into the Purchase Table. Double-click on the table tab. You should see the following empty table.

9. Enter the following data into the Purchase Table. You will need to enter one row at a time as the key consists of the first three fields. Key fields cannot be blank. In Access the TAB or ENTER key can be used to move to the next field. Save and Close the table when done.

10. Item Table We will now enter data into the Item Table. Double-click on the table tab. You should see the following empty table.

11. Enter the following data into the Item Table. Notice that Access automatically places the dollar sign next to entries in the Price field. This is due to the fact that we declared the Price field to be of type Currency. Save and Close the table when done.

12. Queries Access allows us to query the data using a graphical method called Query By Example (QBE) It also allows us to Query using SQL QBE entries are converted to SQL and then executed to return the answer.

13. Query 1 We will now create the query to list the first and last name of all customers who live in the state of “NJ”. The Customer table has the information we need. We need to query the state field.

14. Creating A Query Click on the tab. Click the icon. The following screen should appear:

15. From the Show Table dialog box select Customer and click Then select

16. We want to display the first and last name so lets add those fields to the query detail area near the bottom of the screen. Select and from the first and second column. We will also need the field so select that as well.

17. Query Criteria We are looking for individuals who live in New Jersey so lets place the value NJ into the Criteria section of the State field. Since NJ is a text value we need to place it in double quotes. If you forget to do so Access will add them for you when you move off the field.

18. Running The Query To run the query click the icon located near the upper left hand corner of the Excel screen. Our answer should appear in a New Table. The table is called Query 1.

19. Modifying the Query Once we have our answer we can still modify our query and then re-run it. Right-click on the tab and select Lets modify the query so that it doesn’t display the state field – we know it will be NJ. Click the check box in the State field.

20. Your query detail area should look like the following. Run the query by clicking on the icon. Notice that the State field is no longer displayed.

21. Viewing The SQL Code Right-click on the tab. Select the selection.

22. SQL Code SELECT Customer.First_Name, Customer.Last_Name FROM Customer WHERE (((Customer.State)="NJ")); The extra parenthesis are not necessary. They have probably been placed to accommodate future criteria or queries involving field names that include spaces.

23. Remove the extra parenthesis in the SQL code and re-run the query to verify that it works. The code should appear as follows: SELECT Customer.First_Name, Customer.Last_Name FROM Customer WHERE Customer.State="NJ";

24. Saving The Query Right-click on the tab. Select the selection.

25. Change the Query 1 value in the Query Name Dialog Box to Question 1. Notice the Query appears under the Queries listing:

26. Query Assignment Create a query that does the following: List the Item_ID and Price of the item named “Barbie”. Hint: This requires the Item table. The answer should be: Save the query under the name Question 2.

27. Submitting The Lab Quit Microsoft Access. You won’t be able to submit the file if the database is open. A lock is placed on the file until you quit Access. Submit the Lab7Access file to the Lab7Moodle Assignment.