No results.

[Bell]

Hi,

I've posted this question before but without being able to get my problem
solved, very greatful to answers!

I'm having a table where I have variable values (in a field labeled amount)
charcaterised by a set of properties which make each value unique, these
properties are presented in the fileds; cost unit, category, period and
account.

Now I want to create a query that present these amounts. I want to be able
to choose different cost units som I'm using a Parameter Form for these input
values. Then I get the query to display the summarised amounts given the
chosen cost units. I get Access to summarize according to below;

Account Category1Period1 Category2Period1 Category3Period1
Category1Period2 and so on... by using this expression B0501:
Sum(IIf([Category]="Budget" And [Period]="200501";[Amount])). But now I want
Access to summarise Category1 for the periods of 1,2 and 3, i.e.
B0501+B0502+B0503, hence on a quaterly basis. No matter how I try to do this
I've been unable to succeed. I've been trying various expressions as well as
basing the query with the quaterly expressions on another query performing
the first calculations.

Many thanks for your help / Bell

 

[David S via AccessMonster.com]

Hi Bell,

Can you post the actual table and the complete SQL containing the expression
you describe? It looks to me like an odd way of going about the task, but I
can't see any obvious reason why it doesn't work...

 

[Bell]

Please see below,

PARAMETERS [From CU] Text ( 255 ), [To CU] Text ( 255 );
SELECT Data.Account, Accounts.[Account name], Sum(IIf([Category]="Budget"
And [Period]="200501",[Amount])) AS B0501, Sum(IIf([Category]="Forecast" And
[Period]="200501",[Amount])) AS F0501, Sum(IIf([Category]="Outcome" And
[Period]="200501",[Amount])) AS O501, Sum(IIf([Category]="Budget" And
[Period]="200502",[Amount])) AS B0502, Sum(IIf([Category]="Forecast" And
[Period]="200502",[Amount])) AS F0502, Sum(IIf([Category]="Outcome" And
[Period]="200502",[Amount])) AS O0502, Sum(IIf([Category]="Budget" And
[Period]="200503",[Amount])) AS B0503, Sum(IIf([Category]="Forecast" And
[Period]="200503",[Amount])) AS F0503, Sum(IIf([Category]="Outcome" And
[Period]="200503",[Amount])) AS O0503, [B0501]+[B0502]+[B0503] AS BQ1
FROM Data INNER JOIN Accounts ON Data.Account = Accounts.Account
WHERE (((Data.[Cost Unit]) Between [forms]![ParamFormCU]![FromCU] And
[forms]![ParamFormCU]![ToCU]))
GROUP BY Data.Account, Accounts.[Account name];

Best regards / Bell

"David S via AccessMonster.com" skrev:

 

[David S via AccessMonster.com]

Thanks for that Bell, but it would also help if you could post the fields in
the Data and Accounts tables as well. As an observation, though, I've often
had trouble with trying to use fields in a query where the calculation uses
other fields that are also calculated - your [B0501]+[B0502]+[B0503]
calculation fits this profile. Try leaving the quarterly calculations off
this query, and creating another query that uses this query that does the
calculation.

So, you have the PeriodData query:
SELECT Data.Account, Accounts.[Account name], Sum(IIf([Category]="Budget"
And [Period]="200501",[Amount])) AS B0501, Sum(IIf([Category]="Forecast" And
[Period]="200501",[Amount])) AS F0501, Sum(IIf([Category]="Outcome" And
[Period]="200501",[Amount])) AS O501, Sum(IIf([Category]="Budget" And
[Period]="200502",[Amount])) AS B0502, Sum(IIf([Category]="Forecast" And
[Period]="200502",[Amount])) AS F0502, Sum(IIf([Category]="Outcome" And
[Period]="200502",[Amount])) AS O0502, Sum(IIf([Category]="Budget" And
[Period]="200503",[Amount])) AS B0503, Sum(IIf([Category]="Forecast" And
[Period]="200503",[Amount])) AS F0503, Sum(IIf([Category]="Outcome" And
[Period]="200503",[Amount])) AS O0503
FROM Data INNER JOIN Accounts ON Data.Account = Accounts.Account
WHERE (((Data.[Cost Unit]) Between [forms]![ParamFormCU]![FromCU] And
[forms]![ParamFormCU]![ToCU]))
GROUP BY Data.Account, Accounts.[Account name];

And a QuarterlyData query:
SELECT Account, [Account name], B0501, F0501, O501, B0502, F0502, O0502,
B0503, F0503, O0503, [B0501]+[B0502]+[B0503] AS BQ1
FROM PeriodData;

Please note that I fel a bit ambivalent about the way you've gone about
spinning these out in separate columns - you may want to think about using a
the Query Designer to create a Crosstab query, which might take some of the
pain out of it for you...

 

[Bell]

I have the following fields in the Data table;
Cost Unit Category Period Account Amount

and these in the table Account;
Account Group Account Name

Just "restarted" after vacation and will try your suggestions right away.

Best regards Bell



"David S via AccessMonster.com" skrev: