Primary and foreign keys, database setup, and "Cannot add or change" error

[cpocpo]

Morning, all!

I thought I was passed the initial setup issues to actuall figuring
out what I was doing, but now I am back to the beginning.

I got the "You cannot add or change a record because a related record
is required in table 'tblBranchofService'.

From looking around the Internet, this error is related to a problem

in how my database is setup.

I also found this "**do NOT put the primary key field from a child
table into the parent table" in one newsgroup response, but then
somewhere else it talks about always putting the primaries from the
chidren into the parent......??

Here is what I have. Appreciate any help to make this better and stop
the error so I can enter data. Also, some of my fields may be
redundant or useless. (All of the relationships are "One-to-Many" with
"Enforce Referential" checked but not the two "Cascades"):

(My database tracks personal and recurring training information for
military and civilian staff, BTW)

tblEmployees:
EmployeeID (Autonumber, from EmployeeID on tblJobID)
CivPayGradeID (number, from CivPayGradeID on tblCivPayGrade))<---
Nothing is being stored/updated here.(?)
BranchofServiceID (number, from BranchofServiceID on
tblBranchofService))<---Nothing is being stored/updated here.(?)
JobID (number, from JobID on tblJobandJobCode)
SSN (text)
FName (text), MName (text), LName(text), CivorMil (text)
BranchofService (txt),
CivPayGrade(text), MilRank(text),
HourlyRate (currency), OverTimeRate (currency),
Job (text),
and other stuff like address, phone numbers and e-mail addresses.

tblJobandJobCode:
Job (text
JobID (AutoNumber, PK, to JobID on tblEmployees)
EmployeeID (number, to EmployeeID on tblEmployee)<-Nothing is being
stored/updated here.(?)

tblCivPayGrade:
CivPayGrade (text)
HourlyRate (number),
OverTimeRate (number)
EmployeeID (number)<--Nothing is being stored/updated here.(?)
CivPayGradeID (AutoNumber, PK, to CivPayGradeID on tblEmployees)

tblBranchofService:
BranchofService (text: Army, Navy, etc.)
BranchofServiceID (AutoNumber, PK, to BranchofServiceID on
tblEmployees)
MilRankID (number, from MilRankID on tblMilRank)<-Nothing is being
stored/updated here.(?)

tblMilRank:
MilRank(text)
MilRankID (AutoNumber, PK, to MilRankID on tblBranchofService)

Appreciate the help!

V/R

LostGuy (was lost, then found, now lost again.)

 

[Albert D. Kallal]

I also found this "**do NOT put the primary key field from a child

table into the parent table" in one newsgroup response, but then
somewhere else it talks about always putting the primaries from the
chidren into the parent......??

No, the above is wrong.

You "parent" table should have a autonumber id. This will be the primary key
of this table. By the way, ALL OF your tables should include that autonumber
id as the primary key.

Now, to relate any table to a parent table, you simply create a field in
that child table. This is a plane Jane "long" number type field. Note that
when you add a child record YOU MUST set that value. If you use a sub-form,
then, ms-access WILL set this value. We talking about a one main record, and
many child records that BELONG to that parent record. In 99% of these cases,
you will use a sub-form based on this child table, and each record added
will thus be attached to the parent table (via the plane Jane long number
field we created). So, to make these relationships, you the developer has to
set these values. (building a relationships in the relational windows does
NOT make ms-access set these values for you...you still have to do this
yourself -- or use a sub-form).

tblEmployees:
EmployeeID (Autonumber, from EmployeeID on tblJobID)
CivPayGradeID (number, from CivPayGradeID on tblCivPayGrade))<---
Nothing is being stored/updated here.(?)

CivPayGridID is not really relational type data field. All that field is a
place holder. if you want to enter a particular CivPayGridID, then you
simply have to TYPE IN the value into that field on he form. This is not
really a relational type setup, nor is it a one to many relational. It is
simply a placeholder,a nd field in which you enter values. of course, the
legal list of values that you enter into that plane Jane field is going to
be restricted to tblCivpayGrade. However, I notice that you don't have a
primary key id for that tblCivPayGrade???? Remember, when you add a new
employee, or delete a employee, you not going to be adding, or deleting
records from tblCivPayGrade. So, all that table serves is to provide ea nice
easy list of availing grade codes for you to use. So, in place of a text box
on the employee form, and you having to memorize what ID to enter, you could
use a combo box in place of a text box. The combo box would "list" all
values from civpaygrde table, but you still only typing in a silly single
value here. You are NOT adding new records (so, the difference between a
simple lookup value,a nd that of database relation of a parent to child
table is ARE YOU ADDING NEW RECORDS??? In this case for each employee, you
only enter ONE civpaygradeID (at least that is my assumption).

The above is not different then having one text box (field) to enter your
favourite color. Over time, you could build up a list of favourite colors,
and even make a table of colors to provide LEGAL values for that ONE field.
However, again, this is simply entering a value into ONE field, and is NOT a
relational concept.

However, if you to allow "many" cigpayGrades for each employee, then you
don't need that field in the tblEmployee anymore. So, is this a simple
lookup value, where each employee only requites to you type in a simple
tblCivPayGridID??, or can each employee is going to have many civpayGrades?

Which are you trying to accomplish? if it just one value, then a simple
field in tblEmploee will suffice.

It is the difference between storing a persons favourite color (one field in
the main record), or do you allow "many" favourite colors? If you need
"more" then one favourite color, the you need to add a NEW record for each
favourite color. (thus, you would make a table called tblMyFavoratecolors.
it would have that plane Jane long number field, and you would place the
employeeID in each record that belongs to a given employee. Remember, YOU
MUST set this value, as ms-access can't know what parent record it supposed
to belong to? (how will ms-access know what employeeID to place in each
tblFavorateColor record??...access does NOT KNOW what value to place...YOU
must set this value!!!

while you answer the above question...here is some more thoughts on
relationships:

=====================

Use the right type of relationships:

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

 

[cpocpo]

Albert,

It took me some time to digest all this, but I got rid of some tables
and made them combo boxes, and then I got the relationships lined up
like you said. Everything works now!

Thanks!

V/R

LostGuy