If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Setting up Observation Database
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/For...esign/200911/1 |
#3
|
|||
|
|||
Setting up Observation Database
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 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 |
#4
|
|||
|
|||
Setting up Observation Database
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 "dsmith via AccessMonster.com" u56186@uwe wrote in message news:9fc3b1dc79144@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 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 |
#5
|
|||
|
|||
Setting up Observation Database
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/Ap****ueBadWord.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@uwe wrote in message news:9fc3b1dc79144@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 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 |
#6
|
|||
|
|||
Setting up Observation Database
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 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 |
#7
|
|||
|
|||
Setting up Observation Database
DSmith,
Have a look at the relationships in http://www.rogersaccesslibrary.com/f...osts.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@uwe wrote in message news:9fde7f6b399a5@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 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 |
#8
|
|||
|
|||
Setting up Observation Database
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@uwe wrote in message news:9fde7f6b399a5@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 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 |
#9
|
|||
|
|||
Setting up Observation Database
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" wrote in message ... 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@uwe wrote in message news:9fde7f6b399a5@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 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 |
#10
|
|||
|
|||
Setting up Observation Database
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" wrote in message ... 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" wrote in message ... 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@uwe wrote in message news:9fde7f6b399a5@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 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 |
|
Thread Tools | |
Display Modes | |
|
|