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
1  1898 
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 SecondaryDrug1F ROM Table1 WHERE SecondaryDrug1I s Not Null
UNION ALL
SELECT SecondaryDrug2F ROM Table1 WHERE SecondaryDrug2I s 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.goog legroups.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though the records are sorted by row numbers.
(I had to split the fields to different sheets because Excel has a
limit of 256 fields in each sheet)
My sheets are quite large (~55,000 rows and 200 columns each) and I'll
have to repeat this action many...
|
by: M.Stanley |
last post by:
Hi,
I'm attempting to create a query that will combine 2 columns of
numbers into one. The followng comes from 1 table with 4 fields
(A,B,C,D)
A B RESULT
700 000 700000
700 001 700001
700 002 700002
..
|
by: Julio Sarmiento |
last post by:
I have a table containing the following fields:
rep numbers, year, month, qty, revenue, credit apps, new accounts.
The data in this table reflects the rep's daily numbers. What I need to do
is sum the columns and rank each column totals against all the records in
the table. For example - assume that the figure below are the monthly
totals for four reps.
Rep Number Year Month Qty Revenue Credit Apps
New Accounts...
|
by: Sam.Moretta |
last post by:
Hey all. Ive got a big problem with an sql statement Im working on.
There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding...
|
by: brm6546545 |
last post by:
I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal,
NonTaxableTotal, TaxCollected.
Sample data
1,county,10.00,0.00,0.40
1,city,10.00,0.00,0.10
2,state,0.00,15.00,0.15
When totaling invoice 1 should have totals of 10.00,0.00,0.50 because
| | |
by: rdemyan via AccessMonster.com |
last post by:
I have a subform that displays around 10 fields. Most of the values shown
result from some complex calculations. To make it easy, I have stored the
results in a temporary table and then all I need to do is set the
recordsource of the subform equal to the table.
However, it would be very nice if the last row in the subform showed the
totals of the previous rows (of course where applicable). If it were a
listbox (that didn't require...
|
by: isdeveloper |
last post by:
Hi All,
I have a problem with a table that I want to get nice data out of in a
single query. The guys here reckon it can't be done in a single query
but I wanted to prove them wrong !! Essentially, I want to get the same
column out of the single table, but in one case the column must have a
where clause associated with it, and the other case it does not have a
where clause...
Lets say have a table like this :-
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
by: billelev |
last post by:
Does anyone know if it is possible to combine two queries that have the same fields? I basically want to combine a number of fields with the totals for those fields into one query (see example below).
eg. If the original query contained four items: a, b, c and d, the resulting query or table combination would be:
Name Amount
a 1
b 2
c 3
d 4
ALL 10
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
