Use results from Query to do calculations

Dissander

Registered User.
Local time
Today, 15:22
Joined
Mar 22, 2017
Messages
29
Dear Team,
I would like to use the results from a query for more calculation. I have used count in design view, to count the number of times a word appears. I now want to do addition & percentage of the data.
In the example given (attached)
i want to add "I" with "OTCOL" for e.g.
How can i do this please? thank you/
I have very basic knowledge in access.
 

Attachments

I don't really understand what you are trying to do. Could you demonstrate it with data?
I would need 2 sets:

A. starting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me the data you expect to end up with when you feed it the data from A.
 
The data is about blood transfusion status. IN = Infused , WIWARD=wasted in ward.
The table I provided is the result from a query, where I counted how many units were infused and wasted, etc.
I now want to used these number for calculation. e.g Infused + Wasted in ward= Total
or Wasted /total X 100 = Percentage wasted.
 
You can use a query as the input table for another query, so say I save this query as Query1...
Code:
SELECT Field1, Field2, Field1 * 12 / Field2 As Field3
FROM YourTable
Then I can write another query ...
Code:
SELECT *
FROM Query1
WHERE Field1 > 1100
ORDER BY Field2
...that uses Query1 as the source table.
hth
Mark
 
If Mark's answer doesn't help, please provide me with what I initially requested.
 
Thank you both. Sorry Mark, my knowledge is poor. I could not work it out. I have now attached the original data. I want a query to count all units infused, wasted and presumed. After, I want to to a total number of all units then I want to do percentage e.g % wasted i.e wasted/total *100.
Could you help me please? Thank you.
 

Attachments

I have added 2 queries to your database.

qryCountsByStatus with sql

Code:
SELECT Count(Transfusion.DonationNumber) AS CountRelevant, Transfusion.EpisodeUnitStatus
FROM Transfusion
WHERE (((Transfusion.EpisodeUnitStatus) In ("INFUSED","WASTEDINWARD")))
GROUP BY Transfusion.EpisodeUnitStatus;

and

qryWastePercentage which uses the first query with sql

Code:
SELECT Sum([CountRelevant]) AS tot
, (select countRelevant from qrycountsByStatus where episodeUnitStatus = "WastedInWard") AS waste
, ([waste]/[tot])*100 AS PercentWaste
FROM qryCountsByStatus;

which gives result

tot waste PercentWaste
4707 146 3.10176333120884

I'm attaching my revised copy of your database.

Good luck with your project.
 

Attachments

Hello jdraw. You are amazing. It worked. Thank you very much.
Much appreciated.
 
Happy to help.
Good luck with your database.
 
Could i also ask please, if instead of putting "from qrycountsByStatus where episodeUnitStatus = "WastedInWard")"i wanted to put Infused + wasted In Ward, what would be the formula?
 
Here are the counts by Status

Code:
CountRelevant	EpisodeUnitStatus
4561	INFUSED
182	PRESUMED USED
146	WASTEDINWARD

What exactly should be in the total and what exactly is included in "waste"?

Where does PRESUMED USED fit in your counts?

Here are 2 additional queries to consider since I hve not received response to my post.

query1 'count all Statuses

Code:
SELECT Count(Transfusion.DonationNumber) AS CountRelevant
, Transfusion.EpisodeUnitStatus
FROM Transfusion
GROUP BY Transfusion.EpisodeUnitStatus;

with result
Code:
CountRelevant	EpisodeUnitStatus
4561	INFUSED
182	PRESUMED USED
146	WASTEDINWARD

query3 ' use WastedInWard and Infused as "Waste"

Code:
SELECT Sum([CountRelevant]) AS tot
, (select sum(countRelevant) from query1 where episodeUnitStatus In ( "WastedInWard","INFUSED")) AS waste
, ([waste]/[tot])*100 AS PercentWaste
FROM query1;

with result

Code:
tot	waste	PercentWaste
4889	4707	96.2773573327879

Revised database with the new queries attached.
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom