Create percentage from column names

B

Brian62

Registered User.
Local time
Today, 03:16
Joined
Oct 20, 2008
Messages
159
I am trying to create a percentage of the (yeses and N/A) / (No's). There are 22 column names from another query.

This is what I have tried in my query by I should be in the high 90% range instead of 4%.

Yes: Sum([Doc2Y]+[Doc12-5Y]+[Doc25Y]+[Doc30y]+[Doc36y]+[MemoY]+[Doc19Y]+[Doc25-7Y]+[Doc26Y]+[Doc27Y]+[Doc32-2Y]+[SRSY]+[Doc44-2Y]+[ProjectsY]+[AbstractY]+[Doc34-9Y]+[Doc35Y]+[Doc37Y]+[ProtocolsY]+[Doc32Y]+[Doc49-2Y]+[Doc11-5Y])/("100")

I have another one just like this but for the no's.

Example: 20 yeses or N/A's of 22 column names subtracting 2 No column names should give me 91%.

Is there a way that I can do this in a query that will show on my report?
 
I suspect you're thinking of this calculation like an Excel way of summing and dividing. The Sum() function you've used there will sum up row wise, it doesn't add column values as you can have it in Excel.

Is my assumption right?

Also, I notice you have field names that are similar to each other and I suspect your data is not normalized. Why are the field names increasing in number?
 
Your assumption is correct. When I use the sum() it does give me the total of all the yeses and N/A's for all the columns. I got it to add in by columns but now I need it to divide into a percentage.

The field names are actually document numbers. It is a checklist that I have created and need to calcualte the percentage when sending it up the chain.

I forgot to mention that it will have to divide it by 10 records that I audit each month on top of what I already mentioned. Didn't hink about it until I left for the day. (Brain fart)!

The 91% would be from one Audit.
 
So are we talking about row wise percentages or a percentage of ALL the records?
 
I have setup Between dates for records in the date range I want.

It would be percentages of all records (10) since I do 10 audits per month that has 22 columns of data that I need to get a percentage of.

I'm also thinking that since the data is coming from a date range getting a percentage from the rows by the 22 columns maybe the way to go but I am not sure.
 
Your objective still isn't clear.

Do you want to calculate the percentage of Yes/NAs just for each column? Or you want to get the percentage of Yes/NAs for ALL columns in ALL the records?

I believe it's the latter you're after. In that case there are a couple of steps. First step is to count how many there are for each column. For example, the firs column will be:

Count1: Count(IIF([Doc2Y] In ('Yes', 'N/A'), 1, Null))

Do that for each column.

Create an alias field to sum up all the counts, i.e.

SumAllYesNA: Count1 + Count2 + Count3 + Count4 + ... + Count22

Finally, the alias field to perform the percentage calculation:

PercOfYesNA: (SumAllYesNA / (22 * Count(*))) * 100

NB: Count(*) is simply counting all the records.

I hope this makes sense.
 
Last edited:
You have me on the right track and you did figure out what I was trying to do. I didn't think that the query that the information is drawn from would make a difference but it does (Query 1).

First, I have a popupform that works off a command button that pops up to enter dates where I want to pull records from. Once that happens the report comes up with the selected dates I inputted in the popupform.

Query 1 uses code for example "Doc2Y: Count(IIf([Doc #2] In ("Yes","N/A"),1))" (expressions) to get me my totals and works off of Audit Date (Group By).

Query 2 which I used your codes from Query 1 worked but will only pulled 5 records instead of 10 records because of the group by on Query 1 Audit date.

Example of Query 2:
SumAllNo: Sum([Doc2N]+[Doc12-5N]+[Doc25N]+[Doc30N]+[Doc36N]+[MemoN]+[Doc19N]+[Doc25-7N]+[Doc26N]+[Doc27N]+[Doc32-2N]+[SRSN]+[Doc44-2N]+[ProjectsN]+[AbstractN]+[Doc34-9N]+[Doc35N]+[Doc37N]+[ProtocolsN]+[Doc32N]+[Doc49-2N]+[Doc11-5N])

PercOfNo: ([SumAllNo]/(22*[TotalRecs]))*100

For the total for SumAllYesNA = 437 gives me 39727%
For the total for SumAllNo = 3 gives me 2.72727272727273

All from TotalRecs = 5 records instead of 10.

It needs to pull from the between dates from popupform to give me the right amount of records to be calculated correctly. I hope this helps you understand what I am trying to do.

If you need me to attach the database so you get the total understanding of what I am trying to do.

Thanks!!!!!!
 
I have attached the database:

Here is the flow of order.
Form to enter data is: frmRDStudyAudit. On the form the report that the popupform showing the selected dates for the report is Open Report Button.

The report pulls data off of the dates provided. Use 10/12/10 and 10/13/10.

The report pulls data from QryRDStudyAudit.

On the last page where the percentage (QryRDStudyAudittest1 subreport) of 22 cells times 10 audits = 220 cells I performed is located in QryRDStudyAudittotals. The 22 cells are the required cells of approx 80.

QryRDStudyAudittotals gets it's information from QryRDStudyAudittotal which gets it's information from 10 tables.
 

Attachments

Please always mention what the main goal of your task is. If you're going to use it for a report you should have mentioned that from the start.

On looking at your database I have found that none of your 15 or so tables don't have relationships and they are very unormalized and as a result I can't help you any further with this. Performing this in a query wouldn't give you the correct results because of this.

Nevertheless, since you're using a report and it seems to be working for you, perform the percentage calculation in the report in code. All the calculations will go in the report footer section.
 
I appreciate your help that you provided but I did mention that I wanted this to work in my report in my first post.

Thanks again!
 
You sure did. My apologies.

If you want to get the percentages of the YES/NA from the fields that are showing in your report it won't be too difficult. You can use the codes already provided in my post #6. I noticed one of the codes you wrote wasn't exactly what I wrote.

Let us know how it goes.
 
Finally after 3 weeks of working on this problem it WORKS perfectly! Thanks for all your help!!!!

I have another problem but it probably will have to be a SQL script to work.

Thanks again!!!
 
You must log in or register to reply here.

Similar threads

R
Unusual Column Name in Query
Replies
8
Views
995
RogerCooper
R
L
Create a query table name from combobox
Replies
17
Views
2,176
JHB
J
D
Help for a beginner - creating rows,columns and formatting name
Replies
4
Views
739
danboi10
D
B
How to count data from another query by date
2
Replies
20
Views
3,596
vbaInet
V
T
can you create new column in a query based off of data from two columns already there
Replies
9
Views
2,138
travismp
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom