Question Variance File (1 Viewer)

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
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...

I attached an excel file for your reference of the possible explanation outcome.

A million thanks to all who will help.
 

Attachments

  • variance.xls
    27.5 KB · Views: 74

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
First, explain why this data is in 2 different tables. When you have 2 tables of the same structure, you keep that data altogether. So explain why this data is in 2 different tables and how the data gets in there.

Second, 'Date' and 'Year' are reserved words in Access and make writing code and queries more difficult. I suggest you rename those fields, prefixing them with what they represent (e.g. CounterMonth, ChartYear, etc.).

Third, what's the exact name of those tables you have?
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

Thanks for immediately replying my query.
It is very much appreciated. :) :)

I separated these two data because they are coming from two time periods. The first table is for 6-2015 and the other one is for 3-2015. The Chartline serves as the Mainline (something like Expense), Counter and CounterName are related (CounterName) desrcibes Counter (something like a Cost center and Cost Name) and the Balance.

The third sheet is the comparison of two tables. I mean to have a third sheet (or if it is possible to eliminate it) to show the comparison. For excel, pivot tables compare the two periods and then after that the manual explanation of increases and decrease. Can this be done on ms access?

Hope my explanation helps. :) :)
 

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
Helped a little. You still didn't provide your table name and then you iterated a ton of information in your first post without addressing the issues I identified.

What you want to achieve isn't terribly complex, but its not novice level either. My advice is to read up on Totals queries: https://support.office.com/en-za/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

Give it a shot then post back here what you have.
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

Thanks for the quick information.
I somehow managed to create cross tabs. These two cross tabs produces the sum but only for a specific chartline (as the other chartline serves as a separate Cost Center, but we con focus on this initially). But then how can I create an increase decrease between two cross tabs and also the variance explanation similar to explanation in Excel? Do I need to create a new query or some calculated fields?

Apologies for the inconvenience previously.
Thank you very much.

Cheers,
 

Attachments

  • Sample_variance.accdb
    836 KB · Views: 66

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
You should get your data in one table. Then you should do a Total's query.

Personally, I would leave it in a format similar to what a Total's query gives you. The Excel format you have is just a format and not really much better than what you will have at the end of the Totals query. But if you so desire, you can build a cross-tab query using your Total's query as a source.
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

Ok, apologies.
Can you check if I made the right Totals Query?

I attached again the file and combined the two excel sheets.

Cheers,
 

Attachments

  • Variance_v2.accdb
    508 KB · Views: 57

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
Excellent, just what you should have done. Now you build another query, using that one as the data source. It's a little tricky so I wrote it. Paste this SQL into a new query in that database and run it:

Code:
SELECT qrySum1st.Chartline, qrySum1st.Counter, qrySum1st.CounterName, Sum(IIf([Mo]=3,[SumOfBalance],0)) AS M3Balance, Sum(IIf([Mo]=6,[SumOfBalance],0)) AS M6Balance, Sum(IIf([Mo]=6,1,-1)*[SumOfBalance]) AS Variance
FROM qrySum1st
GROUP BY qrySum1st.Chartline, qrySum1st.Counter, qrySum1st.CounterName;

That produces all the data you want.
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

This is amazing :) :) Everything that I need is here!
There is one more thing, how can I managed to build a query out of that Variance Table that produces the Variance Increase/Decrease explanation.

It will be something like this (since it now calculates the difference between two time periods - M6Balance is the currect period while M3Balance is the prior period therefore M6Balance will serves as the Base Period). So the explanation:

For Chartline 1000 = INCREASE in FAR, 10; SLOW, 10; BIG, 50.

And the same as other Chartlines. :)

Cheers,
Vash
 

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
It would probably have to be a VBA function that looks at the query and returns your string describing the changes. You would use a recordset to loop through all records of the query and build your string for each individual CounterName.
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Oh my, :confused: but it must be in a single field also.
Can you help?
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

I've managed to plug the code on VBA but the Remarks are not filtered per Chartline. I know I am missing something but I cannot figured out how to use the "Filter" part of the syntax.

Appreciate your checking again. :)

Cheers,
 

Attachments

  • Variance_v4.accdb
    724 KB · Views: 61

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Hello Plog,

I used the Query Criteria and it works now, please disregard. :)
Only that the query is slow to gasp. :(

But if you can advise the best way (speed up) thru that created function it's very much appreciated.

Thanks.

Cheers,
Vash
 

plog

Banishment Pending
Local time
Today, 10:20
Joined
May 11, 2011
Messages
11,611
I'm not familiar with that function myself, but I bet its filter works like Dlookup: http://www.techonthenet.com/access/functions/domain/dlookup.php

You compile a string that limits a specific field(s) to a specific value(s). Yours will most likely include every field that has GROUP BY underneath it in the query you are using to look into for the data.
 

vash011390

Registered User.
Local time
Today, 23:20
Joined
Jul 29, 2015
Messages
29
Ok, Many Many Thanks for all your help :) :)

I'll be just tweaking this MS access for it to be user friendly...


Cheers,
Vash
 

Users who are viewing this thread

Top Bottom