Access, A "Relational" Database
Note for Macintosh users: Microsoft Access is not available on Macs or any other Apple computer. Access has been around since 1991 but has only been available on Windows, unlike the other applications in the Microsoft Office Suite which were ported to Macintosh very early. So either you need to use a Windows machine on campus or ignore the database assignments. However, there are more than enough points available from other exercises.
CIT105P Rubrics Assignment 6-7-8: Database 1, 2, 3 Tables 10 Search Query 10 Report 10 Total 30
For a starter list of online video tutorials, see below (very bottom of this page)
Mr. Zajac, the owner of a small store in the village is converting his record keeping from index cards to computer. He has a box of index cards for each customer and another box of index cards for each item for sale in his store. Your job is to design the database to handle the information now stored on cards. Your analysis reveals a need for specific data entry forms and output reports. You also identify ways that sorting, filters, and queries can be used to help manage the data.
For this assignment you will create a database with tables needed to generate the reports and forms he wants.
1. Create a database called CornerStore with a table called Customers
Open Access and click on "Blank desktop database"
This will give you a new database with a single new table called, by default, "Table1." You will need to save it under a more meaningful name and to add fields (below).
As in the instructions and arrows on the image, click on the upper left triangle and ruler icon for "Design View" button.
This brings up the Table Name dialog. Type "Customers" in the text box then click the "OK" button.
Now you have a blank structure form in which you enter fieldnames and attributes.
For the table Customers, use the following field names and attributes:
Field Name Field Properties Customer Number AutoNumber First Name Short Text, Field Size = 20 Last Name Short Text, Field Size =50 Address Short Text, Field Size = 80 City Short Text, Field Size = 60 State Short Text, Field SIze = 30 Mail Code Short Text, Field Size = 15 Phone Number Short Text, Field Size=20
Note that the area to enter field attributes is at the bottom of the form. In this case we are only setting the field size attribute.
When done save (Control-S keystroke) the structure and click on the upper right "X" in the corner to close this design view.
When done your field structure should look like this. Type Control-S to save and click the "View" icon to start entering the information (see below)
At this point, after saving (step above) you will see your new table in spreadsheet view with field names along the column tops and a blank line to start entering data underneath. An asterisk (*) is at the left side of that new-record data line.
Customer number is unique for each customer record. It identifies a specific customer. In other tables we can use customer number when we want to refer to a specific customer. When other tables, such as records of invoices, use the customer number instead of a customer name, it is easy to keep records connected (related) regardless of updates to the customer record, such as name changes, spellings, addresses and so forth. The number remains the same.
Add the following data to the table Customers: Do this by typing into a field in the line with has the asterisk at the left. As soon as you start typing the text goes in and a new asterisk (new blank record) line appears below where you are typing. Do not bother typing anything into the "CustomerNumber" field. The index number here is generated automatically by the database. It is always unique. Start typing the FirstName, then the LastName and finally the PhoneNumber fields for all 5 records as shown below.
Type in the customer names and numbers above.
2 Add a table called Inventory to the database
Click on the "CREATE" tab then click on the "Table" icon.
This part shold be familiar. This step is the same as we had with the Customer table only this time we type "Merchandise" in the Table Name box.
For the table Merchandise, use the following field names and attributes:
Field Properties ItemID AutoNumber Item Short Text, Field Size = 80 Tags Short Text, Field Size = 60 Description Short Text, Field Size = 255
Short Text SupplierID Integer Sale Price Currency DateOfPurchase Date, Format=Short Date DateOfSale Date, Format=Short Date SKU Short Text, Field Size = 40
Filled in, the structure form should look like this.
For the field attributes look at the bottom panel. In this case we are setting a date field to the "Short Date" format.
Add the following data to the table Inventory:
ItemID Item Tags Description Cost Supplier Supplier ID Selling Price DateOfPurchase DateLastSale Sku OnHand 1 Nikon D7200 Body 7200, DSLR,APSC,24mb Nikon 24 mp APS-C DSLR Body $800.00 Nikon 123 $1300.00 1/1/2016 12/1/2016 NDSLR7200 15 2 Power Spec G313, Laptop PowerSpec HP laptop computer $600.00 hp 456 $999.99 10/1/2016 1/2/2017 hp112466 10 3 Alexa echo, alexa, dot Alexa Echo Dot $20.00 Amazon 4256 $49.99 12/12/2016 1/3/2017 ed927650 234 4 Printer epson,printer,wireless,expression,CD Epson Expression XP-430 $60.00 Epson America 272 $54.99 11/11/2015 4/4/2016 ep430exp 25 5 Cruiser Bike Schwinn, bicycle,cruiser Schwinn Mens Legacy 26 inch Cruiser Bike $100.00 Schwinn 383 $139.99 6/6/2016 1/4/2017 SCBi26u3028 35 6 LED Task Lamp LED LED Task Lamp for desk tops $10.00 Univac 8949 $19.99 8/15/2016 12/31/2016 LLED234v12 66 8 Hansel und Gretel - Both Nov 2009 Video DVD (NTSC) DVD, Video, Opera Hansel und Gretel Thursday and Friday Shows (19, 20 Nov 2009) for UMKC Conservatory Music and Dance - White Recital Hall, Performing Arts Center - both casts - 1.6 hours, in German, three acts. DVD - NTSC $20.00 KCD Info 234 $40.00 11/22/2009 11/22/2009 HUG_BOTHNOV2009ntscDVD 4 9 MOOL 2006 Video DVD DVD, Video, Dance Music Of Our Lives - Sunday, 15 October 2006 - Musical Review featuring the music and dance of the 30's and 40's, big Band and Swing - Directed by Billie Mahoney and presented by the Foundation on Aging $5.00 KCD Info 234 $20.00 11/23/2009 11/23/2009 MOOL2006DVD 4 10 Music Of Our Lives Video DVD 2004 DVD, Video, Dance Music Of Our Lives - Saturday and Sunday, 23/24 Oct 2004 - Musical Review featuring the music and dance of the 30's and 40's, big Band and Swing - Directed by Billie Mahoney and presented by the Foundation on Aging $5.00 KCD Info 234 $25.00 11/23/2009 11/23/2009 mool2004102xDVD 4 30 Auricon 16mm movie camera, sound camera movie camera 16mm Auricon 16mm sound-film camera. Auricon Cinevoice 16 mm Camera with 400-foot magazine Model CM 72-A, 20-60mm zoom lens. Serial number A6-79,649, formerly used by NBC. $1500.00 Helix 945 $2500.00 11/27/2009 11/27/2009 16mmAuri06 1 32 Actinograph - Early Densitometer from 1888 antiques, densitometer Antique HD (Hurter-Driffield) Actinograph (patented 1888) used to chart the density of photographic negatives. This is the source of all densitometry used today. $250.00 Helix 945 $350.00 11/27/2009 11/27/2009 HDActino123 1 33 Wild T4 Theodolite for Astronomic Surveys surveying instrument theodolite Classic T4 Theodolite made by Wild in Switzerland. Used in first-order class-1 astronomic surveys to determine latitude and longitude for positions. $4500.00 Wild 2391 $7500.00 11/27/2009 11/27/2009 WTHEO34512 3
It should come out looking like this, below.
Once you have finished all steps,submit the database file for CornerStore as an attachment in your email.
(Note: Save a copy of the database CornerStore. It will be used in the next two assignments.) This is assignment DB1.
Online Helpers - Just a place to get started
Microsoft Access Tutorials on YouTube (a starter list only)
Just Google for "Microsoft Access tutorial" for more (hint, there are tons of these)
Access - making a database - Access 2016
Tutorial for Beginners - Access 2016
Beginners MS Access 2007 Database Tutorial 1 - Introduction and Creating Database
Beginners MS Access 2007 Database Tutorial 2 - Table Filter, Sort & Introduction Forms
Beginners MS Access 2007 Database Tutorial 3 - How To Create Forms with Buttons and Charts
Beginners MS Access 2007 Database Tutorial 4 - Creating Queries
How to Create a Stock Management Database in Microsoft Access 2010
Same, without the distracting background music