access primary keys This is a topic that many people are looking for. bluevelvetrestaurant.com is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, bluevelvetrestaurant.com would like to introduce to you Access 2016: Basics On Primary, Composite & Foreign Keys. Following along are instructions in the video below:
“Primary key is a field you designate that uniquely identifies a record so whenever you you create a table make sure you ve got one field that s gonna contain values only and no duplicates as you can see here in my computer s table. I ve got the asset tag field or the barcode field that has the datatype auto. Number assigned to it as you can tell where it says new that when i enter in a new record while i don t type over new because it s automatically gonna generate a new number for me sequentially so it goes from two two three four five six seven and so on so there s no duplicates in that field making it unique. But can you imagine if i went ahead and got rid of the asset tag field.
And i just had the manufacturer and all the others that if i had a bunch more records that well we re from the same manufacturer same date received purchase price from warranty that if you came into this database. And you saw all those duplicates. He might be tempted to go home let me go ahead and clean this up and delete the duplicates and that would be a boo boo a big fat no because you d be deleting my inventory so to take care of that we ve got one field that s gonna have no duplicates that uniquely identifies each computer. So you can see that well we don t have duplicates.
We actually have this many computers now. I know that the auto number is not a barcode so i want to convert this into a simple number field where i can actually enter in the barcode that comes off of the computer into this field. And i wanted to leave the auto number up there so you can see what it looks like when you use the data type feature. How it automatically generates a number for each record for you.
But let s get rid of it so to do that this is probably going to be the last video that i m going to go over the different ways to change views like for the tab you can right click on it to go to the design view. Or you can come up here on the home tab to the views group and either click on the drop down arrow to switch and go to the other view design or let me click off the opposite view is going to be up here that you re in and typically if it s datasheet view. It s design and design datasheet view in most of the objects. So let s go ahead and click on design.
You can see there s the asset tag. And then we ve got the data type auto. Number. Automatically generates a number of reach record that we add and then over here in the row header for that field you got this cute little key.
Always in that nice well that s the primary key. That s been assigned to it as you recall in an earlier training video. When you create a new table. Access automatically creates the first field for you well.
Let s do a review come up here click on the create tab go to the tables group click on table. And there s the generic table name until you save it and there s the first field. Automatically created for you defaulted with the generic. Name id.
And then the auto. Number data type assigned to it well. It s the only data type that in this view shows you new so. When you add in additional fields.
And you type in the data. There and you save the record it ll automatically convert this to well being the first record one and then sequentially two three four five six and so on and so if you had another table like four employees and you don t want a generic number for each employee. Like hey you re number one you re number. Two you can go ahead and convert this or tweak.
It and rename it and call it like ss for a social security. Number. And then of course go ahead and go to the design view and change it from auto. Number because you don t want to automatically generate a social security.
Number you want to be able to type in that number in any case. Let s go ahead and close out and and that s what we did here is we tweaked it well almost we got to change it from automatically generating a number click in it click on the drop down arrow to just a number that we can type in now. How big is that number how small in other words. We want our database to be as efficient as possible when we pull up data.
Especially if we have hundreds of thousands of records. It could slow it down a bit and so what i mean by that is the field size are we entering in just a few numbers or large numbers into this field. Well. It s just a few numbers so with the data type here if we come down below into the field properties into the field sighs.
The default for the number data type is long integer. And you can come over here and read a synopsis about it and recommendations. But i want to stop us right here before you start reading it you can go ahead and read it. But i want you to know that access is a process when it comes to creating a database.
And i can go off on a lot of tangents. So instead let s go ahead and start with our building blocks. One video at a time and then i ll introduce you to these other things like relationships in a later training video that way once we got the basics we can start putting it together a lot easier than going off on tangents. So access is a process watch the videos at least hopefully you watch all the videos throughout the entire course.
So you got a great foundation. Because there are things that are not covering here that will be addressed and a lot easier later on so having said that let s go ahead and continue and be patient just keep watching the videos. We ve got the field size. Which is long integer you can go ahead and click on the drop down arrow and go mm hmm well i bet.
If it s not long integer must mean that it s a lot shorter. If you re like i don t know what this means just go ahead and make sure that the cursor is flashing that field that you want more information about like for the field size and then hit the f1 key on the keyboard goes to microsoft com. Go ahead and scroll down and over and there s the integer stores numbers in the tens of thousands as opposed to long integer. Which is huy that s a lot and you can see over here.
The storage size just to have that field is two bytes as opposed to four bytes and so you want to make it as efficient as possible if any one of the records throughout the hundreds of thousands for that field is going to be well anything larger than the tens of thousands. Then you have to choose long integer for that field let s go ahead and close out and come down here. And we can click and change. It to let s just do integer now the primary key by default was assigned to the first field.
We just renamed it and changed the datatypes so it could fit our situation and we want to keep track of not an auto number just any number but an actual asset tag or barcode. But if that s not the field that you want the primary key assigned maybe. It s another field down below well. I know it wouldn t make sense to put it for the purchase price because we re buying pretty much the same computer.
It s all going to have the same price. But as far as assigning it goes well come up here go to the related contextual design tab go to the tools group. And there it is primary key and you can see when i click in the field. Here that has it a sign that it s also highlighted there so when i go to another field.
And i want to flip it click on primary key and it reassigns it from the asset tag to the purchase price. But i don t want it to the purchase price. I wanted for the asset tag. Let s go ahead and click.
There and select primary key and now we re back to where we started now. The primary key isn t just something that says hey there should be no duplicates in this field. It s also a cop. A police officer.
It will enforce this so it did assign to the asset tag. If you try to type in a duplicate number you ll get busted not only that but it won t allow any empty values either want to take it for a drive okie dokie. Let s come up here switch views you can go ahead and save it. But if you forget to save it and you switch the view by clicking on data sheet.
It ll prompt you to save it click yes and here we go okay. So we don t have new down below in the new record so we know it s not auto. Number anymore in which case. We have to go ahead and type in our own barcode so to keep it simple instead of typing in a bunch of long numbers.
Let s just go ahead and type in a number here hit the tab key and we ll do micron hit. The tab. Key ctrl colon to put in today s date. Tab.
Let s do a thousand tap or a hundred let s go ahead and if the spacebar and do warranty. There and you can see it s in write mode. So we haven t saved it yet. But notice that well in the asset tag column.
Where i assigned it the primary key is said there could be no duplicates well if i save it right now. I m gonna get busted hold down the shift key and hit enter. And it says off the changes. You request the table are not successful because they d create duplicate values in the index primary key or relationship change.
The data in the field or fields that contain duplicate data remove the index or redefine the index to permit duplicate entries and try okay i got busted let s click okey dokey and so instead the other thing that the primary key does besides not allowing duplicates is no blanks. So if i go ahead and click off to save. It it says. What are you doing you gotta have something in this field.
Okay bust. It again so let s go ahead and type in something that s not already in that field. And then hold down the shift key hit enter ah finally accepted now there are two more keys. I want to introduce you to i don t want to go over them in great detail and we ll cover those a bit later on but as access for building.
A database is a process i want to introduce it to you so you can start thinking about it or familiarizing yourself with it and to do that let s go ahead and change views right click go back to design. And the first key that we went over was the primary key. Which was assigned to one field. The next.
One is called a composite key. Which has two or more fields assigned a primary key within the table. And to oversimplify. Let s say we have an employee s table.
Where we just have the employees first name and lastname well you may have some employees. I have the same first name and some that have the same last name typically i would say you don t have employees that have both the same first name and lastname. And so what you do is you go ahead. And say ok collectively between the two fields.
The first name and lastname. There can be no duplicates and that would be a composite key. So to assign two or more fields. The primary key.
Which once you assign it if it s two or more it converts it into a composite key or it s known as a composite key. It doesn t say hey i m now composite. It just has this key here duplicated two or more times. Just go ahead and click on the row.
Header for one and go down or up to select the others or if it s nonlinear. Then hold down the ctrl key and click on the row header for another. And i ve got a total of three highlighted or the row headers are highlighted one two and three and then come up here on the design tab and the tools group and click on primary key. And now we have all this might get a little bit confusing here but between those three fields.
We can t have any one of them be duplicated so i can have duplicate manufacturers duplicate date receive duplicates in the warranty field. But but when all three i can t have more than one record that has the same manufacturer the same date received in the same warranty collectively that is i can have one record. That has the manufacturer atlas. The date received january 1st the warranty yes.
But i can t have a second record that has that matched it can have the same manufacturer the same date received or the same manufacturer and the date receive. But it can t have the same manufacturer. Date received and warranty or the various combinations that you can see where you can have the same warranty and the date received. But the last one here manufacturer can t be the same.
It s got to be different. So you can see how this can be a bit challenging. But to keep it simple just think about that first name last name. So if this was first name and last name well to go ahead and unassigned.
The primary keys let. Me click off you can go ahead and click into any one of those and then just deselect the primary key and it removes it from all of them you can just go ahead and welp. That was first name and last name you d select primary key and so they can have the same first name you can have five bobs and you can have the same last name feist. Miss.
But you can t have more than one bob smith. Collectively let s go ahead and remove that come back up here and select asset tag and click there. And there we go that cleared out had to refresh itself because i was wondering why weren t he was in bold in any case. I clicked in it and clicked out and it removed the bold and then finally we got the foreign key now.
The foreign key is the field you create one table that will have the same data type value and relates to the primary key of another table in other words when you break your data down into the smallest. Most meaningful parts your tables like i ve got my computer s here then i ll have a table for employees well each employee is gonna have a computer so how do i relate to them or link them up between the two what you can do in this case. As one example is i d create a field over here in the computer s table. And call.
It the employee id or type. An employee id and link the employee id field. Here known as the foreign key. Because it s going to link up and relate to the primary key in the employee table the field.
The employee id that the primary key would be assigned to or from the employees table. I can do it the other way around i could have the asset tag listed at the bottom of the employees table that would be the foreign key. Because that would relate to the primary key or the bar code. The asset tag in the computers table and we ll go over this in more detail.
But i actually want to introduce it to you because in the next training video. When we learn how to create relationships we need to make sure we can relate these tables with primary keys and foreign keys. So my recommendation is that each table ought to have a field that contains unique values the primary key assigned to it and also relate to other tables. We can find out in this case.
The computers that are assigned to each employee in the employee table have perhaps maybe the employee id below here in the computers table. That s going to link up again to the primary key assigned to the employee id field in the employee table thanks for watching hey as a quick reminder. If you liked my video please give it a thumbs up you can also click on me and subscribe to my. Channel get notified of the latest videos and for only 200 a month you can have access to all my microsoft office training.
Thank you for watching all the articles on the topic Access 2016: Basics On Primary, Composite & Foreign Keys. All shares of bluevelvetrestaurant.com are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.