Ronald Schultz said:
Is there anyone that could break down in laymen terms how to work with MS
Access? I have trying to make relationship with different table and I keep
getting this message "The index or primary key can not contain a null
value"
but when I attempt to correct the fault it tells me that I can not
That message is simply telling you that the column that you're using for the
primary key has some blank, or null values in it. And that is not allowed.
In most cases for the primary key, I'd suggest that you just use the default
autonumber "id" for that primary key. In fact, most tables have this collum
added by default. So the first thing to do is open up the table in design
mode and see what you have for primary key collum. (you'll see a little key
in yellow beside that column in design mode for that table). If you use the
wizard to create the table, then often you'll see things like a primary key
called contactID.
We can call this our parent or so called "main" table. The next step is how
to relate other tables to this "main" table?
It is very important to note that when you relates a child table to that
main table, you must declare a new column in this child table. This new
column you make as absolutely nothing to do with the primary key in the
given child table (in other words do not confuse the concept of a primary
key which each table should have, and that of an additional column we used
to relate **child** tables to parent table).
In other words, you have to add a new column to each child table that you're
going to related to a parent table. Keep in mind that this new column we
have to add to the child table has absolutely nothing to do with the primary
key to be in that child table. It is furthermore important that this new
collum we add is a plain Jane ****regular**** long number field. So before
you attempt to build a relationship you must build (add) this new column in
the child table. Furthermore it also means that you can't have any records
in the child table yet, because each child reocrd MUST have a value placed
into the "new" column we just added. So if you have existing data in the
child table, then you can NOT build a relationship because you don't know
what/who each record in the child table belongs to what parent record.
Here is an example:
parent Table: customers
colluns:id, FirstName, LastName etc.
In the above we assume that "id" is the primary key of our table customers.
In most cases it is likely that this "id" column is an auto number
automatically generated number by access.
Now let's assume we want to build a table that is a list of the customers
favorite colors.
child table: FavoteColors
collums:id, Color, customer_id
Notice in the above child table we still have a primary key called "id". We
really don't need this auto number primary key, and it's going to have
absolutely nothing to do what the relationship that we want to build to the
parent table called customers.
The way relationships work is you simply have to enter into the customer_ID
field, the value of the parent table primary key "id". So we might have
parent Table: customers
id Firstname lastName
2 John Smith
3 Joe Smo
Now,in our child table wwe might have:
child table:FavorateColors
id Color customer_id
1 Red 2
2 Blue 2
3 Red 3
In the above you can tell the customer with an id of 2 has a favorite color
of red and blue. You can see that the customer number 3 Joe Smo has a
favorite color of red. In a nutshell this is how relationships work, and the
customer_ID field in the child table has to be set to the correct value of
the primary key in the parent table. This is also why you really can NOT
enter data into the child tables until you set up the relationships first
(and, you need the "id" from the parent table, so this means you MUST enter
the parent reocrds first). This also means that in the child table the
customer_ID collum you're using for the relationship can not be blank or
null. (This also why you're having those problematic messages, you likely
have data in some of your child tables, and you can't have data in those
child tables before you build a relationship. So you must build a
relationship first, and then enter the data)
It is also extremely important to note that the child table customer_ID
collum is ***NOT*** set by MS access for you (how does MS access know what
color record belongs to what customer???). Access does not know this, and
therefore **YOU** are the one that must enter this value into the
customer_ID column. You can enter this value manually during data entry, or
if you use a form + sub form then access will set the value of the child
table column (CUSTOMER_ID) for you.
Also keep in mind that the customer_ID column in the child tabled is simply
a regular plain Jane long number field, it has absolutely nothing to do with
the primary key in that child table. it is not a special column of any type,
it is just simply column that **YOU** have to make up and "assume" as the
column going to be used for the relationship back to the parent table.
If you already have some existing data in the child tables, then you're
going to first add a collum in the child table for the collum to "relate"
back to the parent table. You then have to manually type in the values
(id's) to tell which parent reocrd in the child table belongs to who. ONLY
after you entered those values will you be allowed to setup a relationship.
So you really can't build a relationship with existing data in the tables.
My second question is this, once you create a relationship
between one field from Table A to Table B why can you not create another
one?
You can relate as many tables as you want, there should not be a restriction
in this. You must follow the above rules in ensuring that you first create
that additional column in the child table, and then ensure that the correct
value is set in that child table column to the correct parent table, or
hopefully is usually the tables are blank and you won't have that problem.
So for each additional table you want related, you'll have to add that plain
Jane regular long number column to the child table. You can then start
entering data.
Also on that same note why can you not create a relationship with in a
table?
There is absolutely no reason or need to do the above (in fact you can not
do the above, but you would never need to any way). If you have a customer,
and they have a phone number field, then simply place the phone number field
into that customer record, and by default that information is attached to
the customer. The whole idea of relationships is all about attaching
information to a particular thing, be it a customer, or perhaps your
Christmas mailing list. in the case of the phone number they only have one
work phone number, so you don't need a relationship to define this, but they
might have fifteen or twenty favorite foods, and therefore you would use a
child table to capture that type of information.
