Setting up Observation Database

[dsmith via AccessMonster.com]

I am trying to create something of a survey database. I have seen the sample
database "At Your Survey", but I am not sure how to tweak it to what I need.
I have a form that is for safety observations. Supervisors will observe
procedures and mark them as "safe" or "at-risk". I have a list of things they

can observe but they don't necessarily have to observe everything on the
list. For example:
1. Stair/Ladder Use
2. Elevated Work
They could observe one of these things and mark it as "safe" or "at-risk". I
understand the concept of a table for questions and a table for answers and
then another table for possible answers for each question. I need all of the
possible observations to show up on the form, even if they are not selected
as safe or at-risk. I am unsure how to set up the form or the response table
from the form. I have been reading all the posts and I know that the "At Your

Survey" is a good example, but I just don't know how to modify it so that all

my observations show at once on the form.

I also only need this to
contain one "survey". I don't need response type or numerous surveys. I don't

want the user to have to pick what survey they want. I want the form to just
open up to the "survey" screen. I tried to use it to fit my application, but
it just doesn't seem to fit just right. Around 20 people will be doing these
observations monthly. Actually each person has to do four a month. I work for

a safety department and we are trying to find our biggest area of concern
when it comes to "at-risk" behaviors, as well as those things that we are
doing right.

I feel comfortable building the forms but not sure of the structure of my
tables and how they relate. Can you help me?

 

[Steve]

How about the following tables ........

TblProcedure
ProcedureID
Procedure

TblObserver
ObserverID
FirstName
LastName
etc

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
ProcedureID
LocationObservation (Number data Type. 1 = "Safe" and 2 = "At-Risk")

Steve
(e-mail address removed)

 

[dsmith via AccessMonster.com]

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself
concerning table relationships. I will revisit the tips on relationships to
see if I can figure this out.

How about the following tables ........

TblProcedure
ProcedureID
Procedure

TblObserver
ObserverID
FirstName
LastName
etc

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
ProcedureID
LocationObservation (Number data Type. 1 = "Safe" and 2 = "At-Risk")

Steve
(e-mail address removed)

I am trying to create something of a survey database. I have seen the
sample

[quoted text clipped - 45 lines]

I feel comfortable building the forms but not sure of the structure of my
tables and how they relate. Can you help me?

 

[Steve]

