A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Recording the frequency of Yes/No Fields



 
 
Thread Tools Display Modes
  #11  
Old October 24th, 2009, 01:38 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Recording the frequency of Yes/No Fields

"Allen Browne" wrote in
:

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.


The other nice thing about that is you don't lose the Boolean test.
Instead of testing Yes/No field = True, you'd test Count 0, or,
for that matter, Count Not False. Of course you'd want to have the
default value of the Count field by 0, but if you needed to know it
hadn't been filled out (such that you didn't want the default value
to be 0), you could test ((Not IsNull(Count)) AND (Count False)).
This will work because (False And Null) returns False, so if you
want Null or 0 to return False it would work.

(Not IsNull(Null)) AND (Null False) = False (False And Null)

(Not IsNull(0)) AND (0 False) = False (False And False)

(Not IsNull(1)) AND (1 False) = True (True And True)

All other numbers behave the same as 1, of course.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #12  
Old October 24th, 2009, 03:34 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Recording the frequency of Yes/No Fields

Access can count for you. You can use a 'Totals' query or a 'Crosstab'
query. Search Help on those.

I am not familar with Allen Browne's solution.

--
Build a little, test a little.


"omoluabi" wrote:

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.