Solved

actual vs budget variance reports

Posted on 2002-04-14
19
5,795 Views
Last Modified: 2007-11-27
I suspect I'm making this more difficult than it is but I've been struggling all weekend and I still haven't gotten it to work.  

I want to produce a report with 12 columns as follows:
Actual Week
Budget Week
Variance Week
Actual Month-to-Date
Budget Month-to-Date
Variance Month-to-Date
Actual Quarter-to-Date
Budget Quarter-to-Date
Variance Quarter-to-Date
Actual Year-to-Date
Budget Year-to-Date
Variance Year-to-Date

This data will be entered by account number within departments.

The fields in the data table are: account number, department, week number, actual or budget (yes/no) field, and the amount.  I also have a table that translates the weeks into the appropriate month and quarter.  How do I build the queries to get all the above columns on a single report? Or do I need to structure the input tables differently?  I would so appreciate your help!
0
Comment
Question by:clballas
  • 9
  • 8
  • 2
19 Comments
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6941202
First of all explain to us the function of the secondary tables month, quarter, year etc.  Are these just conversion tables to assist in determining the rollup criteria?

Or are they actual tables that are updated upon each transaction with the transaction amount?

Bob Scriver
0
 
LVL 3

Expert Comment

by:forester
ID: 6941205
clballas, how would you translate a given week number (say "44") into a "Variance Week?"  Can we assume that a "Budget Week" is any week that also has a "Yes" in the "Actual/Budget" field?  

(See, part of your difficulty may be that you haven't answered these questions.  Or, you know and you just didn't tell us yet.  Without knowing these answers, I at least, don't yet have enough information to give you an answer.)
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6941220
I believe the field Variance week means, the difference between the budgeted amount and the actual posted amount for this Account NO.  It would be calculated with each posting.  Is that correct??

Bob Scriver
0
 

Author Comment

by:clballas
ID: 6941820
Yes, the secondary tables are just conversion tables to determine the rollup criteria.  And yes, the variance week is the difference between the budgeted amount and the actual amount.  A Yes in the Actual/Budget field indicates that it is an actual amount and a No indicates that it is a budget amount.  

Sorry that I didn't make these things clear.
0
 

Author Comment

by:clballas
ID: 6941827
Yes, the secondary tables are just conversion tables to determine the rollup criteria.  And yes, the variance week is the difference between the budgeted amount and the actual amount.  A Yes in the Actual/Budget field indicates that it is an actual amount and a No indicates that it is a budget amount.  

Sorry that I didn't make these things clear.
0
 

Author Comment

by:clballas
ID: 6941870
I also set up a table in which to input the current week number to determine which week, month, qtr to report.
0
 
LVL 3

Accepted Solution

by:
forester earned 300 total points
ID: 6942044
Can you describe the table fields (list them) for the week, month, quarter etc.

Given your table structure, the first thing you will need is a query that joins the two tables. This will be a more or less permanent query. Then you will run the additional queries for the Actual Month-To-Date, etc. off this query.

The first set of two queries is quite simple.

Would be something like this - except that you want to run this query off your joined query to perhaps get the week number correct for your system of two tables.

SELECT [Table1].[Account Number], [Table1].[Dept], [Table1].[Week Number], [Table1].[Amount] AS ActualWeek
FROM Table1
WHERE Actual_Budget=True
ORDER BY [Dept], [Account Number], [Week Number];

One for "ActualWeek," one like this for "BudgetWeek" with Actual_Budget = False.

The next set (the pattern for all the remaining queries) would go something like this - except that you want to run these queries off the join of the two tables. so that your 'monht-to-date', 'quarter-to-date' etc. come out correctly.

SELECT [Table1].[Dept], [Table1].[Account Number], [Table1].[Week Number], SUM([Table1].[Amount]) AS ActualMTD
FROM Table1
WHERE Actual_Budget <>0 AND [Week Number] BETWEEN 3 AND 7
GROUP BY [Dept], [Account Number], [Week Number], [Amount]
ORDER BY [Dept], [Account Number], [Week Number];

You want to put these queries into your report as the field specification for each column.

Is this clear enough?  (If we had your exact table and field names, we could give you the basic patterns exactly.)

Pam

Hope this helps
0
 
LVL 3

Expert Comment

by:forester
ID: 6942178
Sorry for the delay, clballas. My ISP went down late last night for maintenance.  I'm in the middle of a big legal analysis episode here, but can help you during the day and tonite, if you want. I too have worked on something all weekend and been under a deadline.
0
 

Author Comment

by:clballas
ID: 6942460
Pam, in entering the second query, I am getting the error message
"You tried to execute a query that doesn't include the specified expression 'WeekNum' as part of an aggregate function."
What am I doing wrong?

Connie

p.s.
my table DATA has these fields: DataID, Dept, AcctNum, WeekNum, Actual, Amount

my table WEEKNUM has these fields: WeekNum, WeekEndingDate, Week, Month, Qtr (for example week number 14 with week ending date of 4/7/02 is month 4 and qtr 2)

my table CURRENTWEEK has one field CurrentWeek and is joined to the WEEKNUM table through QRYCURRENTWEEK
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Expert Comment

by:forester
ID: 6942725
Connie,

The error msg means that in the GROUP BY clause of the query, you must enter the field called WeekNum.

Then it will work OK. It won't necessarily report out the WeekNum field, but it will group by it.

Please note that the functioning of queries that use the GROUP BY clause are sometimes sensitive to the order in which you list the fields.

Could you please post the SQL contents of your QRYCURRENTWEEK so that I could take a look at it?  I'll try to simulate the rest of your table structures and come back with some exact queries for you. Need to finish my current writing, and then will get right to it.
0
 

Author Comment

by:clballas
ID: 6942889
qryCurrentWeek:
SELECT DISTINCTROW CurrentWeek.WeekNum, Tbl_WeekNum.WeekEndDate, Tbl_WeekNum.Week, Tbl_WeekNum.Month, Tbl_WeekNum.Qtr
FROM Tbl_WeekNum INNER JOIN CurrentWeek ON Tbl_WeekNum.WeekNum = CurrentWeek.WeekNum;

Question - when you say to put these queries into my report as the field specifications for each column where exactly do you mean?  This part is new to me and I'm just not sure how to do this.  

BTW-You are a godsend Pam.  
0
 
LVL 3

Expert Comment

by:forester
ID: 6942897
Not much of a godsend, really. I've got a major project of my own, and time is of the essence.  When you establish the twelve columns in your report, you will be putting in fields for the content of those columns. You will want to load these individual queries into those fields, rather than having them simply be fields from the tables.

If you're not conversant with this, you need to go grab a book on Access as quick as you can. I'd suggest Alison Balter's Mastering Access 2000 or some version of her book (for Access97, for Access2002, it doesn't matter). This is because I really haven't got time to help you with report-writing as well.

Thanks for posting the query. I'll find some more time in a little bit to help you.  What are your deadlines?  If this could be put off until Thursday morning, I could do a great job for you then. If not, I'll be able to help in little bits and pieces of time.
0
 
LVL 3

Expert Comment

by:forester
ID: 6943972
Bob Scriver, maybe you can help Connie more than I can, as you seem to be familiar with this kind of problem. Especially if this for a work situation.  However, Connie, is this for a school homework assignment? In that case, we have a different problem on our hands.  See below.

I'm confused about two things,although the rest seems straightforward

1) It must be that you're handling transaction records of some kind, and there must be multiple records for each week. At least one of these must be a budget amount and at least one other must be an actual amount. Otherwise we cannot compute a variance amount for the week.  Is this correct?  

  So, if this is the case, then you are wanting sums of many transaction records for each week.  (Am I still on track here?)

