By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Combining totals for 2 different fields

P: n/a
Hi,

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

Aug 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Aug 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.