98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Access articles
Understanding Primary And Foreign Keys In Microsoft Access
Sun 21st March 2010
Data is stored in access databases in tables. A table is a list of rows and columns containing data. There are usually lots of tables per database. Imagine you needed to build a simple database of customers. The first table you would build is the Customer table, which we will call 'Tbl_Customer.' You must break down the data to its simplest form and not attempt to put all the information into one large table. Consider only the simplest information about the customer. In this table each Customer will have its own record, one record only per customer. Do not be tempted to put all the customer's sales etc in this table as the table will be far too large and impossible to manage. You will also have more than one record per customer and as this table is going to be used to define each customer in the database, that is unacceptable. In Tbl_Customer will be the first name, surname, first line address, second line address, Town, County, Postcode and an ID number that will be an automated number unique to the customer. This number is called a Primary Key. The most important thing to remember is that this number is always unique to each record. No two records will ever have the same number as a Primary Key.
One advantage of having a Primary Key per record is that this number can be placed in other tables as a 'Foreign Key.' If you were to create a table containing all the sales records, there would be a lot of sales information. It would be impractical to have to repeatedly add customer names and addresses in this second table. It would be a waste of space, disorganised and unnecessarily repetitive. It would also lead to duplicates since spelling mistakes on the name, for example, may lead to the same customer being entered several different times. When a report is created to show the sales per customer, one customer may have several different totals due to such errors. Database cleansing to eradicate such problems is a difficult and expensive process so it is preferable to use the Primary Key and Foreign Key method to link tables as a preventative measure.
The Foreign Key is essentially the Primary Key from one table placed in another table in order to join them. When creating a report to see how many sales there are per customer, access will look at a sales record, see that there is a Foreign Key and look in the relating customer table at the matching number to obtain the customer details. Data only needs to be entered once. When creating a front end for the users to enter the sales, a field can be placed on a form, which will automatically show the customer names, and the user can simply choose from the list. Behind the scenes, access will have placed the Primary Key for the selected customer in the Foreign Key field on the sales table.
The Primary Key is therefore vital to define individual records. In reality this is vital when there are likely to be extremely large databases containing thousand if not millions of records. The Foreign Key method allows cross-referencing of tables and records, which is particularly important in modern databases, which can contain thousands of separate tables. The cross-referencing and joining of the tables create relationships between the tables, which is what makes a relational database possible and practical.
Author is a freelance copywriter. For more information on access.training, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-788-understanding-primary-and-foreign-keys-in-microsoft-access.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsUniversity of London
Business Intelligence Analyst Mahir Yuksel Presentation Skills Dennis was absolutely great! Aspen Insurance
Service Quality And Improvement Manager, IT Danielle Ballemtine Management Programe Jon was great! Really felt his passion about the topic and that drew us into participation. Looking forward to the 2nd half of this course! Primesight
Estates Supervisor Desaray Ellis Introduction to Management (1 day) I found the session very productive. I think I got more out of the session having a small group as we all had opportunities to talk about our own individual situations. |
PUBLICATION GUIDELINES