Now the real power of access databases arrives when you can start using multiple tables that would need to be linked together. So firstly, before we look at the mechanics of creating a secondary table and a link between the tables, we need to know what the point in having multiple tables is and what the point is in MS Access relational database. But it just means that you’re going to restructure from time to time.
Let’s take our client’s data we have in here. We have built one table of TBR customers. Let’s go and look at the design of that table and see it holds information about customers.
If we also wanted to hold information about what we’d sold to these customers, we need to add some new columns. So, we have to add a column for what we’ve sold. Suppose we have sold one thing or more than one thing to our customers, so what would it be like?
We have to add more columns, sold the second item, sold the second item date, and sold the third item date. So instead of adding all these extra columns, every time somebody reaches the maximum availability of sold items, we store the sales in a separate table.
Access Relational Database – Use Of Primary Key In Table
You need to delete those two columns because we’re not going to 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 that I’m still sticking with my camel case and no spaces in my field names. Sales Item 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 for us. And sales date will be a date-time field for our sales ID, which is the primary key.
We set that and save the table TBL sales. However, there is 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 in the next row. 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 to.
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, I just put their ID, so that will be a number field. I don’t need to put the number in for the customers to sell this particular item to save the table view.
How to Sell Product without a Customer ID
Now, we can go into sales items. I’m selling a horse for 1500. When did I sell it? Well, it was sometimes in July, and to whom I sell it? 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. 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 Advantages of Creating the Table
This way, as opposed to redesigning, the customer table is here. I have one row for each sale, and I don’t need to have any table structuring. One customer has more things than another in here. I don’t actually have to sell anything to any of the customers. They just don’t have a record.
If I sell more than one thing to a particular customer, that’s great as it 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?
Ms Access Relational Database & It’s Reasons
Each cell is a separate row in the sales table, and subsequently, we will look at how we connect these tables together. We know what customers sold. That’s one reason for having multiple tables for having an access 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 likely to be a handful of regions.
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. If we use our split tables, which we’re going to do, that’ll reduce mistakes. You can choose something that’s a split.
So, I’m effectively proposing that we can take those titles because we’re going to restructure this table if anything changes. We can take those regions and even any other field that adds itself as an appropriate repeating value and create a new table.
For Example
I will go to create a table Design, and this is going to hold the titles. It will be a nice little table, title ID again auto number, and then the actual title name, and that’s just a short text title ID. We save the table TBL Titles view 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, and we might have mx all the potential titles.
We might need the use of what happens at the customer table.
If there were five thousand, six thousand, or fifty thousand rows, that would be a lot of changing to do, but in an ideal world. You actually built this MS Access 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 region’s table, create table design, and region ID number. These primary keys, 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, and the 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 is a need 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 bring 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. Suppose I’m a shop or a wholesaler, then to have items that I then sell to multiple people. So instead of sticking the name of the item, I would 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.
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 need to store, 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, so 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 and 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. It is because I know that’s product ID 1.
And saddle with two back into design to change the sales item into a numeric field. 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, and I could perhaps have subtle cleaner need some of that only up that’s just two dollars.
Advantage of Selling Product At Different Price
Products weight, not really a lot one. We will call it the squeeze bottle in 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 since 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.
For Example
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 access 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 to link these tables together. The access relational database knows that there is a relationship between them. We know, but we haven’t been told access.
