By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Entering formula in field

P: 13

I'm using a query which shows the total spend DDP/EUR per month per profit centre (given by the profit centre). I have also added what the total should be (COGS 5999 or 1300).

I would like to add (in the query) the percentage of reported total spend

As -> total spend (given by company) / COGS 5999 = ..%

How can I put that in my query? As I do not know where to place it (i.e. under which fieldname, in the criteria range, what the formula should be etc)

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Spend.PcCode, Profit_centre.[Profit centre name], Spend.YEAR, Spend.MONTH, Sum(Spend.[Total spend DDP/EUR]) AS [SumOfTotal spend DDP/EUR], COGS.[5999], COGS.[1300]

I hope you can help me
Mar 19 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 14,534
Something like this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Spend.PcCode, Profit_centre.[Profit centre name], Spend.YEAR, Spend.MONTH, Sum(Spend.[Total spend DDP/EUR]) AS [SumOfTotal spend DDP/EUR], COGS.[5999], COGS.[1300],
  2. Format([SumOfTotal spend DDP/EUR] /COGS.[5999], "Percent") As PercentSpend
  3. FROM TableName
  4. GROUP BY Spend.PcCode, Profit_centre.[Profit centre name], Spend.YEAR, Spend.MONTH, COGS.[5999], COGS.[1300]
Mar 22 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.