I follow a standard when setting up tables. All tables begin with "Tbl" and
the first field in the table has the name of the table followed by "ID". The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This (these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date. LocationID In
TblLocationObservation is a foreign key and refers to a specific location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself
concerning table relationships. I will revisit the tips on relationships
to
see if I can figure this out.

How about the following tables ........

TblProcedure
ProcedureID
Procedure

TblObserver
ObserverID
FirstName
LastName
etc

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
ProcedureID
LocationObservation (Number data Type. 1 = "Safe" and 2 = "At-Risk")

Steve
(e-mail address removed)

I am trying to create something of a survey database. I have seen the
sample

[quoted text clipped - 45 lines]

I feel comfortable building the forms but not sure of the structure of
my
tables and how they relate. Can you help me?

 

[Gina Whipp]

DSmith,

I noticed that a field name on one of the tables is a Reserved Word,
tblProcedure - Procedure. This will cause problems for you because it is a
problem for Access. (For a complete list of Reserved Words see...

http://allenbrowne.com/AppIssueBadWord.html

What I do to avoid this issue is prefix the field names with the table
letters, ie...

tblProcedure
pProcedureID (Primary Key)
pProcedure
etc...

tblLocationObservation
loLocationObservationID
loObservationID
loLocationID
loProcedureID (Foreign Key related to tblProcedure-pProcedureID)
loLocationObservation (Number data Type. 1 = "Safe" and 2 = "At-Risk")

....and so on with the rest of the tables. This should keep your field names
*safe*.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself
concerning table relationships. I will revisit the tips on relationships
to
see if I can figure this out.

How about the following tables ........

TblProcedure
ProcedureID
Procedure

TblObserver
ObserverID
FirstName
LastName
etc

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
ProcedureID
LocationObservation (Number data Type. 1 = "Safe" and 2 = "At-Risk")

Steve
(e-mail address removed)

I am trying to create something of a survey database. I have seen the
sample

[quoted text clipped - 45 lines]

I feel comfortable building the forms but not sure of the structure of
my
tables and how they relate. Can you help me?

 

[dsmith via AccessMonster.com]

Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do a
field observation. They must indicate the location of the observation and the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to each
other on my form. Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

I follow a standard when setting up tables. All tables begin with "Tbl" and
the first field in the table has the name of the table followed by "ID". The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This (these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date. LocationID In
TblLocationObservation is a foreign key and refers to a specific location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself

[quoted text clipped - 38 lines]

 

[Gina Whipp]

DSmith,

Have a look at the relationships in
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3 by Duane. It
will help you see how to set up your relationships. To answer your
question, no you shouls not have ObserverID in tblQuestion. You will need a
*joiner* table. In essence since the questions don't change all you really
want to store is the answers/notes/comments.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do a
field observation. They must indicate the location of the observation and
the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to each
other on my form. Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

I follow a standard when setting up tables. All tables begin with "Tbl"
and
the first field in the table has the name of the table followed by "ID".
The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This (these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date. LocationID
In
TblLocationObservation is a foreign key and refers to a specific location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself

[quoted text clipped - 38 lines]

my
tables and how they relate. Can you help me?

 

[Steve]

Ignore Ms. Whipp's response; it's wrong and will just confuse you more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and foreign keys
for your relationships. In other words, connect each fieldname ending in
"ID" in a table with the same field name in the table that defines the
details of that fieldname. For example, connect ObserverID in TblObservation
to ObserverID in TblObserver.

Steve

Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do a
field observation. They must indicate the location of the observation and
the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to each
other on my form. Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

I follow a standard when setting up tables. All tables begin with "Tbl"
and
the first field in the table has the name of the table followed by "ID".
The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This (these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date. LocationID
In
TblLocationObservation is a foreign key and refers to a specific location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself

[quoted text clipped - 38 lines]

my
tables and how they relate. Can you help me?

 

[Gina Whipp]

Steve,

Huh? I never said the tables were wrong. DSmith has Questions that don't
change and therefore adding ObserverID to tblQuestions would be an issue and
not work properly. Why would that information be wrong? I offered a survey
database example that gives a wonderful example of how a survey would set up
AND gave an example of how you deal with questions that don't change. And
while that database might be confusing to you let's give DSmith a change to
examine and make that determination.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Ignore Ms. Whipp's response; it's wrong and will just confuse you more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and foreign
keys for your relationships. In other words, connect each fieldname ending
in "ID" in a table with the same field name in the table that defines the
details of that fieldname. For example, connect ObserverID in
TblObservation to ObserverID in TblObserver.

Steve

Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do a
field observation. They must indicate the location of the observation and
the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to
each
other on my form. Should I have an ObserverID fk in tblQuestion since
each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

I follow a standard when setting up tables. All tables begin with "Tbl"
and
the first field in the table has the name of the table followed by "ID".
The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the
field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This
(these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date. LocationID
In
TblLocationObservation is a foreign key and refers to a specific location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use)
in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse myself
[quoted text clipped - 38 lines]
my
tables and how they relate. Can you help me?

 

[Steve]

Firstly, the OP's tables are correctly designed. Why would you recommend he
look at a survey database? Secondly, you said "You will need a *joiner*
table. Again, the OP's tables are correctly designed. He doesn't need any
more tables!!! Your recommendation was completely erroneous and would only
serve to confuse the OP further.

Steve,

Huh? I never said the tables were wrong. DSmith has Questions that don't
change and therefore adding ObserverID to tblQuestions would be an issue
and not work properly. Why would that information be wrong? I offered a
survey database example that gives a wonderful example of how a survey
would set up AND gave an example of how you deal with questions that don't
change. And while that database might be confusing to you let's give
DSmith a change to examine and make that determination.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Ignore Ms. Whipp's response; it's wrong and will just confuse you more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and foreign
keys for your relationships. In other words, connect each fieldname
ending in "ID" in a table with the same field name in the table that
defines the details of that fieldname. For example, connect ObserverID in
TblObservation to ObserverID in TblObserver.

Steve

Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do
a
field observation. They must indicate the location of the observation
and the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to
each
other on my form. Should I have an ObserverID fk in tblQuestion since
each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

Steve wrote:
I follow a standard when setting up tables. All tables begin with "Tbl"
and
the first field in the table has the name of the table followed by "ID".
The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the
field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This
(these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list of
safety observations by a specific observer on a specific date.
LocationID In
TblLocationObservation is a foreign key and refers to a specific
location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is
a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use)
in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse
myself
[quoted text clipped - 38 lines]
my
tables and how they relate. Can you help me?

 

[Gina Whipp]

Steve,

I guess you missed this question then...

"Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?"

....that question is what I was replying to. I did not mention anything
about redesign or wrong tables. I did mention a table to join Observer and
AnswersToQuestions because of the question posted but I guess you missed
that also.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Firstly, the OP's tables are correctly designed. Why would you recommend
he look at a survey database? Secondly, you said "You will need a *joiner*
table. Again, the OP's tables are correctly designed. He doesn't need any
more tables!!! Your recommendation was completely erroneous and would only
serve to confuse the OP further.

Steve,

Huh? I never said the tables were wrong. DSmith has Questions that
don't change and therefore adding ObserverID to tblQuestions would be an
issue and not work properly. Why would that information be wrong? I
offered a survey database example that gives a wonderful example of how a
survey would set up AND gave an example of how you deal with questions
that don't change. And while that database might be confusing to you
let's give DSmith a change to examine and make that determination.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

Ignore Ms. Whipp's response; it's wrong and will just confuse you more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and foreign
keys for your relationships. In other words, connect each fieldname
ending in "ID" in a table with the same field name in the table that
defines the details of that fieldname. For example, connect ObserverID
in TblObservation to ObserverID in TblObserver.

Steve


Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they do
a
field observation. They must indicate the location of the observation
and the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to
each
other on my form. Should I have an ObserverID fk in tblQuestion since
each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

Steve wrote:
I follow a standard when setting up tables. All tables begin with "Tbl"
and
the first field in the table has the name of the table followed by
"ID". The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the
field
name in the database. In subsequent fields, I use the name of the first
field in a table when referring to a record in another table. This
(these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list
of
safety observations by a specific observer on a specific date.
LocationID In
TblLocationObservation is a foreign key and refers to a specific
location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation is
a
foreign key and refers to a specific Procedure (maybe Stair/Ladder Use)
in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse
myself
[quoted text clipped - 38 lines]
my
tables and how they relate. Can you help me?

 

[Steve]

You missed the OP's question!!! He asked "What should my relationships be?"

Steve,

I guess you missed this question then...

"Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?"

...that question is what I was replying to. I did not mention anything
about redesign or wrong tables. I did mention a table to join Observer
and AnswersToQuestions because of the question posted but I guess you
missed that also.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Firstly, the OP's tables are correctly designed. Why would you recommend
he look at a survey database? Secondly, you said "You will need a
*joiner* table. Again, the OP's tables are correctly designed. He doesn't
need any more tables!!! Your recommendation was completely erroneous and
would only serve to confuse the OP further.

Steve,

Huh? I never said the tables were wrong. DSmith has Questions that
don't change and therefore adding ObserverID to tblQuestions would be an
issue and not work properly. Why would that information be wrong? I
offered a survey database example that gives a wonderful example of how
a survey would set up AND gave an example of how you deal with questions
that don't change. And while that database might be confusing to you
let's give DSmith a change to examine and make that determination.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

Ignore Ms. Whipp's response; it's wrong and will just confuse you more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and foreign
keys for your relationships. In other words, connect each fieldname
ending in "ID" in a table with the same field name in the table that
defines the details of that fieldname. For example, connect ObserverID
in TblObservation to ObserverID in TblObserver.

Steve


Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they
do a
field observation. They must indicate the location of the observation
and the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to
each
other on my form. Should I have an ObserverID fk in tblQuestion since
each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

Steve wrote:
I follow a standard when setting up tables. All tables begin with
"Tbl" and
the first field in the table has the name of the table followed by
"ID". The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the
field
name in the database. In subsequent fields, I use the name of the
first
field in a table when referring to a record in another table. This
(these)
is (are) foreign keys. For example, In TblObservation, ObserverID is a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list
of
safety observations by a specific observer on a specific date.
LocationID In
TblLocationObservation is a foreign key and refers to a specific
location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation
is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder
Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse
myself
[quoted text clipped - 38 lines]
my
tables and how they relate. Can you help me?

 

[Gina Whipp]

You OBVIOUSLY did not read my reply... I made reference to the reviewing
the Relationships in the database I recommended for assistance in setting
them up.

This is going no where... You are looking for a reason to argue and I was
simply answering a post.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

You missed the OP's question!!! He asked "What should my relationships
be?"

Steve,

I guess you missed this question then...

"Should I have an ObserverID fk in tblQuestion since each
observer must address all 20 questions with each observation?"

...that question is what I was replying to. I did not mention anything
about redesign or wrong tables. I did mention a table to join Observer
and AnswersToQuestions because of the question posted but I guess you
missed that also.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

Firstly, the OP's tables are correctly designed. Why would you recommend
he look at a survey database? Secondly, you said "You will need a
*joiner* table. Again, the OP's tables are correctly designed. He
doesn't need any more tables!!! Your recommendation was completely
erroneous and would only serve to confuse the OP further.


Steve,

Huh? I never said the tables were wrong. DSmith has Questions that
don't change and therefore adding ObserverID to tblQuestions would be
an issue and not work properly. Why would that information be wrong?
I offered a survey database example that gives a wonderful example of
how a survey would set up AND gave an example of how you deal with
questions that don't change. And while that database might be
confusing to you let's give DSmith a change to examine and make that
determination.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

Ignore Ms. Whipp's response; it's wrong and will just confuse you
more!

Your tables are correct!!!!!

Read my previous response and just connect the primary keys and
foreign keys for your relationships. In other words, connect each
fieldname ending in "ID" in a table with the same field name in the
table that defines the details of that fieldname. For example, connect
ObserverID in TblObservation to ObserverID in TblObserver.

Steve


Thanks Steve and Gina
my table structure is below:
There are 20 questions that each observer must review each time they
do a
field observation. They must indicate the location of the observation
and the
results of the review, i.e. safe, at risk, or na. What should my
relationships be? I'm having problems getting the tables to relate to
each
other on my form. Should I have an ObserverID fk in tblQuestion since
each
observer must address all 20 questions with each observation?

TblQuestion
QstnID
QstnText
QstnType

TblObserver
ObserverID
FirstName
LastName
ObserverDept

TblLocation
LocationID
Location

TblObservation
ObservationID
ObservationDate
ObserverID

TblLocationObservation
LocationObservationID
ObservationID
LocationID
QstnID
LocationObservation

Steve wrote:
I follow a standard when setting up tables. All tables begin with
"Tbl" and
the first field in the table has the name of the table followed by
"ID". The
data type is autonumber. This is the primary key of each record and
identifies the table where the record is stored any time you see the
field
name in the database. In subsequent fields, I use the name of the
first
field in a table when referring to a record in another table. This
(these)
is (are) foreign keys. For example, In TblObservation, ObserverID is
a
foreign key and refers to a specific observer in TblObserver. In
TblLocationObservation, ObservationID is a foreign key that relates a
record
to a specific observation in TblObservation. Thus you can have a list
of
safety observations by a specific observer on a specific date.
LocationID In
TblLocationObservation is a foreign key and refers to a specific
location
(maybe Dept X) in Tbllocation. ProcedureID In TblLocationObservation
is a
foreign key and refers to a specific Procedure (maybe Stair/Ladder
Use) in
TblProcedure.

Steve
(e-mail address removed)

Thanks Steve,
I made some progress but I think I've managed to totally confuse
myself
[quoted text clipped - 38 lines]
my
tables and how they relate. Can you help me?

 

[Keith Wilby]

You are looking for a reason to argue and I was simply answering a post.

Don't try to engage in a battle of wits with an un-armed opponent.

 

[Arno R]

Firstly, the OP's tables are correctly designed. Why would you recommend he look at a
survey database? Secondly, you said "You will need a *joiner* table. Again, the OP's
tables are correctly designed. He doesn't need any more tables!!! Your recommendation
was completely erroneous and would only serve to confuse the OP further.

Hi $teve, why are you constantly making a fool of yourself ??
Simply put: Gina is right and you are not... You are acting very childish *again*
Also: You are simply not understanding the question and/or not reading properly...
(as usual)

OP asked
"Should I have an ObserverID fk in tblQuestion since each observer must address all 20 questions with each observation?"
The answer has been given by Gina..
"no you shouls not have ObserverID in tblQuestion"
This is a correct answer while you state that it is wrong...! (only typo there)

The OP tells us: "Supervisors will observe procedures and mark them as "safe" or "at-risk"
So we need a *joiner* table to collect these observations. Gina is right on this...
This *joiner* is your proposed TblLocationObservation I guess.

BUT:
The OP's tables (you mean yours??) are *not* quite right...
The proposed table TblObservation as such is completely *nonsense*!
You are storing *what* here?? Date and Observer... Related to what ??
You are missing Location??

Maybe the ObservationDate from TblObservation easily be stored in the table TblLocationObservation.
Also things like ObservationTime and such...
Well to be honest... it depends on whether we need the day-time per procedure that we check....
But we definitely don't need the tblObservation as you proposed....
confusing and erroneous indeed....


To the OP:
==> I would consider each and every answer given by $teve as 'At-Risk'
==> I would consider each and every answer given by Gina's as "Safe"

Also I would try to give *meaningful* names to the fields.
That can be very helpful to 'understand' the information we are gathering.

The supposed fieldname 'LocationObservation' does indeed serve to confuse...
Why not use ProcedureObservation instead ?

Regards, Arno R

 

[Gina Whipp]

<smile>

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[dsmith via AccessMonster.com]

Thanks guys,
I finally got it working...I appreciate your willingness to help those of us
trying to learn. Access is a great program...hopefully I will learn enough to
share in the future.