The data are in the two attached files. They are called AccountInfo and CustomerInfo. These are Excel files, but are only meant to get you the required information for the Access database project. These two files contain the information needed to create two tables in Access. These instructions explain how to do this. 1. Open Access 2007
2. Click on the “Office Button”; Select “New”
3. On the right of the screen, under “Blank Database”, enter a filename “MIS 300 Access Lab-Your Name”; substitute your name for “YourName”. 4. Click on “Create”
5. Create the table CustomerInfo.
To create a table:
If a table already exists,
right click on the table tab and choose “Design View”. Access will ask for a table name. Type in “CustomerInfo” and click “OK”.
If the table does not exist,
Click on “Create” on the Ribbon; then click on Table; View; Design View.
Access assumes the first field is the primary key field. This does not have to be the case, so right click on the “key” and click on “Primary Key”.
Input the field names:
Click on the first field and type in “CustomerID”; Use right arrow and choose Number”. Use the arrow keys and go to the next row under Field Name:
Last Name Text
First Name Text
Middle Initial Text
Right click on the square box next to the “CustomerID” field and choose “Primary Key”. Right click on the “CustomerInfo” tab and save the table. Right click on the “CustomerInfo” tab and close the table.
Use a similar process to create the table “AccountInfo”
AccountID Number Primary key
CustomerID Number Must be the same type as
“CustomerID” in “CustomerInfo”—This is a foreign key
Inputting the data:
Right click on the table and choose “Open” or double click on the table. Input the information given for the two tables.
Save and close both tables.
Click on “Database Tools” on the ribbon
Click on “Relationships”
Add both tables and close the popup window
Put the mouse on “CustomerID” in CustomerInfo and drag the mouse to “CustomerID” in “AccountInfo” A popup window will appear. Click on “Enforce Referential Integrity” and then click on “Create”. Save the relationship and close it.
Click on “Create” on the ribbon. Click on “More Forms”, then choose “Form Wizard”. Choose a table. Move all available fields to selected fields. Then click “Next”. Choose the type of form you want, then click “Next”. Choose the style you want, then click “Next”. The title should be “CustomerInfo”. Click “Finish”. You can view the current data and/or add new data. Save and close this form. Go through the same process for the other table.
A query should never return a blank result!!
These queries must be named and pull up the correct results: Query1 Who lives in Mississippi?
Query2 Who has a Checking balance of less than $1000?
Query3 Who has Savings accounts?
Query4 Who has a Credit Card accounts?
Query5 Who has a Checking account and opened the account on 1/1/1999 or after? Query6 Who has a Savings account or opened an account before 3/4/2000? Query7 Who opened a checking account or Credit Card account after 1/1/2008?
Click on the “Report Wizard” on the ribbon.
Choose the fields from on or both tables that are to be in the report. Choose the way to view the data, then click “Next”. Choose the grouping level, then click “Next” or “Finish”. The report should be named “Report1” and should have the following fields: Customer ID
How you set up the report otherwise is up to you. If you right click on the Report tab and click “Design View”, you will see the many different choices for a report. The report should be easily readable by a regular user and make sense to a regular user.
If you have any questions, then please ask me....