Multi-Column Frequencies

  • Thread starter Thread starter Mathew
  • Start date Start date
M

Mathew

Hello

I have inherited a database that contains three separate
fields for a patient diagnosis (primary, secondary, and
tertiary). I would like to get a count of every instance
of a diagnosis code. (See Below for Example)

Typically I would use a union query for this type of
analysis but the sheer number of entries and the number of
possible diagnoses (over 10k) makes that impossible.
Any suggestions?
Thanks so Much
Mathew

Example Table
ID Prim Secon Tert
1 311 298.85 17.1
2 17.1 311
3 298.85 311 185.42
4 311 185.42 116.21
5 311 185.42
Example Results
Diagnosis Count
311 5
298.85 2
17.1 2
185.42 3
116.21 1
 
Is it possible to normalise the table so that there is only
one diagnosis per row wtih an extra column indicating which
diagnosis (primary, secondary, or tertiary).

Hope This Helps
Gerald Stanley MCSD
 
Create a table with fields:
ID Number foreign key?
DiagType Number 1, 2, or 3 (for prim, sec, tert).
Diag Number the value in your table.

Run an update query 3 times, to add the values from the 3 columns.

You now have a normalized table, that can be queried just as you requested.
 
Back
Top