Now the real power of access databases arrives. When you can start using multiple tables, those tables would need to then be linked together. So firstly, before we look at the mechanics of creating a secondary table. Then a link between the tables. We need to know what is the point in having multiple tables, what's the point in the relational database why can't I use one big table you can. But it just means. that you're going to have to restructure from time to time. Let's take our client's data we have in here. One table TBR customers that we have built. Let's go have a look at the design of that table, and you can see it holds information about customers.
What Is Relational Database In Ms Access? If we also wanted to hold information about what we'd sold to these customers. We'd need to add some new columns. So we'd have to add a column for what we've sold. So sold item be a short text could be a description couldn't it, and the sold date is fine. If we only sell one thing to each of our customers. What if we sell more than one thing. We have to add more columns sold the second item sold second item date, sold third item date. So instead of adding all these extra columns, and then adding another set of extra columns. Every time somebody reaches the maximum availability of sold items. We store the sales in a separate table.
What Is Relational Database In Ms Access & Use of primary key in table
So let's delete those two. Because we're not going to need the close the customer's table. Save the Changes, and look at creating a new table. The new table, we would create in design would have the requirement for a primary key. So I'm going to call it sales ID. We can set that as Auto-Number. Then we need to know what we have sold. So sales item notice I'm still sticking with my camel Case and no spaces in my field names. Sales Item that can be a description, and then sales price. There is a currency option here. This means it will be a numeric field already allowing decimal places. It already puts the currency symbol in for us. Then sales date which will be a date-time field our sales ID is the primary key.
We set that save the table TBL sales. However, there is at the moment one thing wrong with this. If I look at it, I can see, I can go in there, I can type the name of the item. How much it was the date next row. The item how much it was, and the date, but what I'm not telling the system is to whom I sold that. In the table, I also need to know who I'm selling this. Sales customer. Now when it comes to putting the customer details in here. I don't put their name, I don't put their address, put their ID, so that will be a number field. I don't need to put the number in for the customers, that I'm selling this particular item to save the table view.
How to sell product without customer ID
What Is Relational Database In Ms Access? Now we can go into sales items. I'm selling a horse for 1500, when did I sell it, well it was sometime in July, and which customer. Now I need to know the ID at the moment. While we are just in tables. I would actually need to know the ID. Once we move on and start to create some forms. You will see that we don't actually need to know. The ID because we can get access to helpers. I'm going to sell this to a customer one. A customer with ID one just checks the customer's table. I do have a custom one. I do it's actually me I'm just going to close that field list again. We come down to the second item. This is like adding the customer. I'm just adding in the sales.
The advantage of creating the table. This way as opposed to redesigning. The customer table is that here, I have one row for each sale, and I don't need to have any structuring of the table. If one customer has more things than another in here. I don't have to actually sell anything to any of the customers. They just don't have a record. If I sell more than one thing to one particular customer that's great. just becomes another record. So as well as selling the horse, I sold the saddle for 500 on the same day. Do you remember the little shortcut for ditto control in the single apostrophe copies? The value from the row above and the same customer sells customer one?
What Is Relational Database In Ms Access & It's reasons
Each cell is a separate row in the sales table, and subsequently. We will look at how we then connect. These tables together, we know what customers sold. What so that's one reason for having multiple tables for having a Relational Database. The second reason is to reduce repeating values. For example, at our customer's table, a lot of our customers will have the same title. You can see here even with only six. We have got five misters and one misses. There's repetition in our regions there's only likely to be a handful of regions there are sales regions.
What Is Relational Database In Ms Access? There's repetition, so I'm repeating the word north-south maybe even west and east, and because I'm repeating the text that can lead to errors remember in rubbish out. A to reduce the amount of text in the Database b to reduce errors. Because if we using our split tables, which we're going to do that'll reduce errors. Because you can only choose something that's a split. So what I'm effectively proposing that here not, because we're going to need to restructure this table. If anything changes, but we can take those titles. We can take those regions and even any other field that adds itself. As an appropriate repeating value and create a new table.
What Is Relational Database In Ms Access? I will go to create table Design, and this is going to hold the titles. It will be a nice little small table title ID again auto number, and then the actual title name, and that's just short text title ID is a primary key. We save the table TBL Titles view the table, and in here. We have our titles merely once, so Mr. Mrs we might have then lord. We might then have sir, we might have numbers. We might have mx all the potential titles. We might need the use of what then happens at the customer table. If these get replaced with numerical values. So 1 one two one.
If there were five Thousand six thousand fifty thousand rows, that would be a lot of changing to do, but in an ideal world. You actually built this relational database from scratch. So when you design in the first place, you would know that you wanted the titles table separate. You would know that you don't want a region table separate. So I can create, that regions table create table design region ID alter the number. These primary keys all of these I have just created as auto numbers. So they will be given a unique reference region name. Let's give a camel case short text to save the table TBL Regions. View the data north, south, east, and west.
Automatic numbers in customer table
They are given automatic numbers in my customer's table. I then replace the north one south is two. This one had not been given a region. So I'm going to put them in region three because I'm changing these two numbers. When we get it. There want to link this table to the titles. The regions I need to go back into design and change the customer title into a number field. It's going to hold a number and the region into a number field. Because it's going to hold a number when I save, that would have a little bit of a moan about losing some data. After all, we are changing from text to numeric, but I know I have already changed the values to numeric. So I'm not going to lose anything.
Fine save and close the regions close to titles, that brings me back to sales. The reason I have come back to sales is that I want to do one more table. And that is I'm likely to sell the same item many times. If I'm a shop or a wholesaler, then to have items that I then sell to multiple people. So instead of in here sticking the name of the item. I would really ideally want to stick the ID. So I need a new table in design, and this is for my products. Product ID we will go with an auto number. It's so much easier product name short text.
What Is Relational Database In Ms Access & enter entire Product info
As well as the product name, I would Store other things related to the product. There might be an actual product price currency. There might be the product weight you might need to store the weight, so that will be a number now. Because it's a number that's likely to have decimal places. I'm going to change the long integer to a double, and there might be even product Dimensions. I'm going to leave that as a text field so that we can type anything we want in there. Because we might want to put that measurement in meters, feet and inches. It depends on the product, don't forget to set the primary key to saving the table TBL products.
Now I know I already have two products. One is hot 1500 product weight 500 dimensions large. A saddle 500 weight 20 dimensions 18 inches. So because that's the text field, I can put anything. I like it in there, and then we just need to go back to our sails and replace the horse with one. Because I know that's product ID 1. And saddle with two back into design to change the sales item into a numeric field save again. A little bit of a moan about losing data, but I know that they're number values. So nothing's going to be Lost view, and I now have numbers in here. I could add further products, I could Perhaps have subtle cleaner need some of that only up that's just two dollars.
Advantage of selling product at different price
Product weight not really a lot one. We will call it the squeeze bottle is the dimensions, so that's item number three. If I then want to sell item refrigerators come down here. I have sold item number three for one dollar on the first of July. Two sales customers, Two have also sold item number three for 99 cents. I gave them a little bit of Discount on 625 to customer number three. We have got the price in the products table and in the sales table. Because the product table will hold the price of the item. This might be its statutory price, but the sales table holds the price, that we actually sold it at. We might give a little bit of discount to individual customers or sell it at a different price. Because this is a historical table. We will be here forevermore. It may be that currently when I sell the salad cleaner.
At two dollars we would put two dollars in here when we sold it. Then the price might go up. I would change this to three, but all the historical sales at two would still be in there. It would just be new sales going forward that would be three. There's plenty of reasons for having the price in both places. That's now becoming a proper relational database. It has multiple tables, things have clicked about why you would need multiple tables. I am pretty sure they won't click straight away. It will take a little bit of play and understanding for that to happen. But it will click at some point our next stage now is to link these tables together. That access knows that there is a relationship between them. We know, but we haven't been told access.