You could combine the values from the 3 fields into a single query like
this:
SELECT PrimDrug FROM Table1 WHERE PrimDrug Is Not Null
UNION ALL
SELECT SecondaryDrug1FROM Table1 WHERE SecondaryDrug1Is Not Null
UNION ALL
SELECT SecondaryDrug2FROM Table1 WHERE SecondaryDrug2Is Not Null;
Replace Tabe1 with your table name. Access cannot show that query
graphically, but it should work in SQL View.
A better solution would be to normalize the data by creating a related
table, where can have many *records* for the drugs used in each case,
instead of the 3 fields. Whenever you have repeating fields like these 3, it
always indicates that you should use a related table instead.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Macbane" <jo*******@sch.nhs.ukwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
I really hope you can help. I have a databse which records
interventions in patients drug therapy. Each record in the main table
contains 3 fields to capture the 3 main drug names involved in the
intervention and they are called PrimDrug, SecondaryDrug1,
SecondaryDrug2. The drug names are selected from another table called
Drug. What I want to do is to have a graph illustrate the top ten
drugs involved. While it is easy to do this for each field/category, I
am not sure how I can combine the names from all three field in a query
and do a global count of all the drugs involved for all of the records
regardless of the prmary or secondary category.
I bet its really simple but I am having a block. Hope to hear from you
soon!
MacBane
