Reports and data presentation

[CK]

Hi
I wonder whether anyone can help me with a report problem.

Creating a report from patients, symptoms, blood tests, diagnoses, etc.
Each patient can have many symptoms, but only one blood test. The problem
with the report is that for each patient, the blood test result (for example)
is repeated alongside each symptom. The same story of redundant repetition
goes for diagnoses and other fields with a many to many relationship.

How should I create the report such that the data is independent of one
another?
Thus for each patient, although they might have three symptoms presented,
the blood test is only shown once.
I have tried experimenting with variations of grouping levels but still find
repetition. Once this hurdle is cleared I will want to use the grouping
levels to show the data in other ways but because there are several
many-to-many fields this doesn't seem the way forward.

I would welcome any ideas on this.

 

[Klatuu]

Use the Hide Duplicates property of the controls where you don't want to
present repeating values. If you set this property to Yes, a value will not
show if it is the same value as the previous record.

 

[jahoobob via AccessMonster.com]

Hopefully you have at least two tables, one with patient info including blodd
test and another with symptoms and diagnosis. You will need to link these
One-to-many via the patients ID (PK.)
You can then base your report on the patient table and place a sub-report
with symptome-diagnosis (check to see that the sub-report is linked to the
repor by the ID.

 

[Klatuu]

Not really necessary, see my previous post.

Hopefully you have at least two tables, one with patient info including blodd
test and another with symptoms and diagnosis. You will need to link these
One-to-many via the patients ID (PK.)
You can then base your report on the patient table and place a sub-report
with symptome-diagnosis (check to see that the sub-report is linked to the
repor by the ID.

 

[jahoobob via AccessMonster.com]

Yes, but...good database design says you don't repeat info in a table. One
table would look lik this:
1 Patient Blood Symptom1 Diagnosis1
2 Patient Blood Symptom2 Diagnosis2
3 Patient Blood Symptom3 Diagnosis3
That's 40%, or more since there is a lot more patient info, of unnecessary
overhead.
I would still go with two tables.

Not really necessary, see my previous post.

Hopefully you have at least two tables, one with patient info including blodd
test and another with symptoms and diagnosis. You will need to link these

[quoted text clipped - 22 lines]

 

[Klatuu]

I certainly agree with you regarding good database design; however, nothing
in the OP mentioned the data is repetative in any tables. In fact, this
sentence:
"The same story of redundant repetition goes for diagnoses and other fields
with a many to many relationship."
indicates she is using correct structure, except she mentions many to many
which could refer to the results of the query.
My assumption is she is using a query to join the tables to provide the data
for the report. If this is the case, a sub report would not be necessary.

If you are still following this thread CK, you input would be appreciated.

Yes, but...good database design says you don't repeat info in a table. One
table would look lik this:
1 Patient Blood Symptom1 Diagnosis1
2 Patient Blood Symptom2 Diagnosis2
3 Patient Blood Symptom3 Diagnosis3
That's 40%, or more since there is a lot more patient info, of unnecessary
overhead.
I would still go with two tables.

Not really necessary, see my previous post.

Hopefully you have at least two tables, one with patient info including blodd
test and another with symptoms and diagnosis. You will need to link these

[quoted text clipped - 22 lines]

I would welcome any ideas on this.

 

[CK]

Thanks.

Firstly my design is based on Patient table with PatientID (PK). Blood tests
are in a separate table but one-to-one relationship. I have a Symptom table
with a many-many relationship via a joint table with SymptomID and PatientID.
Same goes for diagnoses (each patient can have more than one diagnosis).

I tried to base my report on the fields directly from the tables and again
on a query of the same data

Both ways give me:

PatientID Symptom BloodTest Diagnosis
1 S1 BT1 D1
1 S2 BT1 D1

2 S1 BT2 D2
2 S1 BT2 D3
2 S2 BT2 D2
2 S2 BT2 D3
etc.

if I use "hide duplicates", it works well on the blood tests, but not on the
others.
It leaves me with big gaps where it has hiddden the duplicates.

I have tried to reduce repititive data by creating the many to many tables
but the query inevitably has repetiton in it, I guess for the same reasons
that the report does. In which case it might be the query which needs
tinkering with...

I certainly agree with you regarding good database design; however, nothing
in the OP mentioned the data is repetative in any tables. In fact, this
sentence:
"The same story of redundant repetition goes for diagnoses and other fields
with a many to many relationship."
indicates she is using correct structure, except she mentions many to many
which could refer to the results of the query.
My assumption is she is using a query to join the tables to provide the data
for the report. If this is the case, a sub report would not be necessary.

If you are still following this thread CK, you input would be appreciated.

Yes, but...good database design says you don't repeat info in a table. One
table would look lik this:
1 Patient Blood Symptom1 Diagnosis1
2 Patient Blood Symptom2 Diagnosis2
3 Patient Blood Symptom3 Diagnosis3
That's 40%, or more since there is a lot more patient info, of unnecessary
overhead.
I would still go with two tables.

Not really necessary, see my previous post.

Hopefully you have at least two tables, one with patient info including blodd
test and another with symptoms and diagnosis. You will need to link these
[quoted text clipped - 22 lines]

I would welcome any ideas on this.

 

[jahoobob via AccessMonster.com]

I give up!

Thanks.

Firstly my design is based on Patient table with PatientID (PK). Blood tests
are in a separate table but one-to-one relationship. I have a Symptom table
with a many-many relationship via a joint table with SymptomID and PatientID.
Same goes for diagnoses (each patient can have more than one diagnosis).

I tried to base my report on the fields directly from the tables and again
on a query of the same data

Both ways give me:

PatientID Symptom BloodTest Diagnosis
1 S1 BT1 D1
1 S2 BT1 D1

2 S1 BT2 D2
2 S1 BT2 D3
2 S2 BT2 D2
2 S2 BT2 D3
etc.

if I use "hide duplicates", it works well on the blood tests, but not on the
others.
It leaves me with big gaps where it has hiddden the duplicates.

I have tried to reduce repititive data by creating the many to many tables
but the query inevitably has repetiton in it, I guess for the same reasons
that the report does. In which case it might be the query which needs
tinkering with...

I certainly agree with you regarding good database design; however, nothing
in the OP mentioned the data is repetative in any tables. In fact, this

[quoted text clipped - 23 lines]

 

[John Spencer]

As I look see your structure, I don't see any way to relate a diagnosis to a
symptom. That being the case, when you include both tables in a query you
are going to end up with a row for each combination of diagnosis and symptom
for each patient.

I am not saying that the Diagnosis should be related to a symptom in your
structure. I am stating that your structure does not appear to have a way
to return anything other than what it is returning in one query.

You could try using sub-reports to show the data. Or you might investigate
Duane Hookom's generic Concatenate() function.
See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Thanks.

Firstly my design is based on Patient table with PatientID (PK). Blood
tests
are in a separate table but one-to-one relationship. I have a Symptom
table
with a many-many relationship via a joint table with SymptomID and
PatientID.
Same goes for diagnoses (each patient can have more than one diagnosis).

I tried to base my report on the fields directly from the tables and
again
on a query of the same data

Both ways give me:

PatientID Symptom BloodTest Diagnosis
1 S1 BT1 D1
1 S2 BT1 D1

2 S1 BT2 D2
2 S1 BT2 D3
2 S2 BT2 D2
2 S2 BT2 D3
etc.

if I use "hide duplicates", it works well on the blood tests, but not on
the
others.
It leaves me with big gaps where it has hiddden the duplicates.

I have tried to reduce repititive data by creating the many to many tables
but the query inevitably has repetiton in it, I guess for the same reasons
that the report does. In which case it might be the query which needs
tinkering with...

I certainly agree with you regarding good database design; however,
nothing
in the OP mentioned the data is repetative in any tables. In fact, this
sentence:
"The same story of redundant repetition goes for diagnoses and other
fields
with a many to many relationship."
indicates she is using correct structure, except she mentions many to
many
which could refer to the results of the query.
My assumption is she is using a query to join the tables to provide the
data
for the report. If this is the case, a sub report would not be
necessary.

If you are still following this thread CK, you input would be
appreciated.

Yes, but...good database design says you don't repeat info in a table.
One
table would look lik this:
1 Patient Blood Symptom1 Diagnosis1
2 Patient Blood Symptom2 Diagnosis2
3 Patient Blood Symptom3 Diagnosis3
That's 40%, or more since there is a lot more patient info, of
unnecessary
overhead.
I would still go with two tables.
Klatuu wrote:
Not really necessary, see my previous post.

Hopefully you have at least two tables, one with patient info
including blodd
test and another with symptoms and diagnosis. You will need to link
these
[quoted text clipped - 22 lines]

I would welcome any ideas on this.