Grouping query

[IgorM]

Hi

I have a table named tblCostsByYear with data as follows:

AccountName Year Value
Fuel 2009 234.56
Coal 2008 123.33
Insurance 2008 898.99
Fuel 2009 12.88
Land tax 2009 885.99
Insurance 2008 345.88
and so on (there are about 50 different account names)

What I want to do is query the database so it returns the costs in each year
grouped by the AccountName column in the way that only 70 per cent of the
total costs within in each year is grouped by its original account name, the
remainder (30 per cent) is grouped under the name 'Other costs'.
How can I do that?

Thanks for any help

Kind regards
IgorM

 

[John Spencer]

First you need to explain a bit more of what you want.

Do you want 70 percent of the total cost for each Account?

Or do you want the most expensive accounts until you reach approximately 70
Percent of the total expenditure of ALL accounts for the year.

Or do you want the least expensive accounts until you reach approximately 70
percent of the total expenditure for the year.

Or do you want to select accounts to come as close as possible to 70 percent
of the total expenditure for the year (a mixture of expensive and not
expensive that best fits the 70 percent total). This one is very expensive in
use of computer resources and very difficult to solve.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

You have 2 Fuel records for 2009 and 2 Insurance records for 2008. Is this
the way your actual data is stored or is this a mistake in your sample data?

I would think you need to get the totals by account and year in one query
and yearly total in another. Then use a running sum and ranking query to
determing the top 70% based on the total cost for the year.

 

[IgorM]

Dear John,

Thanks for response. What I want to do is:
"the most expensive accounts until you reach approximately 70 percent of the
total expenditure of ALL accounts for the year." So basically I assume that
the top 70 percent is more important (and I want to see them in detail, with
their original account names). The remainder is less important, so I want to
have it grouped under 'Other costs' name.

Kind regards
IgorM

 

[IgorM]

Hi

It's not a mistake. This are actually records from a finance system. So in
each year the could have been hundreds records for fuel (as many as invoices
for fuel), insurance, etc.

 

[John Spencer]

The following is speculation and is entirely untested. But, hopefully it will
give you an idea on how to solve the problem. Lots of nesting.

You need a query to get Account totals by year save this as qAccountSum
SELECT AccountName
, [Year] as AccountYear
,Sum([Value]) as AccountTotal
FROM tblCostsByYear
GROUP BY AccountName, [Year]

Notice that I aliased field names in the query above to get rid of the
reserved words Year and Value which often require that you use brackets around
the field names. Access does not play well with brackets in sub-queries used
in the FROM clause

Next you need a running sum query based on qAccountSum
SELECT Q1.AccountName, Q1.AccountYear, Q1.AccountTotal
, Sum(Q2.AccountTotal) as RunningSum
FROM qAccountSum as Q1 INNER JOIN qAccountSum as Q2
ON Q1.AccountYear = Q2.AccountYear
AND Q1.AccountTotal<= Q2.AccountTotal
GROUP BY Q1.AccountName, Q1.AccountYear, Q1.AccountTotal
HAVING Sum(Q2.AccountTotal) >=
(SELECT .3 * Sum([Value]) FROM tblCostsByYear
WHERE tblCostsByYear.[Year] = Q1.[Year])

To get the final 30 percent is more complex, but might be handled using
SELECT AccountYear, Sum(AccountTotal) as YearlyRemainder
(SELECT Q1.AccountName, Q1.AccountYear, Q1.AccountTotal
, Sum(Q2.AccountTotal) as RunningSum
FROM qAccountSum as Q1 INNER JOIN qAccountSum as Q2
ON Q1.AccountYear = Q2.AccountYear
AND Q1.AccountTotal<= Q2.AccountTotal
GROUP BY Q1.AccountName, Q1.AccountYear, Q1.AccountTotal
HAVING Sum(Q2.AccountTotal) <
(SELECT .3 * Sum(tblCostsByYear.Value)
FROM tblCostsByYear
WHERE tblCostsByYear.Year = Q1.AccountYear)) as qFinal
GROUP BY AccountYear


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County