Prev: How big a record size is too big from a performance standpoint
Next: Collecting form data offline
From: dsmith via AccessMonster.com on 29 Nov 2009 22:32
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
>santus(a)penn.com
>
>> 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?

--
Message posted via http://www.accessmonster.com

From: Gina Whipp on 29 Nov 2009 23:25
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

"dsmith via AccessMonster.com" <u56186(a)uwe> wrote in message
news:9fde7f6b399a5(a)uwe...
> 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
>>santus(a)penn.com
>>
>>> 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?
>
> --
> Message posted via http://www.accessmonster.com
>


From: Steve on 30 Nov 2009 10:44
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


"dsmith via AccessMonster.com" <u56186(a)uwe> wrote in message
news:9fde7f6b399a5(a)uwe...
> 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
>>santus(a)penn.com
>>
>>> 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?
>
> --
> Message posted via http://www.accessmonster.com
>


From: Gina Whipp on 30 Nov 2009 11:30
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

"Steve" <notmyemail(a)address.com> wrote in message
news:ObK92PdcKHA.744(a)TK2MSFTNGP05.phx.gbl...
> 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
>
>
> "dsmith via AccessMonster.com" <u56186(a)uwe> wrote in message
> news:9fde7f6b399a5(a)uwe...
>> 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
>>>santus(a)penn.com
>>>
>>>> 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?
>>
>> --
>> Message posted via http://www.accessmonster.com
>>
>
>


From: Steve on 30 Nov 2009 15:32
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.


"Gina Whipp" <NotInterested(a)InViruses.com> wrote in message
news:ONqe6pdcKHA.4724(a)TK2MSFTNGP05.phx.gbl...
> 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
>
> "Steve" <notmyemail(a)address.com> wrote in message
> news:ObK92PdcKHA.744(a)TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>> "dsmith via AccessMonster.com" <u56186(a)uwe> wrote in message
>> news:9fde7f6b399a5(a)uwe...
>>> 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
>>>>santus(a)penn.com
>>>>
>>>>> 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?
>>>
>>> --
>>> Message posted via http://www.accessmonster.com
>>>
>>
>>
>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: How big a record size is too big from a performance standpoint
Next: Collecting form data offline