Reporting a Not null values (1 Viewer)

[tezread]

If i have a table with most of the fields in ti being list boxes (yes/no) is there any way I can show only the fields with a 'yes' in a report.

One of the fields is

Discharge_meds_digoxin

If the list bix option is 'yes' I want to have in the report 'Digoxin' as opposed to 'yes'?

I have thought of a long winded approach i.e.:

Digoxin: IIf([Discharge_meds_digoxin] = "Yes","Digoxin","")


but with so many fields this would take ages in a query surely

 

[CBrighton]

Sounds like the data would be better split into 2 tables in a one to many relationship - 1 person to multiple meds (I assume that's what this is).

That way you could easily return a list of the meds which apply to a specific peron.

 

[vbaInet]

You can use:

Digoxin: IIf([Discharge_meds_digoxin] = -1, "Digoxin", Null)

That would be more optimised. But like CBrighton highlighted, your current structure may need a re-think.

 

[tezread]

I was thinking about reorganizing the table structure as well! Honest. I might do that a) that will work and b)it will normalize the data to a greater extent

 

[tezread]

Sounds like the data would be better split into 2 tables in a one to many relationship - 1 person to multiple meds (I assume that's what this is).

That way you could easily return a list of the meds which apply to a specific peron.

As it stands I currently have two tables -

tblPatient
tblEpisode

tblEpisode has data pertaining to treatment that patient was given in an episode

But should I break that tblEpisode down even further i.e.

Clinicalevent
Treatmentoptions
Dischargemeds
Initialdrugs

i have attached current db

etc?

Best wishes

 

[CBrighton]

An episodes table is fine, it's like an invoice table in that it has data for that specific interaction.

But if you record data which isn't patient data and isn't episode data (like medical history, meds issued, etc) I'd put them in their own table, linked to the patients table (I imagine the same way the episodes table is linked to it).

:edit:

I'd expect a number of reference tables too, like a table with all the meds (this could just be the names to use in any comboboxes or a full table including cost, item code, dosage, warnings, etc), etc.