Create a Set of Tables, then import data
Download Files - for import into tablesThese four files become the main content for your project database. Right-Click on the link and save the file to your drive. Then import the data to your project database - after creating tables for the data. Customers.txt - text file to import into your Members tableEmployees.txt - text file to import into your Employees table Invoices.txt - text file to import into your Invoices table InvoiceDetails07.txt - text file to import into your InvoiceDetails table Products.txt - text file to import into your Products table |
Course Book:
Access 2007 For Dummies (or later)
All-In-One Desk Reference
Eight books in one - Each with its own set of chapters.
This book is not required but it is inexpensive and provides a valuable and very complete reference.
Tables From Scratch
| Exercise | FOLLOW THE STEPS BELOW : one-by-one - just like a workbook Creating a table creates the holder for data which is a basic building block to progressively build a final project. When you have all the components of your final project you will email it to me as an attachment. |
| A database is useless without the information (data) contained in tables. |
When we create a table we are creating a holder for a certain set of information that we wish to group together. For example we may want to create on table to hold names and addresses of customers and another table to hold invoices for those customers.
Creating tables
When you create a database a blank table is created by default and is given the name Table1.
You can also use the create toolbar by clicking the "Table" icon in the "Tables" section.
In either case you will have a default table to start.
We are going to use "Design View" to create our tables. In the database dialog double-click on the "Create table in Design View" text option (make sure the "Tables" object is clicked, see above illustration).
Now you can start entering fields into your still-blank table. This is called creating the "structure" of the table.
Field Names and Data Types
Here is the list of fields we will start with. The field name is on the left, then the field type which may be followed by a default value. The first four fields are often missed but become a big help later, when someone needs to maintain the database or when a programmer, using this database, needs pointers to use in selecting records.
x
Customer Table
Fields to enter for the Customer tableNote: in the earlier (Access 2003) versions of this lesson this table is called "Members" If you still see a "Members" table mentioned, just translate Members to "Customers" - thank you.
Download file to fill in data is : Customers.txt - Right-click on this link and save the file, then import the data
Field Name Data Type default value id autoNumber This name will be supplied for us by Access. It is a number which provides a unique identifier for each record. gid text This is a "Global IDentifier" - used to create a unique identifier in a distributed database (different from the autonumber field above which is unique only within each table) The GID (Global IDentifier) needs to be created with a program or with a SQL function (not available in Access) Created date Date() For this click in the "field properties" section at the bottom and then click in the "Default Value" line. Enter "Date()" This is a function which enters the current system date into the record. Updated date Date() CustomerID number NOTE: This field is designed to be the common-data item which matches each customer to each invoice. You will have to enter the right number values yourself (1,2,3 ...) to make the four-table query work. FirstName text LastName text Address1 text Address2 text City text StateProv text MailCode text Phone text text
The illustration below shows typical entry of new fieldnames into a new table
When your are done your fields will look like the illustration below.
Entry is simple. Click a text entry area in the "Field Name" column and start typing. Although spaces in a field name are legal, if you have any ideas about using this database on the internet, don't use spaces in the name. Your programmers will say nasty things at you between their clenched teeth behind your back. Spaces in filenames and in fieldnames can cause transport problems from server to client and back to server.
Once you have typed the field name simply hit the "Enter" button and the cursor will move to the next column, the data type. Here you can either click on the pull-down menu in the text area to select the data type (such as autonumber, date, text, memo, number) or you can (easier and faster) just type the first letter of the data type ("a" or "d" or "t" or "m" or "n"). Then click "Enter" again and you will be in the comments column. This is to let you write notes to yourself about the field's usage.
As obvious or as memorable as you may think the new field is at the moment, put in some sort of comment. Later you - and anyone who follows you - will be glad you commented your work. Commenting (documenting) is one of the most neglected and yet most important things you need to do in computer work. Separate documents are good too, but they are often not used later, or get forgotten or misplaced. So the brief ("inline") documentation known as "comments" are extremely important because these are encountered in the process of doing the work.
Our New Table
It should wind up looking like this.
In this view the "Field Properties" panel at the bottom shows properties for the date field. In this case we are setting the default value to "Date()" We chose to do this because we want to know when each individual record was created. The updated field is used by a program to indicate the last time someone edited any of the information in the record. This field can also be edited manually but usually isn't.
You will have noticed that the "TEXT" data type is the default type. You get this automatically. You will also have noticed that all text fields are also given a field size of 255 characters. The old default size was 50 characters. I still prefer to set the default size at 50 characters to avoid wasting tons of space. You can define the default field size in the Options Dialog, the Object Designer Panel.
This is less than you need for some items but a lot more than you need for many others. We will change this later. In addition, note the "Allow Zero Length" property in the "General" tab at the bottom. In the latest versions of Access this defaults to "yes." In earlier versions this was "no" and caused any number of needless headaches (mostly for programmers who write SQL updates and inserts). Take my word for it. You almost, almost, almost, always, always, always want this to be "YES," "YES," YES!" (Cha Cha Cha!)
Here is a panel showing field properties for a number field. In this example I've pulled down the datatype list for the various number types. You might wonder why you even have to bother with this. After all, isn't a number just a number? Yes and yes and no. Why bother? Because databases still expect you to have to mess with some real under-the-hood design matters.
The different types of numbers each come with different types and amounts of storage space needed to handle the values. Singles and Doubles are numbers with fractional (decimal) parts. Integers are whole numbers only. Long integers are whole numbers with larger values (require more space to store). A Byte is the smallest number to hold (eight bits = one byte) and it can have any of 256 values. The 256 comes from the number of on/off (=0 or =1) patterns which can be made using the eight bits, some having a value of zero and some having a value of 1.
If that stuff makes your head hurt, don't worry too much. It isn't something we are going to cover here in any depth. It would 1) be a distraction and 2) is really here to let you know that there are reasons that you will need to make some choices in number types. Just so it doesn't seem too arbitrary.
Import Customer Data
- We need to add data - for the first record enter your own name and data
- Remember, the CustomerID field is a number starting at 1 and incrementing for each record
- Then, to fill out the table you are to import data from a CSV file to fill the table.
- Download this file Customers.txt (right-click then "save target as")
- Import it into your new Customer Table
- Remember, the CustomerID field will need to be filled in for queries to work. I've deliberately left out the customerID field information in order for you to fill it in. Should be a number starting at one and incrementing by one. It will match the same number in the invoice table which we will import later. (don't use fancy numbers, just 1, 2, 3, 4, and so forth)
- If you need a reminder look at Importing Data to Existing Table
Entering Data Using the Datasheet View
Now we need to enter some data. First (and always) hit the control-s key combination (Ctrl-S) to save your work. Then (new wrinkle) use the view icon to switch to "datasheet" view.
Now you have a data-entry format in rows and columns (at this point just one row because you haven't entered anything. And that one row is a "Blank Record" row. This is a buffer. As soon as you place the mouse cursor in one of the text-entry areas and start typing you will see that your entry row is now a record and another blank record row opens up underneath.
|
Starting: Blank Record only |
 |
| Before entering a record Access provides a starter line which is blank except for any default values you may have set | |
| As soon as you start typing |  |
As soon as you start typing you have an Access record. The ID number is added, the line opens to receive your data and a new blank link is positioned under the row you are typing in. Hit the "Enter" key to move to each next field. In the CustomerID field, enter a one (1). (see screen capture extract below) |
|
| A new record and ready for more |  |
When you reach the end of the row, one more hit on the "Enter" key places the highlight/cursor on the blank record, ready to go again, should you start typing. There is always a blank record waiting to be filled in at the bottom, after all the rows in the table. |
When done entering your own name (above), enter the first row of names in the table below.
We will use the second row a little later when we make a form for data entry.
|
|
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
UPDATE NOTE: The exercises have been modified to manual data entry for the main names which are important to the later queries. For additional import the eight names in the newer version of members.txt provide more data to use. The new names are Jim Dandy, Frances Laggard, Pie Rat, Carmine Redder, MyKul Anmee, Bantee Rooster, Rosco Lux and Red Sharpie.
NOW - The Other Tables in the set
(This is where this becomes a relational database)
We need 4 more tables in our POS database.
1 - Create the four database tables based on the field information in the HTML tables below
2 - Once created, fill these tables with information. To do this quickly first download the following four files using the links in each of the HTML tables with field information and then import each of those files to their proper tables.
- Invoice table - Download Invoices.txt
- Invoice line item (detail) table - Download InvoiceDetail.txt
- Employees table - Download Employees.txt
- Products table - Download Products.txt
The Invoice Table
|
Invoice Table (Header) |
||
| Download file to fill in data is : Invoices.txt - Right-click on this link and save the file, then import the data | ||
| Field Name | Data Type | Description |
| InvoiceID | autonumber | This field relates (connects) each invoice to each invoice-details (sales item) record (each item purchased) so that all the lines in the invoice can be listed when the invoice is viewed or printed |
| CustomerID | number | This field relates each invoice to each customer so the customer informatino can be listed every time the invoice is viewed or printed |
| EmployeeID | number | This field relates each invoice to each sales person so that the sales person can be listed every time the invoice is viewed or printed |
| DeliveryID | number | |
| Status | text | |
| InvoiceDate | Date | |
| ShipDate | Date | |
| ShippedTo | text | |
| ShippedVia | text | |
| ShippingCost | Currency | |
| Notes | Memo | |
The InvoiceDetails Table
|
InvoiceDetails Table - version for Access 2007 examples Line items for each invoice. One record for each item listed on the invoice |
||
| Download file to fill in data InvoiceDetails07.txt Right-click on this link and save the file, then import the data |
||
| Field Name | Data Type | Description |
| InvoiceDetailID | autonumber | |
| InvoiceID | number | This field contains the number of the invoice which relates this item to the invoice allowing this line to be shown whenever the invoice is shown or printed |
| sku | text | NOTE: this field name and type differ from 2003 (above) |
| ProductName | text | |
| Quantity | number | |
| PricePerUnit | currency | |
| PaymentTerms | text | |
| Discount | number | |
The Employees Table
|
Employees Table Names and Pay information for workers |
||
| Download file to fill in data is : Employees.txt - Right-click on this link and save the file, then import the data | ||
| Field Name | Data Type | Description |
| EmployeeID | autoNumber | This field relates each employee to any invoice or to any time-and-pay record |
| DepartmentName | text | |
| SocialSecurityNumber | text | |
| EmployeeNumber | text | |
| FirstName | text | |
| MiddleName | text | |
| LastName | text | |
| Title | text | |
| text | ||
| Address | text | |
| City | text | |
| StateProv | text | |
| PostalCode | text | |
| HomePhone | text | |
| WorkPhone | text | |
| DepartmentID | number | |
| Birthdate | Date | |
| DateHired | Date | |
| Salary | Currency | |
| BillingRate | Currency | |
| SupervisorID | Number | |
| SpouseName | text | |
| EmrgcyContactName | text | |
| EmrgcyContactPhone | text | |
| Notes | Memo | |
| OfficeLocation | text | |
Note: The CustomerID field matches to Members Table
CustomerID Note
The CustomerID field in the two tables: Members, Invoice is very important in gettting a query to work which matches tables. To match as a relationship they must have the same datatype (in this case, number). To match in the query they must numbers in one table with numbers in the other. That is:
- The CustomerID field in Members is filled in with unique (no duplicate) sequential values (such as 1, 2, 3, 4, 5, etcetera). In the example data this field has no data. You will have to provide it with the right data. The data will have to fit within
- The CustomerID field in Invoice gets values dependent on which customer is buying from the store. In the example data you will see that this field is already filled in.
If your four-table query is not showing any data - only column headers - it is because there are no matching numbers. Go back to the Members table and look at the data for the CustomerID field.
The Products Table
|
Products Table Line items for each invoice. One record for each item listed on the invoice |
||
| Download file to fill in data: Products.txt - Right-click on this link and save the file, then import the data | ||
| Field Name | Data Type | Description |
| recid | autonumber | |
| sku | text | SKU stands for "StocKing Unit" and is a store's own product id |
| dept | text | general area where product belongs in store |
| title | text | |
| image | text | name of any image file to show product |
| price | currency | |
| shortDesc | memo | |
| longDesc | memo | |
| weight | number | |
| active | yes/no | |
| options | memo | |
The Printed Invoice Taken Apart
|
Directly below is an illustration of a standard invoice. All the detail lines (items) are on the same piece of paper. The invoice is reached by the invoice number and on the invoice is the customer number and the employee number. When we convert this to a database we need to break the information iterms into separate tables. In the case of the invoice information at the top of the paper and the invoice items, these will become separate tables. See below on the right for more ...
|
The Customer table is separate as is the employee table. The InvoiceDetail table is just a list of item-lines on invoices. So that we are able to put the item-lines with the invoice, we include the invoice number as an piece of data in each InvoiceDetail record. So when we get an invoice in a database we are really pulling in the invoice header. From the invoice header we then look for all the InvoiceDetail records which belong to the invoice. And from there we create "calculated fields" which are ways to display - "on the fly" - the line totals and the final bill/invoice total. |
