Help with counting please

[Sue]

tblPatient contains the following fields:
DiagnosisID (foreign key from tblDiagnosis)
DiseaseStatusID (foreign key from tblDiseaseStatus)
ScreenStatusID (foreign key from tblScreenStatus)

I'm using this to track the diagnosis and disease status (i.e. no prior
treatment, relapsed disease, refractory disease) of patients as well as
whether they were screened for, consented to, or enrolled in research
protocols.

I want a count of the number of patients who have a given disease, then the
number of patients whose for each disease whose disease is new vs relapsed,
etc., then the number of patients in each of the above categories who were
screened, consented, or went on protocol.

Not sure how to start.

Help, please?

 

[Lynn Trapp]

To start with, I'm a bit puzzled by your table structure. Why would the
patient table have foreign keys to the other three tables? As for your
query, I think we will need to see something about hte structure of the
other tables to give you a good answer.

 

[John W. Vinson]

tblPatient contains the following fields:
DiagnosisID (foreign key from tblDiagnosis)
DiseaseStatusID (foreign key from tblDiseaseStatus)
ScreenStatusID (foreign key from tblScreenStatus)

Um????

This appears to be incorrectly set up. Surely each Patient may have many
Diagnoses, and each Diagnosis can apply to many Patients... a many to many
relationship. What's in tblDiagnosis? Is this a list of all the valid
diagnosis codes and condition names?

Similarly for the other tables... what is the actual structure of your tables?

The foreign key should - MUST - be in the "many" side table. If you have a
DiagnosisID field in the Patient table then you're saying that the patient can
have either zero or one conditions, never more. That would be nice, but I
would need four DiagnosisID's myself, right now!

John W. Vinson [MVP]

 

[Sue]

Hi John, and thanks for your time.
What I'm trying to track here is the diagnosis (and disease stage) of each
patient at the time of their initial visit to our program. Thus each patient
may have only one diagnosis & disease stage. So the structure is:

tblDisease
DiseaseID (autonumber)
Disease (text)

tblDiseaseStatus
DiseaseStatusID (autonumber)
DiseaseStatus

In tblPatients, DiseaseID is a number field, and in the form I use for data
entry (and the query based on tblPatients) there is a combo box which relates
back to tblDisease so, for example, Acute Myeloid Leukemia can be chosen.
Similarly in tblPatients, DiseaseStatusID is a number field, and in the form
I use for data entry (and in the query based on tblPatients) there is a combo
box which relates back to tblDiseaseStatus so that, for example, Relapsed or
No Prior Treatment can be selected.

I hope that makes it more clear.

What I want is to be able to tally the number of patients with each disease
by disease status, ie. the number of non-Hodgkins lymphoma patients who have
relapsed disease, the number of multiple myeloma patients who have had no
prior therapy, etc.

Your help will be greatly appreciated!

 

[Sue]

I've got 179 diagnoses in tblDiagnosis.
I've got 8 disease status' in tblDiseaseStatus.
I've got 14 screen status' in tblScreenStatus.

I'm trying to limit data entry errors & ensure data consistency.

The other tables each have 2 fields - the primary key is an autonumber, and
the diagnosis, disease status, and screen status are each text fields.

Thank you for your help.

 

[John W. Vinson]

What I want is to be able to tally the number of patients with each disease
by disease status, ie. the number of non-Hodgkins lymphoma patients who have
relapsed disease, the number of multiple myeloma patients who have had no
prior therapy, etc.

Ok... a Totals query will do this for you.

Create a Query joining the patients table to the Diagnosis table (by
DiagnosisID of course), and to the Stages table.

Select the Disease and DiseaseStage fields, and the PatientID from the patient
table.

Change it to a Totals query using the Greek Sigma icon.

Leave the default Group By on Disease and DiseaseStage, and use Count on the
PatientID.

John W. Vinson [MVP]

 

[Sue]

Hello all - and thanks for all your suggestions.
I'm actually taking a hard luck at the structure of my db at the moment - I
think most of the problem I'm having relates to a crying need for re-design.
But I've saved your suggestion, John, as I think you've got the key.
Again, thanks!