Solved

Trying to calculate a percentage in an access query.

Posted on 2012-03-12
17
269 Views
Last Modified: 2012-03-13
I am returning a few lines of data from my table. Each has an amount associated with it. I just want to return the selected items (through criteria on field [Seq]), then in another calculated field I'm trying to calculate each amount divided by the sum of specific amounts.

Here is a screenshot of what I'm trying to do;

query
Here is that formula again;
Percentage: [LineItem]/(Sum(IIf([Seq]="1",[Amount],0))+Sum(IIf([Seq]="2",[Amount],0)))

When I try to run this I get the following error;
error
Thanks for looking!
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711553
The first thing I have noticed is that you should select 'expression' for the table on the Percentage calculated field.

On the line directly below your formula click the empty field and select 'expression'.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711561
This is not a totals query.. I don't have a dropdown where I can select expression:
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711602
As soon as you use 'Sum' this query became an aggregation query.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711611
You can make this a totals query and for the fields that you are not totaling, you could make them where clauses that are visible and the value is not null.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711620
I think I see what you were saying.. I had to manually click the Totals button in order for the drop down to show up.   Now I'm getting a data mismatch error.. Not sure why that is though..
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711632
I did just correct my Percentage formula.. It was using the incorrect field.. Should have been;

Percentage: [Amount]/(Sum(IIf([Seq]="1",[Amount],0))+Sum(IIf([Seq]="2",[Amount],0)))

Turns out this doesn't do what I want though.. It is not doing a sum of every line like I thought it would.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711668
There is a problem.

If Amount is a number, say 50.
and the Seq = 1 then you would
calculate 50/0.  You cannot divide by zero.  that will throw an error.

Your If statement looks like it may be wrong.

if  seq=1
then Amount
Else 0

if seq=2
then amount
else 0

you are not summing anything.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711675
Any idea how to get each line being returned by the query to divide by the sum of several lines of data? I thought that's what  Sum(IIf([Seq]="1",[Amount],0))+Sum(IIf([Seq]="2",[Amount],0))) was going to do for me.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711702
The syntax for IIF is

IIF( condition, True, False)

What you did is string several IIF statements together, but they don't sum.

It looks like you truly do want to do a totals query. So you need to group off one or more of your fields.  Then you would have a simple sum that you could use in your percentage calculation.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37711710
When you are trying to total the amounts for the percentage.  What characterizes that group of records together? Are they the same Category?
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711717
In this case yes they are the same category.. I'm putting a quick screenshot together that will better represent the output I'm trying to get.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711727
Okay.. Maybe this will help identify what I'm trying to accomplish.. This is a screenshot of the exact output I'm trying to get to.. Is this possible in one query?

output
0
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 500 total points
ID: 37711794
I think I have a solution:

For the percentage you will do a sub query as follows:

Seq                              Category      LineItem    Amount  PPAmount
criteria >=1 and <=3

as you had before.  But for Percentage:

Percentage: [Amount]/(select sum([Amount]) from qryCSLPL where [Seq] in("1", "2"))
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37712400
SeyerIT,

Is it a coincidence that the sum of 1 and 2 is the same as the total for 3?

Are all the percentages supposed to be *Only* compared to the total of 1 and 2 as a percent?
(ex: 4 is 133% of one and two.)

In other words can you post more sample data, and make it more "random".

As always a sample DB is always much clearer.

If your goal here is to simply calculate the percentage that each line item is of the total then you can try something like this:

Create a public function in a module:
Public Function GetPercent(Amount As Currency) As Double
Dim dblTotal As Double
    dblTotal = DSum("Amt", "YourTable")
    GetPercent = Amount / dblTotal
End Function

Then call this in your query as a new field:
SELECT YourTable.ID, YourTable.Cat, YourTable.Item, YourTable.Amt, GetPercent([Amt]) AS PCT
FROM YourTable;
Database68.accdb
0
 
LVL 2

Author Closing Comment

by:SeyerIT
ID: 37714203
This was perfect! Sorry I'm just getting back to you on this. Boag, give me a second and I'll follow up and explain.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37714215
Boag, It was just a coincidence that those number equaled out the way they did. I didn't want it to appear that I was just taking the percentage by the sum of only the visible lines in the query and that they may come from other lines as well. My end query for this one was actually including the sum of Seq 1,2,3 & 4 but that wasn't going to be the case on the next one.

Thanks for your help though! Your always very good at guiding me to a better description of my problems so I can get through these :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37714849
No problem, again, as long as you got the solution you needed, then it's all good...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Google drive Permission Error 43 47
ORDER BY 7 37
What can I do to shrink my Access Frontend? 12 45
Access Criteria - Select first word from a string 27 30
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question