Prev: How big a record size is too big from a performance standpoint
Next: Collecting form data offline
From: dsmith via AccessMonster.com on 25 Nov 2009 10:41
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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

From: Steve on 26 Nov 2009 14:01
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
santus(a)penn.com




"dsmith via AccessMonster.com" <u56186(a)uwe> wrote in message
news:9fa5fe6b707eb(a)uwe...
>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?
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
>


From: dsmith via AccessMonster.com on 27 Nov 2009 19:22
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.
Steve wrote:
>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
>santus(a)penn.com
>
>>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?

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

From: Steve on 29 Nov 2009 12:38
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

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


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

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


 |  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