Creating a Table.

Open the example file you downloaded (from here).

You will see that the database has only two tables it in now, one for Customers, and one for Orders.We are going to add a third table, but first we need to covers a few points about tables.

The Data(sheet) View of a Table

If you click on the Customer table you will see it looks a lot like an excel sheet filled with data.

You can see there is data for 29 customers in our database. For each customer we have a set of columns that contain some attribute of a customer - their first or last name, the company they work for, their phone number, and so on. Each row of data is called a record, and the structure of a record is the same for every customer in the table.

In some ways this view is exactly the same as using excel: you can click on any of the cells to add or change the data, and you can click on a new line at the bottom and add a new record, or you could delete a line. The difference is that excel will let you enter anything in any cell. If you put text in a number field it might break something (a formula, for example), but excel will happily let you do it anyway. Access, on the other hand, won't let you put data where it doesn't belong.

Aside: Spreadsheets Vs Databases

In excel, the location (which cell it is in) of data is how you know what it is and how you manipulate it. To write a formula to add two pieces of data (two numbers) together, you look up which cells the numbers are in and write the formula to add them together (e.g. "=A1+A2"), and you put that in some other cell (location). It doesn't matter where you decided to put the data originally, but if you move them later the formula will break. You (the operator) also need to make sure that there are only numbers in the cells you want to use for this formula, because excel won't care - if you accidentally put "twelve" in one of those cells by mistake excel isn't going to stop you trying to add it to another number, it will just give you a failed result.

You can also put lots of different types of data one one "page" (sheet), in whatever free-form design you prefer - you can make your excel worksheets look like christmas trees if you feel like it - because you as the user can make sense of it; excel doesn't have to. Usually though, you want to be able to deal with a lot of data (that's the advantage of computers after all) - to add together a long column of numbers, or perhaps create a filter based on thousands of rows. To do that you generally end up needing data in  List Data format - every column contains the same type of data, no empty rows of data and so on - because excel features like autofilters, conditional highlights, and pivot tables only work over data organized that way. That format is basically emulating a database record. In a database, each piece of data is defined, and how each piece goes with other pieces is captured in the record structure.

You can often use either tool (spreadsheet or database) for the same job, but the following distinction might help clarify the best uses of each:

• spreadsheets are generally best for analyzing or visualizing data
• databases are best at storing and processing large quantities of data, and often sit under other tools that are used to analyse the data (including excel)

Elements of a Database Table

Getting back to our example, the elements of a database table you need to remember are as follows:

1. Each Table contains a number of Fields. Fields are pieces of data about something of interest that we want to keep track of in our database. These are the columns you see in the Customer datasheet view above. In the Customer table, the fields are CustomerID, Company, Last Name, First Name and so on, all the way to Notes.
2. Each row of data in a Table is called a Record. Each record is therefore made up of many fields, but only one instance of each. So one customer id + one customer company + one last name + one first name etc (all from the same row) equals one customer record. The first customer record in Northwind is the first line of the table, for Anna Bedecs. There are 29 rows of data = 29 records. *
3. We also want to be able to keep track of different information about different things, so a database can have multiple tables (look back at Northwind - there are 20 different tables) So Fields that are connected or related to each other somehow are grouped together in separate Tables. Our database has two tables - Customer, and Order.
4. Each table can have different fields in it, but every record within one table must follow the same design (see below). You can't have one customer record where you have the fields defined above, and then another with a totally dfferent set of fields, in the same table. Every table has a single design, or definition. Every record in the table matches that definition, that set of rules. Think of it like a cookie cutter - you can make as many records with it as you wish, but they are all the same.
5. You can, however, have fields with the same name like "Telephone Number" appear in multiple tables. To stop confusion we distinguish fields in different tables from one another by including the table name. For example, Customers.Mobile Phone is the Mobile Phone field inside the Customers table.

* The number of records in a table is conceptually unlimited, but very large databases can exceed the limits of different products (Microsoft Access is a consumer / small business database product meant for desktop use, for instance, while Microsoft SQL Server is an enterprise database product). Access 2016 limits include a 2GB file size, and 255 fields per table.

The Table Design View

For every field in a table, we need to define the datatype of that data. This type defines what sort of data can be held in that field, and enables certain processing. Let's examine the design strucure of the Orders table below. To see this yourself, click on the "View" button on the left hand side of the menu ribbon.

Looking at the Order table, we can see that the OrderID field is a Number (more on that in a moment), the Order Date is Date/Time, and the Shipping Fee is a Currency. This means that only numbers can be entered in the OrderId field, only valid dates can be entered in the Order Date field, and data entered into the Shipping Fee field must correspond to rules about currency. The following table from the Access Help shows you the usage for each of the datatypes built in to Access.

Primary Keys

An idea we need to introduce at this point is that of a key.

We need to be able to tell one record from another. We do this by making one of the fields (or one combination of fields) in a table the primary key. Primary key status means:

1. The primary key field can never be empty - it is mandatory that there is a value. If there is a chance we might not have a value for a field for even one record in the table (say one customer that doesnt have an email address), it can't be the primary key field.
2. The primary key field must uniquely identify each record. If we choose "Patient Name" as a primary key, we can never have two patients with the same name, ever, as we couldnt tell them apart. For this reason we often add fields like CustomerID or OrderID, because we can guarantee they will be unique.
3. The primary key-value must exist when the record is created. This means you must know it at the time you enter the record, you can't come back and enter it later.
4. The primary key must remain stable—you can’t change the primary-key field(s).  Don't choose a field that might change (for instance
5. The primary-key value can’t be changed.

If we need more than one field to comply with the rules above we call that a compound key (made of multiple fields). For instance, people names aren't very unique (try getting a gmail account using your full name and no numbers in it), but a person's name plus DOB might be unique in some situations. So a compound primary key might be Name+DOB. If you do this, be very sure you can comply with all the rules above, especially the uniqueness requirement.

In our file, CustomerID and OrderID are the primary keys of the Customer and Order tables, respectively. You can tell by the little key icon next to the field name in the design view. You can't have a table without a primary key defined - Access will not let you save the table.

If you look at Northwind, many of the key fields have the type "autonumber". This means Access will assign the number automatically, ensuring it remains unique. If you want to manage the ID numbers yourself, you can just use Number.

The other reason we need a key, is so that we can link records from different tables together (we will look at that soon).

Adding a New Table

We now have all we need to create a new table. We are going to add the table Shippers. This table will hold data on who the shipping agent is for all our orders.

To create the table, go to the Create Menu (top ribbon menu), and click on Table.

You will see a new table, in datasheet view. By default, Access gives it the name Table1, until you save it. Do that now, by right clicking on the Table tab, and clicking "save". Call the new table Shippers.

Now click to change to the table design view. You will see Access wants you to have a primary key field, so by default it gives you the field "ID" with the type autonumber, and the little primary icon is present. Just to make things very clear, we are going to rename ID and call it ShipperID. Click on the field and edit the name. You can leave the datatype as autonumber.

Now we want to add all the other fields to our table. If this was a database you were designing, you would need to think about which fields you need and what types they should have, but we are going to follow the Northwind example, so add fields until your table matches the image below.

Adding Data

We now have a table that has all of the fields defined, but its empty, waiting for data. You can type this in now (I chose Shippers because it only has 3 records). Note that the Northwind creators were fairly slack by the time they got to this table, so most of the fields are empty and all 3 shippers seem to have the same address.... If this was a production system, those would be red flags for bad data.

Now that we have our new table, it is time to connect it to our other tables. Continue on to table relationships.