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
|
|||
|
|||
Recording the frequency of Yes/No Fields
Hi all,
I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
#2
|
|||
|
|||
Recording the frequency of Yes/No Fields
Instead of a Yes/No field and then a Number field to record how many, could
you get away with just the Number field? If the answer was No, presumably the Number is zero. It seems to me that this would be a better (more reliable) design, as it would prevent the existence of bad data (e.g. where the Yes/No field contains No, but the Number field contains 8.) Having just the Number field avoids that kind of inaccuracy. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
#3
|
|||
|
|||
Recording the frequency of Yes/No Fields
The problem is that I'm somewhat short of ideas on how to make this work.
What kind of data are you collecting and how do you plan to use it? I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. You do not need a frequency field (Nor for that matter a tblFrequency) as your queries can count for you when you need that information. -- Build a little, test a little. "omoluabi" wrote: Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
#4
|
|||
|
|||
Recording the frequency of Yes/No Fields
I'm building a monthly review database for a healthcare company to store
monthly health reports on their patients. I have 6 tables that houses different classes of data. namely tblClient,tblReviewer,tblClientReviewer,tblInciden ts,tblMedicalUpdates and tblMedicationErrors. There is a one to many relationship between tblClientReviewer and tblIncidents,tblMedicalUpdates and tblMedicationErrors. The Yes/No field of interest originated from tblIncidents. Below is a stub that generated the table: INCIDENTS: Any incidents to report this month Yes/No: If yes indicate the type of incident Reportable Behavioral/Psychiatric Frequency {1,2,3,4,5,6,7,8,9,10 or more} Property destruction Frequency {1,2,3,4,5,6,7,8,9,10 or more} Reportable Medical Incident Frequency {1,2,3,4,5,6,7,8,9,10 or more} Elopement Frequency {1,2,3,4,5,6,7,8,9,10 or more} Sexual Aggression Frequency {1,2,3,4,5,6,7,8,9,10 or more} Other Type of Incident- specify Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Abuse Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Neglect Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Exploitation Serious Injury Frequency {1,2,3,4,5,6,7,8,9,10 or more} All the incidents above are fields in my tblIncidents. Now the big question is how to go about representing the frequency of each in the table. Remember we don't need to record the frequency if the incident never took place. "KARL DEWEY" wrote: The problem is that I'm somewhat short of ideas on how to make this work. What kind of data are you collecting and how do you plan to use it? I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. You do not need a frequency field (Nor for that matter a tblFrequency) as your queries can count for you when you need that information. -- Build a little, test a little. "omoluabi" wrote: Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
#5
|
|||
|
|||
Recording the frequency of Yes/No Fields
Thanks Allen, your suggestion makes perfect sense but I forsee that being a
problem when I'm creating the reports. How do I indicate that the incident occured in the report? I'm certain the report query will bring up the corresponding frequency provided the incident occured, however I would like to display a checked checkbox alongside the frequency. How can this be done? Sorry if my questions are too basic, I'm a newbie in this field. "Allen Browne" wrote: Instead of a Yes/No field and then a Number field to record how many, could you get away with just the Number field? If the answer was No, presumably the Number is zero. It seems to me that this would be a better (more reliable) design, as it would prevent the existence of bad data (e.g. where the Yes/No field contains No, but the Number field contains 8.) Having just the Number field avoids that kind of inaccuracy. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. . |
#6
|
|||
|
|||
Recording the frequency of Yes/No Fields
Create a query. In the Criteria row under the number field:
0 That will eliminate all the records where the number as zero. (Presumably you don't have negatives.) Or you can put a text box on the report if you wish. Set its Control Source to: =([YourNumberFieldNameHere] 0) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Thanks Allen, your suggestion makes perfect sense but I forsee that being a problem when I'm creating the reports. How do I indicate that the incident occured in the report? I'm certain the report query will bring up the corresponding frequency provided the incident occured, however I would like to display a checked checkbox alongside the frequency. How can this be done? Sorry if my questions are too basic, I'm a newbie in this field. "Allen Browne" wrote: Instead of a Yes/No field and then a Number field to record how many, could you get away with just the Number field? If the answer was No, presumably the Number is zero. It seems to me that this would be a better (more reliable) design, as it would prevent the existence of bad data (e.g. where the Yes/No field contains No, but the Number field contains 8.) Having just the Number field avoids that kind of inaccuracy. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. . |
#7
|
|||
|
|||
Recording the frequency of Yes/No Fields
Thanks Allen. I will run with that and let you know how it goes.
Thanks for your patience. "Allen Browne" wrote: Create a query. In the Criteria row under the number field: 0 That will eliminate all the records where the number as zero. (Presumably you don't have negatives.) Or you can put a text box on the report if you wish. Set its Control Source to: =([YourNumberFieldNameHere] 0) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Thanks Allen, your suggestion makes perfect sense but I forsee that being a problem when I'm creating the reports. How do I indicate that the incident occured in the report? I'm certain the report query will bring up the corresponding frequency provided the incident occured, however I would like to display a checked checkbox alongside the frequency. How can this be done? Sorry if my questions are too basic, I'm a newbie in this field. "Allen Browne" wrote: Instead of a Yes/No field and then a Number field to record how many, could you get away with just the Number field? If the answer was No, presumably the Number is zero. It seems to me that this would be a better (more reliable) design, as it would prevent the existence of bad data (e.g. where the Yes/No field contains No, but the Number field contains 8.) Having just the Number field avoids that kind of inaccuracy. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. . . |
#8
|
|||
|
|||
Recording the frequency of Yes/No Fields
Thanks Allen, I will run with this and let you know how thing go. Thnks for your patience. "Allen Browne" wrote: Create a query. In the Criteria row under the number field: 0 That will eliminate all the records where the number as zero. (Presumably you don't have negatives.) Or you can put a text box on the report if you wish. Set its Control Source to: =([YourNumberFieldNameHere] 0) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Thanks Allen, your suggestion makes perfect sense but I forsee that being a problem when I'm creating the reports. How do I indicate that the incident occured in the report? I'm certain the report query will bring up the corresponding frequency provided the incident occured, however I would like to display a checked checkbox alongside the frequency. How can this be done? Sorry if my questions are too basic, I'm a newbie in this field. "Allen Browne" wrote: Instead of a Yes/No field and then a Number field to record how many, could you get away with just the Number field? If the answer was No, presumably the Number is zero. It seems to me that this would be a better (more reliable) design, as it would prevent the existence of bad data (e.g. where the Yes/No field contains No, but the Number field contains 8.) Having just the Number field avoids that kind of inaccuracy. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "omoluabi" wrote in message ... Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. . . |
#9
|
|||
|
|||
Recording the frequency of Yes/No Fields
All the incidents above are fields in my tblIncidents.
I would suggest a different structure. Have a table for incident type like this -- IncidentType -- TypeID - Autonumber - primary key Incident - text tblIncidents -- IncidentID - Autonumber - primary key Type - number long integer - foreign key related to [IncidentType].[TypeID] IncidentDate - DateTime ClientID - number long integer - foreign key related to [Client].[ClientID] Remarks - memo - to record 'Other Type of Incident- specify' -- Build a little, test a little. "omoluabi" wrote: I'm building a monthly review database for a healthcare company to store monthly health reports on their patients. I have 6 tables that houses different classes of data. namely tblClient,tblReviewer,tblClientReviewer,tblInciden ts,tblMedicalUpdates and tblMedicationErrors. There is a one to many relationship between tblClientReviewer and tblIncidents,tblMedicalUpdates and tblMedicationErrors. The Yes/No field of interest originated from tblIncidents. Below is a stub that generated the table: INCIDENTS: Any incidents to report this month Yes/No: If yes indicate the type of incident Reportable Behavioral/Psychiatric Frequency {1,2,3,4,5,6,7,8,9,10 or more} Property destruction Frequency {1,2,3,4,5,6,7,8,9,10 or more} Reportable Medical Incident Frequency {1,2,3,4,5,6,7,8,9,10 or more} Elopement Frequency {1,2,3,4,5,6,7,8,9,10 or more} Sexual Aggression Frequency {1,2,3,4,5,6,7,8,9,10 or more} Other Type of Incident- specify Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Abuse Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Neglect Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Exploitation Serious Injury Frequency {1,2,3,4,5,6,7,8,9,10 or more} All the incidents above are fields in my tblIncidents. Now the big question is how to go about representing the frequency of each in the table. Remember we don't need to record the frequency if the incident never took place. "KARL DEWEY" wrote: The problem is that I'm somewhat short of ideas on how to make this work. What kind of data are you collecting and how do you plan to use it? I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. You do not need a frequency field (Nor for that matter a tblFrequency) as your queries can count for you when you need that information. -- Build a little, test a little. "omoluabi" wrote: Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
#10
|
|||
|
|||
Recording the frequency of Yes/No Fields
Thank heavens for people like you, I never thought i could do it that way. It
poses another question though. How can I get the frequency of each Incident type for a patient? Is it possible to combine your approach with Allen Browne's solution? "KARL DEWEY" wrote: All the incidents above are fields in my tblIncidents. I would suggest a different structure. Have a table for incident type like this -- IncidentType -- TypeID - Autonumber - primary key Incident - text tblIncidents -- IncidentID - Autonumber - primary key Type - number long integer - foreign key related to [IncidentType].[TypeID] IncidentDate - DateTime ClientID - number long integer - foreign key related to [Client].[ClientID] Remarks - memo - to record 'Other Type of Incident- specify' -- Build a little, test a little. "omoluabi" wrote: I'm building a monthly review database for a healthcare company to store monthly health reports on their patients. I have 6 tables that houses different classes of data. namely tblClient,tblReviewer,tblClientReviewer,tblInciden ts,tblMedicalUpdates and tblMedicationErrors. There is a one to many relationship between tblClientReviewer and tblIncidents,tblMedicalUpdates and tblMedicationErrors. The Yes/No field of interest originated from tblIncidents. Below is a stub that generated the table: INCIDENTS: Any incidents to report this month Yes/No: If yes indicate the type of incident Reportable Behavioral/Psychiatric Frequency {1,2,3,4,5,6,7,8,9,10 or more} Property destruction Frequency {1,2,3,4,5,6,7,8,9,10 or more} Reportable Medical Incident Frequency {1,2,3,4,5,6,7,8,9,10 or more} Elopement Frequency {1,2,3,4,5,6,7,8,9,10 or more} Sexual Aggression Frequency {1,2,3,4,5,6,7,8,9,10 or more} Other Type of Incident- specify Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Abuse Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Neglect Frequency {1,2,3,4,5,6,7,8,9,10 or more} Alleged Exploitation Serious Injury Frequency {1,2,3,4,5,6,7,8,9,10 or more} All the incidents above are fields in my tblIncidents. Now the big question is how to go about representing the frequency of each in the table. Remember we don't need to record the frequency if the incident never took place. "KARL DEWEY" wrote: The problem is that I'm somewhat short of ideas on how to make this work. What kind of data are you collecting and how do you plan to use it? I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. You do not need a frequency field (Nor for that matter a tblFrequency) as your queries can count for you when you need that information. -- Build a little, test a little. "omoluabi" wrote: Hi all, I'm designing a database that requires users to specify the number of times a Yes/No field occurs. This number is not required if the choice is No but if Yes, I plan to create a drop down menu for the user to specify the number of times the choice occured. My first thought is to create a Frequency field for each choice but then I thought there must be a better way to do it. I came up with an idea to create a Frequency table (tblFrequency) described below: tblFrequency: Field: FreqID Field: Freq I'm planning to make FreqID a foreign key on my Incidents table which contains the choices (Yes/No) fields. The problem is that I'm somewhat short of ideas on how to make this work. Thanks for your patience. |
|
Thread Tools | |
Display Modes | |
|
|