Trying to create a calculation in a query....

[mprekop]

Hi,

I am attempting to move a reporting system that currently operates in
excel over to MS Access. Part of this is to reproduce the fields
contained in the Excel reports one of which calculates the accounting
amount against % of sales.

My snafu is that the parameter "sales" lives in the ACCTNAME field
along with all the other account names that I need to measure a
percentage of that against. I have a table set up as follows:

ACCT NAME | AMOUNT

eg sales $20k
Gross Profit $12k
etc
etc


I would then want the thrid column along to display the amount figure
as a percentage of sales. But how do I do that if sales is "in" the
ACCTNAME field?


An example of a similar type, if anyone can provide this would be most
helpful.


Thanks in Advance.

Maz

 

[KARL DEWEY]

You can use a criteria on the ACCT NAME field ---
Like "*" &"sales" & "*"
But to calculate a percent you need two numbers. Where will you get the
second figure from?

 

[Roger Converse]

So you are trying to figure out the percent of gross margin against the total
in that column?

If that is the case, you could query the table for the total and capture
that amount. Then divide that by your figure, for which Karl's method will
work.