I misunderstood what you wanted. That's a lot simpler. Its just a case of
adding the two summations:
SELECT BudgetTitles.BudgetUnitTitle,
Sum(ActualSalaries.TotalCost) AS [Department Salaries - Actual],
Sum(ActualOtherCosts.ExtendedCost) AS [Department Other - Actual],
Sum(ActualSalaries.TotalCost)+Sum(ActualOtherCosts.ExtendedCost)
AS [Department Total Costs]
FROM (BudgetTitles LEFT JOIN ActualSalaries ON
BudgetTitles.BudgetUnitTitle=ActualSalaries.BudgetUnit) LEFT JOIN
ActualOtherCosts ON BudgetTitles.BudgetUnitTitle=ActualOtherCosts.CostCenter
GROUP BY BudgetTitles.BudgetUnitTitle, ActualSalaries.Event;
However, I'd advise that you check the results of your query carefully
against the base data as I think there is a danger that it might not return
the correct values. As you are LEFT OUTER JOINing the BudgetTitles table to
each of the other tables, if one of these has more rows per budget title than
the other you could get duplicate values returned from the table with the
lesser number of rows before the summations take place, as a consequence of
which one of the totals will be too high. It may be that the way your data
is structured avoids this, but in principle the risk does exist.
This can be avoided by using subqueries to sum the values, rather in the way
my original example did, but a better solution might be to combine all the
expenditure into one table with a column to indicate the category of
expenditure, salaries or other. This table could then be INNER JOINed to
BudgetTitles, so only one row per item of expenditure is returned. By
grouping by budget title and then by expenditure type the total expenditure
per budget title/expenditure category can easily and reliably be returned.
These would be in separate rows of course, not as separate columns, but the
latter can be achieved either by means of a crosstab query, or as the
expenditure categories are fixed as salaries and other, two columns could be
returned without recourse to a crosstab query by grouping solely by budget
title and conditionally aggregating the values like this:
SUM(Amount*IIF(ExpenditureCategory = "Salaries",1,0)) As TotalSalaries,
SUM(Amount*IIF(ExpenditureCategory <> "Salaries",1,0)) As TotalOther
Ken Sheridan
Stafford, England
Jerry Anderson said:
I must admit to being something of a newbie (but with high aspirations!) I
can’t find any clear examples of what I’m trying to accomplish. Here’s the
deal:
I have three tables: BudgetTitles, ActualSalaries, and ActualOtherCosts
Through the following SQL, I get the total for Salaries and Other Costs by
Budget Unit:
SELECT BudgetTitles.BudgetUnitTitle, Sum(ActualSalaries.TotalCost) AS
[Department Salaries - Actual], Sum(ActualOtherCosts.ExtendedCost) AS
[Department Other - Actual]
FROM (BudgetTitles LEFT JOIN ActualSalaries ON
BudgetTitles.BudgetUnitTitle=ActualSalaries.BudgetUnit) LEFT JOIN
ActualOtherCosts ON BudgetTitles.BudgetUnitTitle=ActualOtherCosts.CostCenter
GROUP BY BudgetTitles.BudgetUnitTitle, ActualSalaries.Event;
What is the syntax to add an additional column that displays the total for
Salary and Other Costs (again grouped by BudgetTitle)?
Ken Sheridan said:
If you want to show both the individual and aggregated values in the same
result table then you'll need to use subqueries. Without knowing how you've
arrived at the current columns its difficult to say how this might be done by
amending the current query, but you could base yet another query on the
current one like so:
SELECT [Cost Center], [Salaries-Projected], [Other-Projected],
(SELECT SUM([Salaries-Projected])
FROM [YourCurrentQuery] AS Q2
WHERE Q2.[Cost Center] = Q1.[Cost Center])
AS [Total Salaries-Projected per Cost Center],
(SELECT SUM([Other-Projected])
FROM [YourCurrentQuery] AS Q3
WHERE Q3.[Cost Center] = Q1.[Cost Center])
AS [Total Other-Projected per Cost Center]
FROM [YourCurrentQuery] AS Q1;
Another approach, which can be done without having to write any SQL, would
be to create a query which groups by cost centre and sums the
salaries-projected and other-projected columns. Then join this query to your
current query on [Cost Center].
Alternatively you could of course base a report on your current query, group
the report by cost centre and sum the two values in a group footer.
Ken Sheridan
Stafford, England
Jerry Anderson said:
I have a query with the following fields:
Cost Center; Salaries-Projected;Other-Projected
These fields result from a combination of SELECT queries that I have rolled
into one.
I would like to add an additional calculated field that will total
Salaries-Projected and Other-Projected by Cost Center.
How do I do this?
