Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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;

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;

Thanks for looking!

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

Here is that formula again;

When I try to run this I get the following error;

Thanks for looking!

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

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

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.

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.

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

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 :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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"))