Create a Set of Tables, then import data

Download Files - for import into tables

These 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 table
Employees.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 table

Note: 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    
EMail 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

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)
The names you enter from the table below will have CustomerID fields with values from 2 through 9.

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.

gid2003CXern
Created3/25/2003
Updated3/25/2003
CustomerID2
FirstNamePaulo
LastNameHernandez
Address1123 Main St
Address2Apt 4
CityTopeka
StateProvKS
MailCode66101
Phone123-456-7890
EMailph@m.co
 
gid2003CXmit
Created3/25/2003
Updated3/25/2003
CustomerID3
FirstNameGloria
LastNameSmith
Address1456 Second St
Address2 
CityGeneva
StateProvNY
MailCode14456
Phone315-789-1230
EMailgs@m.com
 
gid2003CXmar
Created3/25/2003
Updated3/25/2003
CustomerID4
FirstNameKhalid
LastNameOmar
Address1789 Third St
Address2 
CityAuburn
StateProvNY
MailCode13021
Phone315-192-0189
EMailko@m.com
 
gid2003CXais
Created3/25/2003
Updated3/25/2003
CustomerID5
FirstNameAnnunciata
LastNameKaiser
Address19 Rodeo Dr
Address2 
CityBeverly Hills
StateProvCA
MailCode90210
Phone310-274-9123
EMailak@m.com
gid2003CXian
Created3/25/2003
Updated3/25/2003
CustomerID6
FirstNameGeorge
LastNameCiano
Address1160 Genesee St
Address2 
CityAuburn
StateProvNY
MailCode13021
Phone315-253-5555
EMailgc@m.com
 
gid2003CXtin
Created3/25/2003
Updated3/25/2003
CustomerID7
FirstNameMarie
LastNameStine
Address127 N. Main St
Address2 
CityCanandaigua
StateProvNY
MailCode14424
Phone585-396-1234
EMailms@m.com
 
gid2003CXisc
Created3/25/2003
Updated3/25/2003
CustomerID8
FirstNameLoren
LastNameDisc
Address147 Castle St.
Address2 
CityGeneva
StateProvNY
MailCode14456
Phone315-789-5555
EMailld@m.com
 
gid2003CXalu
Created3/25/2003
Updated3/25/2003
CustomerID9
FirstNameHai
LastNameFalutin
Address1475 N Beverly Dr
Address2 
CityBeverly Hills
StateProvCA
MailCode90210
Phone310-858-5555
EMailhf@m.com

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.

  1. Invoice table - Download Invoices.txt
  2. Invoice line item (detail) table - Download InvoiceDetail.txt
  3. Employees table - Download Employees.txt
  4. 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  
Email 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:

    1. 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
    2. 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.