Display and count unique in report 1

[rugbyguy]

Hey folks,

I have a small problem I was hoping someone could help me with.

I have a report based on a query (access 2002).

I would like to count the frequency of all distinct records in the following fields: [disease1],[disease2] and [disease3], listing the disease and the combined frequency. Can this be done in the expression builder?

As always, my questions have a wrinkle. The same diseases appear in [disease2] and [disease3] fields. My end product should look like the following:

disease...frequency
pneunomia 5
common cold 8
headache 15

These frequency counts should be a combination of the 3 fields.

Thanks.

Rugbyguy

 

[Darrylles]

Hi,

I'd look at your table design. You can't logically name fields as 'disease1', 'disease2', 'disease3' etc and then hold multiple diseases within each.

It doesn't make sense. You'll be multiplying the complexity of your application, and it will become exponentially more difficult as time goes on.

I think that it may be better for you to ask "What's the best table design for this scenario......", and then think about reporting.

(You may well spend the rest of your life in Tek-Tips otherwise - lol).

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[rugbyguy]

Unfortunately, I am stuck with the existing table structure. I wouldn't worry too much about [disease2] or [disease3]. I can find an easy way around that.

The crux of my problem is that I need to list the distict occurences in my field [disease1], while producing a sum of total occurences.

 

[dhookom]

RugbyGuy,
You can create a UNION query that normalizes your data:
SELECT [Disease1] as Disease
FROM tblA
WHERE [Disease1] is not null
UNION ALL
SELECT [Disease2]
FROM tblA
WHERE [Disease2] is not null
UNION ALL
SELECT [Disease3]
FROM tblA
WHERE [Disease3] is not null;

You can then create a totals query based on the union query that groups by Disease and Counts Disease.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.