12mth Budget report construction

[john603]

Hi everyone,



I am new to Access. However, I do have some programming background. The challenge for me now is to construct a 12mth budget report for the company. I have used VBA to construct budget templates for years and is now pretty sick of data validation rules in Excel which no matter how I set it up, some smart ass will find a way to break it. The other reason I prepare to switch to Access is to fully appreciate the real database engine capacity which you all know better than me.

Now, I have read quite a lot of books and prepare to get started. I don't what go to table construct level as my problem now is how to construct a 12mth budget report. let's assume that I finally get what I need from query table or form. The data format in the final report should look like this and all income, exp records are ready in the query/queries.

Jan Feb Mar ..................... Dec
income 1-1
income 1-2
income 1-3
subtotal income 1

income 2-1
income 2-2
income 2-3
subtotal income 2

Total income = inc1+inc2

expense 1-1
expense 1-2
expense 1-3
subtotal exp1

Gross margin = Total income1 - Subtotal exp1

expense 2-1
expense 2-2
expense 2-3
subtotal exp2

Profit = Gross margin - subtotal epx2

I have no problem using aggregate group function in report design which basically I just assign the income and exp to different group and sum it up in the report. However, the real challenge to me is how I get "Total Income", "Gross Margin" and "Profit" in the same report? A text control will have to be added in group/page/report level and I don't want that repeated several times but just once.

If I have confused any concepts, please have a look at template in .pdf I attached which should look similar to what I want to achieve in Access. I really hope there will be some good news! thanks in advance.

John

[rpeare]

Here's what I would do.

In your GL table (I'm assuming your income and expense categories are GL categories)

Have something like:

tbl_GL
GL_ID  GL_Desc      GL_Group  
1      Income 1-1   1         
2      Income 1-2   1         
3      Income 1-3   1         
4      Income 2-1   2         
5      Income 2-2   2         
6      Income 2-3   2         
7      Expense 3-1  3         
8      Expense 3-2  3
9      Expense 3-3  3
10     Expense 4-1  4
11     Expense 4-2  4
12     Expense 4-3  4

In your report sorting/grouping you can then split it by the GL group and put in subtotals for each of those groups.

From there you can either use domain functions (dsum) OR you can create four different subreports (one for each category) and perform the summing you want based on a formula that gets the value of the items reported in the subforms to get the reporting structure you want. You could also add meta groups (i.e either artificially or in the GL table defining, say, that all income has a meta group of one, all expenses have a meta group of 2) and have a second set of sorting and grouping based on the meta group.

[Dal Jeanis]

1) Okay, if you've read a LOT of books, then take a quick look over at Access MVP Roger Carlson's site http://rogersaccesslibrary.com and review his simple tutorials on database design and application design. He has sample forms and practice case studies for an entity relationship diagram. Doing a proper ERD will save you dozens of hours of trouble for each hour you spend in design up front. it will also help simplify your thinking, if you initially tend to overcomplicate things (the way I do).

2) Your desired layout is a pretty normal crosstab query. See Allen Browne's site for general notes about crosstabs. http://allenbrowne.com/ser-67.html

3) You can lay crosstabs out vertically and horizontally by using the method rpeare suggests, with a few tweaks to get a sum column. I built something similar in post 10 of this thread - https://www.accessforums.net/queries...sis-39592.html. You can use that post for a reference example, and holler if you run into any trouble.

But first, you need the underlying schema and data.

[john603]

Hi rpeare, your method is my initial plan. However, I am not so sure that the subform format can be seen as one report when I start to rebuild the whole ribbon. The idea is to provide managers with front end Access application with backend database managed by me on one single computer served as a server.

Dal Jeanis, I must say maybe I have not read the books properly or completely that I have not seen the concept of a crosstab query. It is certainly very interesting topic. My plan is to do as much research as I can before I start. I am well aware the benefit of doing so. I have considered the option to go to PHP + MySQL combination which would give me more freedom to do what I want including migrating whole thing to WEB. However, the learning task is huge. So, compare to Access, PHP would be my second choice as an accountant.

The current company budget data is sufficient which need to be slimmed which professionally called normalisation. I guess until I decide the method I would be using in the final report, the query table can not be decided and the same to table relationship design.

I will do some research and come back to this thread again. thanks, guys. very helpful.

[rpeare]

I am not a huge fan of using crosstabs except in very specific cases. The problem with them is that if, for instance, if you wanted to print this report at the beginning of the year you would end up with a column only for january, the remaining columns simply would not be there because there is no data to support them. Crosstabs rely on there actually being data in the table to create a column. There are ways around that of course, for instance building a table that has all the months on it then making a cartesian query and basing your crosstab on the cartesian query but it's very clunky to do it that way.

I'll work on an example and try to post later today.

[rpeare]

John603.zip

Here's an example of doing it without subforms.

I've shaded the Gross Margin and total income areas and shown set their height to 1 pixel, then set the CAN GROW property to YES. This way they are only shown if there is data in the subsection and use up minimal space in your report design.

I didn't know if your revenue was recorded as + and your expense recorded as - (most systems I've seen store both as positives and you have to figure loss/gain by subtracting expense from revenue instead of doing a straight sum but you can modify the formulas if you aren't doing things that way).

[Dal Jeanis]

john - It can be inefficient to try to learn everything before you start practicing. Access is an excellent tool for iterative work - successive approximation. The great thing is that you can reformat and rearrange the database as you go. Make your best guess, keep the data as granular as possible (simple tables that each represent one thing), populate some test data, and begin getting comfortable with queries, tables and forms.

Crosstabs are one of the esoterics of the business. Don't spend much time on them until you understand the basics. Most Access programmers don't use them at all, then there are a few magicians who use them for AMAZING THINGS, generally people who wandered in from Excel where crosstabs and pivot tables (different animals with similar names) were commonly used things integral to the "sexy" presentation of data tables.

[john603]

John603.zip

Here's an example of doing it without subforms.

I've shaded the Gross Margin and total income areas and shown set their height to 1 pixel, then set the CAN GROW property to YES. This way they are only shown if there is data in the subsection and use up minimal space in your report design.

I didn't know if your revenue was recorded as + and your expense recorded as - (most systems I've seen store both as positives and you have to figure loss/gain by subtracting expense from revenue instead of doing a straight sum but you can modify the formulas if you aren't doing things that way).

hello, rpeare,

You are the men! I have not fully understood what you did but the result is what I wanted. Seems I got a long way to go as it appears quite simple and straightforward. Real appreciated. I will have a thought about it this week and see whether I got more questions. Cheers.

John

[Dal Jeanis]

john - Some times it takes a master to pare a requirement to its simplest form. rpeare has earned his VIP status time after time by demonstrating that ability.

[john603]

Ok, I managed to understand some of the concepts in the template. rpeare tried to differentiate each group in the final report and give them a name in the table so he can sum them up in the final report accordingly. However, I don't understand what is GL_Meta column for in the query. Maybe that was the initial plan to group final report? but DSuM is doing the trick.

very special thanks to rpeare, the templates actually points out several main ideas. The event idea is basically how we construct the accounting software. In the budget, income/exp has to be 12mth format so I can modify that slightly. The payroll data can follow the event idea just to divide 12mths.

Now, I am excited to construct the budget 12mth report. Although it has a long way to go but at least I have the direction now. Thanks again.

[rpeare]

the GL META is a construct (formula) just so I could produce the grayed out subtotal areas. It's possible to do it without the GL meta in there, it's just what I normally do if I have to perform calculations other than a straight subtotal for a section (in your case subtotaling all income, then subtotaling all income minus one section of expense) that way if something else comes up I'm prepared for it.