Is is possible to count the Y/N's in a record?

[Guest]

Hello,

I have a table that has 31 Y/N fields. While it sounds crazy, it is used to
track medication taken on each day of the month.

Is it possible to count how many Y's are in a record and have it display
somewhere?
The eventual goal is to calculate the percentage of Y's in an entire month.

Optimistic,

John.

 

[John Vinson]

Hello,

I have a table that has 31 Y/N fields. While it sounds crazy, it is used to
track medication taken on each day of the month.

It's still a misdesigned table. What about multiple doses per day?
What about spanning across months - suppose you wanted to find how
many doses *in the past two weeks*?

A tall-thin table with fields for DoseDate, perhaps MedicationID and
Amount, would be a much better design. You can reconstruct this
'spreadsheet' for display purposes using a Crosstab Query if you wish;
and your count of yesses is trivially easy.

Is it possible to count how many Y's are in a record and have it display
somewhere?

Since Yes is stored as -1 and No as 0, you can use an expression

-([Day1] + [Day2] + [Day3] + [Day4] ... etc. through [Day31])

The eventual goal is to calculate the percentage of Y's in an entire month.

Divide it by the number of days in the month... but since I don't know
how you're identifying which month this record pertains to I can't
advise. Again, very easy with the normalized design.

John W. Vinson[MVP]

 

[Guest]

Hey John,

Thanks for your reply.

Each record is a particular medication and dose, so it's really just a did
you take it our not issue.

I will use the formula you suggested...many thanks!

John.

Hello,

I have a table that has 31 Y/N fields. While it sounds crazy, it is used to
track medication taken on each day of the month.

It's still a misdesigned table. What about multiple doses per day?
What about spanning across months - suppose you wanted to find how
many doses *in the past two weeks*?

A tall-thin table with fields for DoseDate, perhaps MedicationID and
Amount, would be a much better design. You can reconstruct this
'spreadsheet' for display purposes using a Crosstab Query if you wish;
and your count of yesses is trivially easy.

Is it possible to count how many Y's are in a record and have it display
somewhere?

Since Yes is stored as -1 and No as 0, you can use an expression

-([Day1] + [Day2] + [Day3] + [Day4] ... etc. through [Day31])

The eventual goal is to calculate the percentage of Y's in an entire month.

Divide it by the number of days in the month... but since I don't know
how you're identifying which month this record pertains to I can't
advise. Again, very easy with the normalized design.

John W. Vinson[MVP]

 

[John Vinson]

Hey John,

Thanks for your reply.

Each record is a particular medication and dose, so it's really just a did
you take it our not issue.

I would still STRONGLY - vehemently even - recommend normalizing your
table structure. This wide-flat structure *will* (not may) cause you
grief in the future.

John W. Vinson[MVP]

 

[GasMan]

I would echo Johns' sentiments, also then there would be no reason to
log the No answers, just have records for the Yes answers.

Hey John,

Thanks for your reply.

Each record is a particular medication and dose, so it's really just a did
you take it our not issue.

I will use the formula you suggested...many thanks!

John.

Hello,

I have a table that has 31 Y/N fields. While it sounds crazy, it is used to
track medication taken on each day of the month.

It's still a misdesigned table. What about multiple doses per day?
What about spanning across months - suppose you wanted to find how
many doses *in the past two weeks*?

A tall-thin table with fields for DoseDate, perhaps MedicationID and
Amount, would be a much better design. You can reconstruct this
'spreadsheet' for display purposes using a Crosstab Query if you wish;
and your count of yesses is trivially easy.

Is it possible to count how many Y's are in a record and have it display
somewhere?

Since Yes is stored as -1 and No as 0, you can use an expression

-([Day1] + [Day2] + [Day3] + [Day4] ... etc. through [Day31])

The eventual goal is to calculate the percentage of Y's in an entire month.

Divide it by the number of days in the month... but since I don't know
how you're identifying which month this record pertains to I can't
advise. Again, very easy with the normalized design.

John W. Vinson[MVP]

Please remove obvious from email address if emailing.

 

[Chris2]

Hey John,

Thanks for your reply.

Each record is a particular medication and dose, so it's really just a did
you take it our not issue.

I will use the formula you suggested...many thanks!

Access rookie,

I also, strongly, echo the advice of the other respondents. Normalize
your database.

An example:

CREATE TABLE PatientsMedication
(PatientID LONG NOT NULL
,MedicationID LONG NOT NULL
,DosageOccurrence DATETIME NOT NULL
,DosageAmount TEXT(72) NOT NULL
,CONSTRAINT pk_PatenentsMedication
PRIMARY KEY (PatientID, MedicationID, DosageOccurrence)
)

The query to answer the same question as the long involuted expression
the current db-design requires is:

SELECT PM1.PatientID
,PM1.MedicationID
,COUNT(PM1.DosageOccurrence)
FROM PatientsMedication AS PM1
WHERE MONTH(PM1.DosageOccurrence) = MONTH(Date())

That will get you everything for the current month. Simple, easy to
modify to get the answers to other questions, and can handle multiple
dosages per day, variable dosage sizes, multiple medications, and
multiple patients; and also drops messing around with the -1/0 values
for the boolean data-type.


Sincerely,

Chris O.

 

[Guest]

Hey guys,

Thank you so much for your advice; there is a saying that he who does not
listen to the wisdom of the elders shall dwell with pink penguins on the
shores of Peru...(not really...

A lot of how I was approaching the database was based on how things work
here; I will have to revisit a better way to do things. I look forward to
embarking on this great adventure of database normalization!

Thanks again,

Rookie.