How to combine and sum data in multiple fields

[Big Tony]

We are a medical clinic. One table describes each
patient visit by date, and then by diagnoses. We have
four diagnosis drop down boxes, allowing the viewer to
choose from 150 diagnoses, and assign a patient up to
four per clinic appointment (per record).

What we would like to do is take the data that is in the
resulting 4 fields in the table, and combine them into
one field for a query, so that we can total how many
times each diagnosis was made during a certain time
period.

I hope that I have not been too abstract in this
description, let me know if more detail is needed to
describe my dilemma.

 

[John Spencer (MVP)]

Take a look at UNION queries to get all your data into a normalized structure.

SELECT Dx1, VisitDate
FROM YourTable
UNION ALL
SELECT Dx2, VisitDate
FROM YourTable
UNION ALL
SELECT Dx3, VisitDate
FROM YourTable
UNION ALL
SELECT Dx4, VisitDate
FROM YourTable

Obviously you will need to replace the above with your field and table names.

Save that as qUnionDxDates

Now you can use that as the source for a totals query. Something like

SELECT DX1, Count(Dx1) as HowManyTimes
FROM qUnionDXDates
WHERE VisitDate Between #1/1/04# and #12/31/04#
Group BY DX1