Homework Data Files For CI214 Access Course
Please read this. It's important. An Access database is a collection of tables that all fit inside of one file. In this course, the only database you will ever use for homework is the one you will create in the second week of class, called something like "CI214_[YourName].accdb". When you download and create new tables for homework assignments, they will be part of this one file, not a separate database file. If you don't follow these instructions carefully, you can wind up making a separate database file. That's always a mistake. All tables should go into the same database file.
Starting from this page, you can create and download dummy data for tables for various exercises. Please note that no two students will receive exactly the same data, although all the data will be similar. Don't turn in homework based on somebody else's homework data.
How to get and save this data: To get the data for an assignment, you will need this Web page, a text editor such as Microsoft Notepad (or any programmer's editor) and a copy of Access. You can find instructions in your text on pages 272-273, or do this:
1. Click the link below for the assignment you need to do.
| Link | Save under this name: | Create these tables: |
|---|---|---|
| Week 5: Plumb Bob Magic Product Customer Sale Orderline |
PB_Product.csv PB_Customer.csv PB_Sale.csv PB_Orderline.csv |
PB_Product PB_Customer PB_Sale PB_Orderline |
| Membership List: 500 Persons | [Your organization]_Members.csv |
2. You will see a page of text data like this:
Last Name, First Name, Email, Address, City, State, Zip Vigiland, Otto, otto@queryall.com, 656 Chasten St, Offertolia, WI, 56081 Delacosta, Fran, fran@dynamics.com, 477 Ralston Rd, Quainte, WI, 56083 Padawan, Sam, sam@zerofootprint.com, 327 Wall St, Xerxes, IA, 33574
3. Copy all of this text to the clipboard. You can use Ctrl-A/Ctrl-C, or draw your mouse across it and choose "Edit/Copy".
4. Paste the text into your text editor and save the file with the extension ".csv", like this: "PB_Customer.csv". "CSV" stands for "Comma-Separated Values" and means that this is a text file where the individual data items are separated by commas.
5. Now open your regular class database file using Access. In Access 2007, choose "External Data", then "Text File". This opens a dialog as shown here:
6. Choose "Import the Source File Into a New Table". The "Import Text Wizard" will open. On the first panel, check that the "Delimited" radio button is selected, and choose "Next".
7. On the next step of the wizard, be sure the "Comma" radio button is checked. IMPORTANT: You must check the "First Row Contains Field Names" box yourself.
8. The next step of the wizard is to look over each table column and see whether Access has correctly understood it. There are two common problems here you may need to fix: (1) Access thinks a column like "Zip Code" or "Telephone" is a number. You'll have to change these columns to text. (2) Access thinks a numeric column such as a price is a text field. You'll have to change these columns to "Integer", "Number" or "Currency". Click "Next" when done.
9. On the next step, you should check the box that lets Access add its own autonumber primary key, called "ID".
10. Finally, on the "Import to Table" screen, enter the name of your new table. The assignment will tell you what this table should be called. IMPORTANT: Don't let your table be named something like "Assignment4". You must give it a meaningful, simple name such as "Customer" or "Supplier".
