Primary Keys, Indexes, Relations (inability to update/edit queries

[LauraL]

Created an employee database with the following tables:
Employee Info
Haz Mat Training Records
Safety Training Records
(where tables for training records are for 2 separate training programs)

I have created identical queries and forms using name and SS# from the
employee info table for both haz mat and a separate set for safety training.
Everything on the safety training queries and forms operate without issue.
Having issues with an error message with Haz Mat Queries and forms (not able
to update any of the information) - getting index, primary key or
relationship error. I've match all indexes, primary keys and relationships
(what is set up for safety training matches how I've set up Haz Mat).... any
suggestions for what may be causing error message??

 

[Jeff Boyce]

Laura

I don't know what you mean by "match[ed] all indexes, primary keys and
relationships".

It all starts with the data. Please provide a description of the tables you
are using ... for example:

tblPerson
PersonID (primary key)
FirstName
LastName
DOB

tblClass
ClassID (primary key)
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID (primary key)
PersonID (foreign key)
ClassID (foreign key)
EnrollmentDate
...

Your description didn't mention foreign keys ... how are your tables
"related"?

You mention two separate (but very similar sounding) tables for [haz mat]
and [safety training]. Are these two tables essentially identical, except
that one is for [haz mat] and the other is for [safety training]? If so,
you've designed your table structure to mimic what you would have probably
done if you'd been limited to using a spreadsheet! If the two tables are
essentially identical in field structure, you only need one table, with one
additional field to indicate [?haz mat] or [?safety training]!

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LauraL]

Thank you Jeff. . . here's more info

tblEmployeeInfo:
SS# (PrimaryKey)
Per# = personnel number
Last Name
First Name
. . . and more related to dept, status, address, city, st, zip, etc.

tblTRN_HazMat - - - all fields relate to just haz mat training
ID (primary key – assigned by access)
SS#
. . . multiple fields related to training record
Date fields (1 for each of the three different tests)
Test A, B (1 for each of the three different tests)
100% = Y/N (1 for each of the three different tests)
ManagerReview (1 for each of the three different tests) –
indicates if test is with manager to be
reviewed
HazPerformance completed
HazShip Compliance turned in
1 record includes all three HazTest, as well as the performance and
ship compliance info - all related to a single year's worth of HazMat
training per employee.

tblTRN_Safety
ID (primary key – assigned by access)
SS#
. . . multiple fields related to training record (only 1 test in this
category)
Date field
Test A, B
100% = Y/N
ManagerReview – indicates if test
is with manager to be reviewed
1 record includes the training information for safety for a given year per
employee.

Maybe I should have combined these into one table, but I have created
multiple queries and reports to provide information either just on hazmat or
just on safety.

tblEmployeeInfo (one) has a one-to-many relationship set up with each
tblTRN_HazMat (many) and tblTRN_Safety (many).

There are no relationships between tblTRN_HazMat and tblTRN_Safety (don't
anticipate I'll need them, they are completely separate).

You mention – foreign keys (I have not established, don’t believe I need
them, I could be wrong....???).

I said that I matched what I did with tblTRN_Safety and with tblTRN_HazMat .
.. which simply means that I mirrored what I did with both tables (as well as
the creation of the queries and forms), I have also investigated the
properties of each and have made sure the properties are identical (i.e.
indexes, etc.)

Does this provide enough information or do you need more?

I am grateful for any help you can provide.

Laura

I don't know what you mean by "match[ed] all indexes, primary keys and
relationships".

It all starts with the data. Please provide a description of the tables you
are using ... for example:

tblPerson
PersonID (primary key)
FirstName
LastName
DOB

tblClass
ClassID (primary key)
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID (primary key)
PersonID (foreign key)
ClassID (foreign key)
EnrollmentDate
...

Your description didn't mention foreign keys ... how are your tables
"related"?

You mention two separate (but very similar sounding) tables for [haz mat]
and [safety training]. Are these two tables essentially identical, except
that one is for [haz mat] and the other is for [safety training]? If so,
you've designed your table structure to mimic what you would have probably
done if you'd been limited to using a spreadsheet! If the two tables are
essentially identical in field structure, you only need one table, with one
additional field to indicate [?haz mat] or [?safety training]!

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Created an employee database with the following tables:
Employee Info
Haz Mat Training Records
Safety Training Records
(where tables for training records are for 2 separate training programs)

I have created identical queries and forms using name and SS# from the
employee info table for both haz mat and a separate set for safety
training.
Everything on the safety training queries and forms operate without issue.
Having issues with an error message with Haz Mat Queries and forms (not
able
to update any of the information) - getting index, primary key or
relationship error. I've match all indexes, primary keys and
relationships
(what is set up for safety training matches how I've set up Haz Mat)....
any
suggestions for what may be causing error message??

 

[Jeff Boyce]

I'm not sure I have a clear image of your table structure yet... We aren't
there. We can't see what you're looking at.

It sounds like you have repeating fields in the HazMat table ("1 for each of
the three different tests"). This design is standard ... in spreadsheets!
If you want to get the best use of Access' relationally-oriented
features/functions, don't feed it 'sheet data!

Foreign keys are just the way to point back to another table's primary key
.... if you have SS# as a primary key in tblEmployeeInfo, then using SS# in
the HazMat table is using it as a foreign key there. It tells a HazMat
record which tblEmployeeInfo record it "belongs" to.

I don't understand what test A/B means in the Safety table, nor 100% Yes/No.

I'm reluctant to suggest ways to continue processing the data as you now
have it structured (as best I understand), as it will only get harder and
harder for both you and Access until you take the time to more fully
normalize your data.

If the terms "relational" and "normalize" are unfamiliar, consider spending
the time to come up the learning curve on these ... Access will "thank you"
and you will thank yourself.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you Jeff. . . here's more info

tblEmployeeInfo:
SS# (PrimaryKey)
Per# = personnel number
Last Name
First Name
. . . and more related to dept, status, address, city, st, zip, etc.

tblTRN_HazMat - - - all fields relate to just haz mat training
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record
Date fields (1 for each of the three different tests)
Test A, B (1 for each of the three different tests)
100% = Y/N (1 for each of the three different tests)
ManagerReview (1 for each of the three different tests) -
indicates if test is with manager to
be
reviewed
HazPerformance completed
HazShip Compliance turned in
1 record includes all three HazTest, as well as the performance and
ship compliance info - all related to a single year's worth of HazMat
training per employee.

tblTRN_Safety
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record (only 1 test in this
category)
Date field
Test A, B
100% = Y/N
ManagerReview - indicates if test
is with manager to be reviewed
1 record includes the training information for safety for a given year per
employee.

Maybe I should have combined these into one table, but I have created
multiple queries and reports to provide information either just on hazmat
or
just on safety.

tblEmployeeInfo (one) has a one-to-many relationship set up with each
tblTRN_HazMat (many) and tblTRN_Safety (many).

There are no relationships between tblTRN_HazMat and tblTRN_Safety (don't
anticipate I'll need them, they are completely separate).

You mention - foreign keys (I have not established, don't believe I need
them, I could be wrong....???).

I said that I matched what I did with tblTRN_Safety and with tblTRN_HazMat
.
. which simply means that I mirrored what I did with both tables (as well
as
the creation of the queries and forms), I have also investigated the
properties of each and have made sure the properties are identical (i.e.
indexes, etc.)

Does this provide enough information or do you need more?

I am grateful for any help you can provide.

Laura

I don't know what you mean by "match[ed] all indexes, primary keys and
relationships".

It all starts with the data. Please provide a description of the tables
you
are using ... for example:

tblPerson
PersonID (primary key)
FirstName
LastName
DOB

tblClass
ClassID (primary key)
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID (primary key)
PersonID (foreign key)
ClassID (foreign key)
EnrollmentDate
...

Your description didn't mention foreign keys ... how are your tables
"related"?

You mention two separate (but very similar sounding) tables for [haz mat]
and [safety training]. Are these two tables essentially identical,
except
that one is for [haz mat] and the other is for [safety training]? If so,
you've designed your table structure to mimic what you would have
probably
done if you'd been limited to using a spreadsheet! If the two tables are
essentially identical in field structure, you only need one table, with
one
additional field to indicate [?haz mat] or [?safety training]!

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Created an employee database with the following tables:
Employee Info
Haz Mat Training Records
Safety Training Records
(where tables for training records are for 2 separate training
programs)

I have created identical queries and forms using name and SS# from the
employee info table for both haz mat and a separate set for safety
training.
Everything on the safety training queries and forms operate without
issue.
Having issues with an error message with Haz Mat Queries and forms (not
able
to update any of the information) - getting index, primary key or
relationship error. I've match all indexes, primary keys and
relationships
(what is set up for safety training matches how I've set up Haz
Mat)....
any
suggestions for what may be causing error message??

 

[LauraL]

I think that may be part of the issue - - - - I did feed it 'sheet
data'...(took it right from my excel sheets (I'm embarassed to admit, but
speaks to how much I have yet to learn in access)....the terms Relational and
normalize are familiar. . . I am not an expert with Access (YET) . . .
knowing what I wanted to get out of the reports is what drove my decision to
do this...

If you could help me understand better how I can more normalize my tables
that would be great.

I think the employeinfo table is ok... it's the training tables. by what
you are saying it sounds like I can create one table for ALL training...

Maybe I need to consider creating fields for:
Training Type: options = haz mat or safety
Course: options = Haz I, haz II, Haz III, Safety

Then create separate records for each of the 4 training types.... It's when
I get to the queries that this starts to break down for me... in order to
create the queries that'll provide me the info I need in my reports. Would
it be helpful to email you copies of the reports (I have several, but would
send the 2 reports (haz and safety) that show all years of training)??

I know that I can set parameters...etc. I just seemed so simple to keep all
the haz test info for one year all on one record....

One more thing: foreign key - do I need to go into the training tables and
identify SS# as the foreign key - or did setting up the relationship 1-many
already do that??

Thanks again for your help...I am appreciative!

I'm not sure I have a clear image of your table structure yet... We aren't
there. We can't see what you're looking at.

It sounds like you have repeating fields in the HazMat table ("1 for each of
the three different tests"). This design is standard ... in spreadsheets!
If you want to get the best use of Access' relationally-oriented
features/functions, don't feed it 'sheet data!

Foreign keys are just the way to point back to another table's primary key
.... if you have SS# as a primary key in tblEmployeeInfo, then using SS# in
the HazMat table is using it as a foreign key there. It tells a HazMat
record which tblEmployeeInfo record it "belongs" to.

I don't understand what test A/B means in the Safety table, nor 100% Yes/No.

I'm reluctant to suggest ways to continue processing the data as you now
have it structured (as best I understand), as it will only get harder and
harder for both you and Access until you take the time to more fully
normalize your data.

If the terms "relational" and "normalize" are unfamiliar, consider spending
the time to come up the learning curve on these ... Access will "thank you"
and you will thank yourself.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you Jeff. . . here's more info

tblEmployeeInfo:
SS# (PrimaryKey)
Per# = personnel number
Last Name
First Name
. . . and more related to dept, status, address, city, st, zip, etc.

tblTRN_HazMat - - - all fields relate to just haz mat training
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record
Date fields (1 for each of the three different tests)
Test A, B (1 for each of the three different tests)
100% = Y/N (1 for each of the three different tests)
ManagerReview (1 for each of the three different tests) -
indicates if test is with manager to
be
reviewed
HazPerformance completed
HazShip Compliance turned in
1 record includes all three HazTest, as well as the performance and
ship compliance info - all related to a single year's worth of HazMat
training per employee.

tblTRN_Safety
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record (only 1 test in this
category)
Date field
Test A, B
100% = Y/N
ManagerReview - indicates if test
is with manager to be reviewed
1 record includes the training information for safety for a given year per
employee.

Maybe I should have combined these into one table, but I have created
multiple queries and reports to provide information either just on hazmat
or
just on safety.

tblEmployeeInfo (one) has a one-to-many relationship set up with each
tblTRN_HazMat (many) and tblTRN_Safety (many).

There are no relationships between tblTRN_HazMat and tblTRN_Safety (don't
anticipate I'll need them, they are completely separate).

You mention - foreign keys (I have not established, don't believe I need
them, I could be wrong....???).

I said that I matched what I did with tblTRN_Safety and with tblTRN_HazMat
.
. which simply means that I mirrored what I did with both tables (as well
as
the creation of the queries and forms), I have also investigated the
properties of each and have made sure the properties are identical (i.e.
indexes, etc.)

Does this provide enough information or do you need more?

I am grateful for any help you can provide.

Laura

I don't know what you mean by "match[ed] all indexes, primary keys and
relationships".

It all starts with the data. Please provide a description of the tables
you
are using ... for example:

tblPerson
PersonID (primary key)
FirstName
LastName
DOB

tblClass
ClassID (primary key)
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID (primary key)
PersonID (foreign key)
ClassID (foreign key)
EnrollmentDate
...

Your description didn't mention foreign keys ... how are your tables
"related"?

You mention two separate (but very similar sounding) tables for [haz mat]
and [safety training]. Are these two tables essentially identical,
except
that one is for [haz mat] and the other is for [safety training]? If so,
you've designed your table structure to mimic what you would have
probably
done if you'd been limited to using a spreadsheet! If the two tables are
essentially identical in field structure, you only need one table, with
one
additional field to indicate [?haz mat] or [?safety training]!

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Created an employee database with the following tables:
Employee Info
Haz Mat Training Records
Safety Training Records
(where tables for training records are for 2 separate training
programs)

I have created identical queries and forms using name and SS# from the
employee info table for both haz mat and a separate set for safety
training.
Everything on the safety training queries and forms operate without
issue.
Having issues with an error message with Haz Mat Queries and forms (not
able
to update any of the information) - getting index, primary key or
relationship error. I've match all indexes, primary keys and
relationships
(what is set up for safety training matches how I've set up Haz
Mat)....
any
suggestions for what may be causing error message??

 

[Jeff Boyce]

Laura

If I were setting up a "training db" in my environment (no guarantees it
matches yours), I'd probably use:

tblEmployee
EmployeeID
FName
LName
DOB
<<no SS#, since there are some strict rules about asking for/using
this, and not everyone has one)>>

tblTraining
TrainingID
TrainingTypeID (a foreign key from a TrainingType table, so you
could always add more training/types)
TrainingTitle
TrainingDescription

tblDepartment
DepartmentID
...
(you did describe some info about an employee as "department",
right -- what happens when s/he moves?!)

trelAssignment
AssignmentID
EmployeeID
DepartmentID
AssignmentFrom
AssignmentTo
SupervisorsEmployeeID

trelTrainingCompleted
TrainingCompletedID
EmployeeID
TrainingID
TrainingDateFrom
TrainingDateTo

tlkpTrainingType
TrainingTypeID
TrainingType (e.g., HazMat, Safety, DefensiveDriving, ...)

The testing issue opens an entire new can of worms. You might want to take
a look at Duane's db for creating tests/surveys to see if you can adapt it
to your situation. Take a look at Duane Hookom's AtYourSurvey as a model
for data structure.

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I think that may be part of the issue - - - - I did feed it 'sheet
data'...(took it right from my excel sheets (I'm embarassed to admit, but
speaks to how much I have yet to learn in access)....the terms Relational
and
normalize are familiar. . . I am not an expert with Access (YET) . . .
knowing what I wanted to get out of the reports is what drove my decision
to
do this...

If you could help me understand better how I can more normalize my tables
that would be great.

I think the employeinfo table is ok... it's the training tables. by what
you are saying it sounds like I can create one table for ALL training...

Maybe I need to consider creating fields for:
Training Type: options = haz mat or safety
Course: options = Haz I, haz II, Haz III, Safety

Then create separate records for each of the 4 training types.... It's
when
I get to the queries that this starts to break down for me... in order to
create the queries that'll provide me the info I need in my reports.
Would
it be helpful to email you copies of the reports (I have several, but
would
send the 2 reports (haz and safety) that show all years of training)??

I know that I can set parameters...etc. I just seemed so simple to keep
all
the haz test info for one year all on one record....

One more thing: foreign key - do I need to go into the training tables and
identify SS# as the foreign key - or did setting up the relationship
1-many
already do that??

Thanks again for your help...I am appreciative!

I'm not sure I have a clear image of your table structure yet... We
aren't
there. We can't see what you're looking at.

It sounds like you have repeating fields in the HazMat table ("1 for each
of
the three different tests"). This design is standard ... in
spreadsheets!
If you want to get the best use of Access' relationally-oriented
features/functions, don't feed it 'sheet data!

Foreign keys are just the way to point back to another table's primary
key
.... if you have SS# as a primary key in tblEmployeeInfo, then using SS#
in
the HazMat table is using it as a foreign key there. It tells a HazMat
record which tblEmployeeInfo record it "belongs" to.

I don't understand what test A/B means in the Safety table, nor 100%
Yes/No.

I'm reluctant to suggest ways to continue processing the data as you now
have it structured (as best I understand), as it will only get harder and
harder for both you and Access until you take the time to more fully
normalize your data.

If the terms "relational" and "normalize" are unfamiliar, consider
spending
the time to come up the learning curve on these ... Access will "thank
you"
and you will thank yourself.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you Jeff. . . here's more info

tblEmployeeInfo:
SS# (PrimaryKey)
Per# = personnel number
Last Name
First Name
. . . and more related to dept, status, address, city, st, zip, etc.

tblTRN_HazMat - - - all fields relate to just haz mat training
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record
Date fields (1 for each of the three different tests)
Test A, B (1 for each of the three different tests)
100% = Y/N (1 for each of the three different tests)
ManagerReview (1 for each of the three different tests) -
indicates if test is with manager
to
be
reviewed
HazPerformance completed
HazShip Compliance turned in
1 record includes all three HazTest, as well as the performance
and
ship compliance info - all related to a single year's worth of HazMat
training per employee.

tblTRN_Safety
ID (primary key - assigned by access)
SS#
. . . multiple fields related to training record (only 1 test in this
category)
Date field
Test A, B
100% = Y/N
ManagerReview - indicates if test
is with manager to be reviewed
1 record includes the training information for safety for a given year
per
employee.

Maybe I should have combined these into one table, but I have created
multiple queries and reports to provide information either just on
hazmat
or
just on safety.

tblEmployeeInfo (one) has a one-to-many relationship set up with each
tblTRN_HazMat (many) and tblTRN_Safety (many).

There are no relationships between tblTRN_HazMat and tblTRN_Safety
(don't
anticipate I'll need them, they are completely separate).

You mention - foreign keys (I have not established, don't believe I
need
them, I could be wrong....???).

I said that I matched what I did with tblTRN_Safety and with
tblTRN_HazMat
.
. which simply means that I mirrored what I did with both tables (as
well
as
the creation of the queries and forms), I have also investigated the
properties of each and have made sure the properties are identical
(i.e.
indexes, etc.)

Does this provide enough information or do you need more?

I am grateful for any help you can provide.


:

Laura

I don't know what you mean by "match[ed] all indexes, primary keys and
relationships".

It all starts with the data. Please provide a description of the
tables
you
are using ... for example:

tblPerson
PersonID (primary key)
FirstName
LastName
DOB

tblClass
ClassID (primary key)
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID (primary key)
PersonID (foreign key)
ClassID (foreign key)
EnrollmentDate
...

Your description didn't mention foreign keys ... how are your tables
"related"?

You mention two separate (but very similar sounding) tables for [haz
mat]
and [safety training]. Are these two tables essentially identical,
except
that one is for [haz mat] and the other is for [safety training]? If
so,
you've designed your table structure to mimic what you would have
probably
done if you'd been limited to using a spreadsheet! If the two tables
are
essentially identical in field structure, you only need one table,
with
one
additional field to indicate [?haz mat] or [?safety training]!

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Created an employee database with the following tables:
Employee Info
Haz Mat Training Records
Safety Training Records
(where tables for training records are for 2 separate training
programs)

I have created identical queries and forms using name and SS# from
the
employee info table for both haz mat and a separate set for safety
training.
Everything on the safety training queries and forms operate without
issue.
Having issues with an error message with Haz Mat Queries and forms
(not
able
to update any of the information) - getting index, primary key or
relationship error. I've match all indexes, primary keys and
relationships
(what is set up for safety training matches how I've set up Haz
Mat)....
any
suggestions for what may be causing error message??