2) Connie, I don't see the need for your third table called CURRENTWEEK.  Am I missing something here?  It is sufficient to have a reference or lookup table to make a given week number have a corresponding month and quarter. So, I can't figure out what function you intended for your third table.

There are several ways this problem can be approached. Before showing you how, could you please tell us what this is for - especially if it is for a school homework assignment.  If it is, there may be some particular lessons your teacher is trying to teach, and the solution should illustrate those.  If this is for a work situation, and not a school assignment, we need to know a few other things about this before selecting the best approach to help you.
0
 
LVL 3

Expert Comment

by:forester
ID: 6944015
Connie, this is not a difficult problem, if the above premises (1 and 2) are correct.  

Basically you need one query like that shown below, and the rest of the work is done in the Report.

SELECT Transactions.Dept, Transactions.AcctNum, Transactions.[Amount Type], Transactions.Amount, WeekNum.WeekNum AS WeekNum_WeekNum, WeekNum.WeekEndingDate, WeekNum.Week, WeekNum.Month, WeekNum.Qtr
FROM WeekNum INNER JOIN Transactions ON WeekNum.WeekNum = Transactions.WeekNum;

I took the liberty of renaming your table DATA to be "Transactions."  Calling a table "DATA" is a very bad practice as the word is used for many other things throughout Access. I also changed the name of your field you called "Actual" to be "Amount Type" as this is a flag field. That is, you're using the field to state what kind of a transaction / what kind of amount the currency in Amount represents.

The report has this query as the data source. All the summing of the transactions for week, month, quarter and year to date are done in summary fields in the report itself. If you build a report, using the Report Wizard, you will find a place where it asks you is the data supposed to be "Individual Detail" or "Summary."  If it is "Summary", the wizard will ask you for some further information about which fields are to be summed.  When you look at the report it generates in Design View, you can see how these summary fields are constructed.

OK?
0
 

Author Comment

by:clballas
ID: 6944132
This is not a school assignment; it is for work.  There are two records for each week: actual and budget.  The variance amount is the difference between the two.  There are many transactions because there are multiple departments and within each department are multiple general ledger accounts (such as compensation, benefits, taxes etc).

I set up the CURRENTWEEK table so that I have a place to hold the current week number.  I use that number to create a subset of the transactions for the cumulative columns.  This is the query that does that:

SELECT Transactions.AcctNum, Transactions.Dept, Transactions.WeekNum, Transactions.Actual, Transactions.Amount
FROM Transactions
WHERE (((Transactions.WeekNum)<=(SELECT [WeekNum] FROM [CurrentWeek])));
0
 

Author Comment

by:clballas
ID: 6944201
From your comment from 6:49 a.m. yesterday, I constructed the various MTD, QTD, and YTD queries but I get stuck on how to put these queries into the report to get all the columns on the same output page.  I feel like a real dunce; there must be some concept here I'm just not getting.  
0
 
LVL 3

Expert Comment

by:forester
ID: 6945578
OK, Connie, all this info helps.  And last question for now - about your deadline?

If you send me an e-mail address, I can send you a complete mdb example on Thursday, if this will not be too late.  I have about half of this worked up for you now, but still have got my own urgent problem.

In fact, if you like, if you send me an email address, I'll send you what I've got put together so far, and will keep sending you updates as my time permits me to work on it.  Will this be OK for you?

Don't feel like a dunce. We've all of us been there.
0
 

Author Comment

by:clballas
ID: 6969209
I will be assigning points to you Pam after I increase the points to try to give you sufficient credit.  Your help was invaluable and I will send you an email with specifics but I want to make sure I thank you publicly here.  Thanks so very, very much.
0
 
LVL 3

Expert Comment

by:forester
ID: 6969541
Connie, have you gotten the MTD, QTD and YTD into your reports in the way you wished?  

An no need to increase the points for the question. We're all just here to help - we were all helped once.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now