Variance File

[vash011390]

Hello All,

I am trying to create a Ms Access database that will generate an automatic variance explanation comparing two periods in two tables and the comparison in third table. This will help me eliminate the time spent on tedious (manual) comparison of various increases and decreases between lines. I don't know how to start - which query to use or maybe macro can help so I am seeking professional help.

Actually I already created .xlsm file with customized sumif but the file is soooo slow to generate variances...

A million thanks to all who will help.

Table 1

Year	Month	Chartline	Counter	CounterName	Balance
2015	6	1000	a1	far	200
2015	6	3000	a1	far	150
2015	6	1000	a1	far	40
2015	6	1000	a2	slow	10
2015	6	2000	a2	slow	30
2015	6	1000	a3	big	500
2015	6	1000	a3	big	100
2015	6	2000	a2	slow	200
2015	6	5000	a1	far	100
2015	6	2000	a1	far	20
2015	6	1000	a2	slow	10
2015	6	1000	a1	far	10
2015	6	3000	a3	big	70
2015	6	2000	a1	far	50

<tbody>
</tbody>

Table 2

Year	Month	Chartline	Counter	CounterName	Balance
2015	3	5000	a1	far	200
2015	3	2000	a1	far	100
2015	3	1000	a1	far	20
2015	3	1000	a2	slow	10
2015	3	3000	a2	slow	10
2015	3	2000	a3	big	70
2015	3	1000	a3	big	50
2015	3	3000	a2	slow	200
2015	3	1000	a1	far	150
2015	3	1000	a1	far	40
2015	3	2000	a2	slow	10
2015	3	1000	a1	far	30
2015	3	1000	a3	big	500
2015	3	2000	a1	far	100

<tbody>
</tbody>

Table 3

Pivot of the two tables and increase decrease.

Chartline	1000
Sum of Balance		Month
Counter	CounterName	3	6	Inc(Dec)
a1	far	240	250	10
a2	slow	10	20	10
a3	big	550	600	50
Grand Total		800	870

<tbody>
</tbody>

Explanation:

INCREASE $10 FAR, $10 SLOW, $50 BIG.

<tbody>
</tbody>

 

[Micron]

generate an automatic variance explanation comparing two periods in two tables and the comparison in third table.

Are you saying you want to store the results in a third table, or to compare table1 and table2 against table3? Or are you going to display the results in a form or report (usually best to do calculations in forms or queries, but I have not always adhered to that "rule of thumb")?

 

[vash011390]

Hello Micron,

Table 1 and Table 2 came from two different time periods.
The third table should contain the Variance Explanation after comparing the two but I do not know how.
The pivot only show the calculation differences between Table1 and Table2 but can be eliminated or just add another table to show this.

Hope I explained it clearly.

 

[Micron]

I think you will need a union query that creates totals in order to get the records from each table into successive rows, then a cross tab query to arrange the rows into columns that resembles your pivot table. However, you cannot create a calculated field in a crosstab query to give you the difference, so you need a select query that uses the crosstab as a source. Someone may post an eaiser way than my solution, but here are sql statements that you can play with if they are not quite right:

qry1 - 1st part of union query (put the ; back on to run this as a separate query)

SELECT Table1.Year, Table1.Month, Table1.Counter, Table1.CounterName, Sum(Table1.Balance) AS SumOfBalance
FROM Table1
GROUP BY Table1.Year, Table1.Month, Table1.Counter, Table1.CounterName, Table1.Chartline
HAVING (((Table1.Chartline)=1000))
ORDER BY Table1.Month

qry2 - 2nd part of union query (remove the UNION to run this separately, ORDER BY can only be in 1st part)

SELECT Table2.Year, Table2.Month, Table2.Counter, Table2.CounterName, Sum(Table2.Balance) AS SumOfBalance
FROM Table2
GROUP BY Table2.Year, Table2.Month, Table2.Counter, Table2.CounterName, Table2.Chartline
HAVING (((Table2.Chartline)=1000));

qry3 - the concatenated union query

SELECT Table1.Year, Table1.Month, Table1.Counter, Table1.CounterName, Sum(Table1.Balance) AS SumOfBalance
FROM Table1
GROUP BY Table1.Year, Table1.Month, Table1.Counter, Table1.CounterName, Table1.Chartline
HAVING (((Table1.Chartline)=1000))
ORDER BY Table1.Month
UNION 
SELECT Table2.Year, Table2.Month, Table2.Counter, Table2.CounterName, Sum(Table2.Balance) AS SumOfBalance
FROM Table2
GROUP BY Table2.Year, Table2.Month, Table2.Counter, Table2.CounterName, Table2.Chartline
HAVING (((Table2.Chartline)=1000));

qry4 - crosstab query to pivot the row values to columns so we can do math on side by side values

TRANSFORM Avg(Query3.SumOfBalance) AS [Total Of SumOfBalance]
SELECT Query3.Counter, Query3.CounterName FROM Query3
GROUP BY Query3.Counter, Query3.CounterName
PIVOT Query3.Month;

qry5 - do the math

SELECT Query4.Counter, Query4.CounterName, Query4.[3], Query4.[6], [6]-[3] AS Inc_Dec FROM Query4;

I didn not try to name the column Inc(Dec) because Access will no doubt interpret that as a UDF (user defined function). Your grand total row is a job for a form or report. Access is not Excel, after all!
Hope this helps.

 

[vash011390]

Hello Micron,

Many many thanks for all the help.
This is all that I need.

Cheers,
vash

 

[Micron]

You're welcome!