The key component to all relationships is communication, and this is no different with your database. One of the founding principles of any relational database, like MS Access, is that of the relationships between tables. No I do not really mean for you and your relational databases to go sit on a plush leather couch for an hour a week and have a D&M. I am referring to the relationships between the tables and how they link together to save you inputting time and storage space.
It is possible to build a database based upon just the one table, but then that table could grow at an alarming rate. This would cause you problems around accessing the correct records speedily, as well as causing you to have to input the same data over and over again. For example, imagine that you own a small business for home selling beauty products and you have decided to use Access to manage your invoices. You create only one table in this database to hold all of your invoice details (customer names and addresses, invoice numbers, invoice dates, and product details… the list goes on). This means that every time you want to create an invoice you would have to input all of the details, from scratch again and again, for each of the items purchased on that invoice, as well as details of the customer and details of the invoice itself.
This might only be a small nuisance while you are still a small company and have a small number of customers and only a few products. In a situation like this you will only be producing a few invoices. However, once your business grows and you develop a large customer network and have a lot of products, you will find yourself producing lots and lots of invoices and spending a lot of time unnecessarily inputting data that you have already entered at least once.
So, how can we rectify this? If you split your table into two separate tables, one for customers and one for products, you can hold information about these items just once, and that data can be ‘referenced’ in other tables. To do this, you will need to create a field in the customer table that uniquely identifies each customer, as well as one in the product table that uniquely identifies each product. These fields can be allocated in Access as “Primary keys”. This means that they are the unique identifiers for just one record in the table. In order to create a record for an invoice you would need a third table (we’ll call this table simply ‘Invoice’). The invoice table will need to hold details that are pertinent to just one invoice, like the invoice date, the due date and the total amount of the invoice and it would, of course, need to hold, or link, to information about the customer and the products appearing on the invoice. You could set the invoice number as the primary key for the invoice table and create links to the customer and product tables by including the customer ID (the primary key from the customer table) and the product ID (primary key from the product table). When the primary keys from one table are used to create a link in another table like this they are termed “Foreign keys”.
So, now to create an invoice record you simply need to enter an invoice number, invoice date and due date plus the client ID and the product ID. However, the problem now is that you can only record one product against an invoice. This is because the invoice ID has been used as the primary key for the invoice table and, as we know, the primary key is a unique identifier. Therefore, you will have to overcome this problem by creating one more table. This table will hold the product information for a given invoice – we can call this the “Invoice Product Detail” table. For this final table you will need to be able to record the invoice ID many times (each time you allocate a product to an invoice) so that data item cannot be set as the primary key and you would need to give the table another field to use as primary key (e.g. Invoice Product ID), and add the Invoice ID as a foreign key.
Now, to create an invoice with several products you would just create the invoice record containing the invoice number, invoice date and due date, add the customer ID (so you can find the customer details to add to the invoice) and add a product ID for each invoice product detail record.
Access is a very powerful and useful tool for small businesses, and its power can be enhanced a great deal by understanding the concept of database relationships. Boot up your software and get learning.
Over and out
MobileGekette
