Use results from Query to do calculations (1 Viewer)

Dissander · Jul 14, 2017

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.

plog · Jul 14, 2017

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.

Dissander · Jul 14, 2017

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.

MarkK · Jul 14, 2017

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

plog · Jul 14, 2017

If Mark's answer doesn't help, please provide me with what I initially requested.

Dissander · Jul 14, 2017

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.

jdraw · Jul 15, 2017

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.

Dissander · Jul 15, 2017

Hello jdraw. You are amazing. It worked. Thank you very much.
Much appreciated.

jdraw · Jul 15, 2017

Happy to help.
Good luck with your database.

Dissander · Jul 15, 2017

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?

jdraw · Jul 15, 2017

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.

Use results from Query to do calculations (1 Viewer)

Dissander

Registered User.

Attachments

plog

Banishment Pending

Dissander

Registered User.

MarkK

bit cruncher

plog

Banishment Pending

Dissander

Registered User.

Attachments

jdraw

Super Moderator

Attachments

Dissander

Registered User.

jdraw

Super Moderator

Dissander

Registered User.

jdraw

Super Moderator

Attachments

Similar threads

Users who are viewing this thread