Creating Report From Multiple Sources in Access 2010

[JeffGeorge]

Ok, you asked for it! I have no idea what this is going to look like but this is the ultimate goal. Each of this non-calculated values combines a number of G/L account numbers (often in sequence but not always), a period, and a fiscal year. I'm not sure I understand what you are saying about building my own table, but my brain is getting soggy from staring at these numbers. I'd consider it a victory for now if I could just click on the report icon, have it prompt for a period and for a fiscal year and get just the first column of data. So far I've been able to get it to prompt for the year in the Gross Sales query but I I'm not quite sure how to get it to take a requested period and then use that and sum it. And even if I could that only gets the first number in the column. I don't want it to prompt subsequently for each query. I'm used to building forms to input data into a table and building reports to pull data from tables. This is quite different, at least I think it is, from things I've done in the past.

Summary Form								Variance to		Variance to								Variance to		Variance to
(in dollars)		January 2013	% of Sales	Budget	% of Sales	January 2012	% of Sales	Budget		Prior Yr		YTD 2013	% of Sales	YTD Budget	% of Sales	YTD 2012	% of Sales	Budget		Prior Yr
Gross Sales		$ 6,450,000	100.0%	$ 5,000,000	100.0%	$ 8,000,000	100.0%	$ 1,450,000		$ (1,550,000)		$ 6,450,000	100.0%	$ 5,000,000	100.0%	$ 8,000,000	100.0%	$ 1,450,000		$ (1,550,000)
								$ -		$ -								$ -		$ -
Outbound Freight		$ 125,000	1.9%	$ 75,000	1.5%	$ 200,000	2.5%	$ 50,000		$ (75,000)		$ 125,000	1.9%	$ 75,000	1.5%	$ 200,000	2.5%	$ 50,000		$ (75,000)
Sales allowances		$ 12,000	0.2%	$ 10,000	0.2%	$ 15,000	0.2%	$ 2,000		$ (3,000)		$ 12,000	0.2%	$ 10,000	0.2%	$ 15,000	0.2%	$ 2,000		$ (3,000)
								$ -		$ -								$ -		$ -
Net Sales		$ 6,313,000	97.9%	$ 4,915,000	98.3%	$ 7,785,000	97.3%	$ 1,398,000		$ (1,472,000)		$ 6,313,000	97.9%	$ 4,915,000	98.3%	$ 7,785,000	97.3%	$ 1,398,000		$ (1,472,000)
Material COS		$ 4,800,000	74.4%	$ 3,800,000	76.0%	$ 6,000,000	75.0%	$ 1,000,000		$ (1,200,000)		$ 4,500,000	69.8%	$ 3,800,000	76.0%	$ 6,000,000	75.0%	$ 700,000		$ (1,500,000)
Direct Labor		$ 250,000	3.9%	$ 200,000	4.0%	$ 280,000	3.5%	$ 50,000		$ (30,000)		$ 250,000	3.9%	$ 200,000	4.0%	$ 280,000	3.5%	$ 50,000		$ (30,000)
Mfg Overhead		$ 715,000	11.1%	$ 605,000	12.1%	$ 834,000	10.4%	$ 110,000		$ (119,000)		$ 620,000	9.6%	$ 605,000	12.1%	$ 834,000	10.4%	$ 15,000		$ (214,000)
Total COS		$ 5,765,000	89.4%	$ 4,605,000	92.1%	$ 7,114,000	88.9%	$ 1,160,000		$ (1,349,000)		$ 5,370,000	83.3%	$ 4,605,000	92.1%	$ 7,114,000	88.9%	$ 765,000		$ (1,744,000)
Gross Margin		$ 548,000	8.5%	$ 310,000	6.2%	$ 671,000	8.4%	$ 238,000		$ (123,000)		$ 943,000	14.6%	$ 310,000	6.2%	$ 671,000	8.4%	$ 633,000		$ 272,000
Mfg. Overhead Detail:
Indirect Wages		$ 250,000	3.9%	$ 200,000	4.0%	$ 285,000	3.6%	$ 50,000		$ (35,000)		$ 200,000	3.1%	$ 200,000	4.0%	$ 285,000	3.6%	$ -		$ (85,000)
Benefits		$ 150,000	2.3%	$ 135,000	2.7%	$ 180,000	2.3%	$ 15,000		$ (30,000)		$ 150,000	2.3%	$ 135,000	2.7%	$ 180,000	2.3%	$ 15,000		$ (30,000)
Supplies		$ 80,000	1.2%	$ 60,000	1.2%	$ 98,000	1.2%	$ 20,000		$ (18,000)		$ 60,000	0.9%	$ 60,000	1.2%	$ 98,000	1.2%	$ -		$ (38,000)
Maint & Repairs		$ 90,000	1.4%	$ 80,000	1.6%	$ 121,000	1.5%	$ 10,000		$ (31,000)		$ 80,000	1.2%	$ 80,000	1.6%	$ 121,000	1.5%	$ -		$ (41,000)
O/S Services		$ 35,000	0.5%	$ 25,000	0.5%	$ 45,000	0.6%	$ 10,000		$ (10,000)		$ 25,000	0.4%	$ 25,000	0.5%	$ 45,000	0.6%	$ -		$ (20,000)
Licenses & Fees		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -
Leases		$ 30,000	0.5%	$ 30,000	0.6%	$ 30,000	0.4%	$ -		$ -		$ 30,000	0.5%	$ 30,000	0.6%	$ 30,000	0.4%	$ -		$ -
Utilities		$ 15,000	0.2%	$ 15,000	0.3%	$ 15,000	0.2%	$ -		$ -		$ 15,000	0.2%	$ 15,000	0.3%	$ 15,000	0.2%	$ -		$ -
Depreciation		$ 60,000	0.9%	$ 55,000	1.1%	$ 55,000	0.7%	$ 5,000		$ 5,000		$ 55,000	0.9%	$ 55,000	1.1%	$ 55,000	0.7%	$ -		$ -
Total Mfg Overhead Detail		$ 715,000	11.1%	$ 605,000	12.1%	$ 834,000	10.4%	$ 110,000		$ (119,000)		$ 620,000	9.6%	$ 605,000	12.1%	$ 834,000	10.4%	$ 15,000		$ (214,000)
Selling & General Administrative Expense:
Wages		$ 120,000	1.9%	$ 120,000	2.4%	$ 125,000	1.6%	$ -		$ (5,000)		$ 120,000	1.9%	$ 120,000	2.4%	$ 125,000	1.6%	$ -		$ (5,000)
Benefits		$ 30,000	0.5%	$ 28,000	0.6%	$ 35,000	0.4%	$ 2,000		$ (5,000)		$ 30,000	0.5%	$ 28,000	0.6%	$ 35,000	0.4%	$ 2,000		$ (5,000)
Travel		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -
Office Supplies		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -
Dues & Subscriptions		$ 2,500	0.0%	$ 2,500	0.1%	$ 2,500	0.0%	$ -		$ -		$ 2,500	0.0%	$ 2,500	0.1%	$ 2,500	0.0%	$ -		$ -
Advertising & Sales Promotion		$ 3,500	0.1%	$ 3,500	0.1%	$ 3,500	0.0%	$ -		$ -		$ 3,500	0.1%	$ 3,500	0.1%	$ 3,500	0.0%	$ -		$ -
Professional Fees		$ 35,000	0.5%	$ 35,000	0.7%	$ 36,000	0.5%	$ -		$ (1,000)		$ 35,000	0.5%	$ 35,000	0.7%	$ 36,000	0.5%	$ -		$ (1,000)
Phone		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -
Rent		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -		$ 3,000	0.0%	$ 3,000	0.1%	$ 3,000	0.0%	$ -		$ -
Taxes		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -		$ 5,000	0.1%	$ 5,000	0.1%	$ 5,000	0.1%	$ -		$ -
Misc		$ 200	0.0%	$ 200	0.0%	$ 200	0.0%	$ -		$ -		$ 200	0.0%	$ 200	0.0%	$ 200	0.0%	$ -		$ -
Total SG&A Expense		$ 210,200	3.3%	$ 208,200	4.2%	$ 221,200	2.8%	$ 2,000		$ (11,000)		$ 210,200	3.3%	$ 208,200	4.2%	$ 221,200	2.8%	$ 2,000		$ (11,000)
Operating Income		$ 337,800	5.2%	$ 101,800	2.0%	$ 449,800	5.6%	$ 236,000		$ (112,000)		$ 732,800	11.4%	$ 101,800	2.0%	$ 449,800	5.6%	$ 631,000		$ 283,000
Other Income & Expense		$ 15,000	0.2%	$ 15,000	0.3%	$ 20,000	0.3%	$ -		$ (5,000)		$ 15,000	0.2%	$ 15,000	0.3%	$ 20,000	0.3%	$ -		$ (5,000)
Net Income		$ 322,800	5.0%	$ 86,800	1.7%	$ 429,800	5.4%	$ 236,000		$ (107,000)		$ 717,800	11.1%	$ 86,800	1.7%	$ 429,800	5.4%	$ 631,000		$ 288,000

[rpeare]

Ok, I don't see a reason this can't be done, your budget number was not part of the original data but January numbers and the YTD numbers are very gettable. Based on this view, the only things you need are the month totals, the year to date totals and the total budget. If you're putting this on a report you do not need any of the calculation fields to be in the query. You can put those as unbound fields on the reports and simplify your query even further.

So, based on your original example of the table which field contains the numbers for january? is it the 'starting balance' column?

[JeffGeorge]

Yeah, I forgot to mention (actually I overlooked it until just now) that there is also a field that flags the record as either Actual or Budget. I guess that was too far down the road for me to see it.

PERIOD_1 is the field which contains any January data, assuming that this is the period the user requests. I think I'm going to force them to use numbers 1-12 for the period just to keep it simple. Then I can, somehow, append their entry to a PERIOD_ value or something. I'm not quite sure about that yet.

In addition we only want records that match the FISCAL_YEAR that they will input. In the example above it would be 2013. Each of the non-calculated values comes from records where the account number matches what the company has defined as that type of item. For example, the Gross Sales value equates the last query I posted, i.e. a sum of accounts >="4010.01" And <="4050.04". The other items are similar but with different G/L accounts associated, and it's all purely arbitrary.

So right now I have a half-built master query which calls a bunch of other queries to then populate the report data. The master query also includes some calculated values. I tried to calculate them at report run time but since some calculated values require other calculated values I was getting prompted for data which I want automatically calculated.

I'm also thinking of building a table that includes just the fields I need from the G/L table and then assign each record a type based on account number, as you suggested. But I'm not sure that will make anything easier as I will simply have the source data in a different table.

[rpeare]

PERIOD_1	DIVISION	GLACCOUNT	FISCAL_YEAR	TRAN_TYPE	BEG_BAL	PERIOD_2	PERIOD_3	PERIOD_4	PERIOD_5	PERIOD_6	PERIOD_7	PERIOD_8	PERIOD_9	PERIOD_10	PERIOD_11	PERIOD_12	PERIOD_13	PERIOD_A	CUR_BAL	CREATEDATE	CREATETIME	CREATEUSER	CREATESTATION	LASTDATE	LASTTIME	LASTUSER	LASTSTATION
-1286	1	4010.01	2013	A	5866.6	10122.69	-10203.29	378.36	482.14	-608.7	2059.02	-978.8	586.8	-178.41	-23.73	-1716.68	0	0	4500	03-Jan-06	03-Jan-06	LW	CASHIER	16-Jan-07	16-Jan-07	KR	KRAMIN
1000	1	4010.02	2013	A	0	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000			1200
-500	1	4050.04	2013	A	10	-500	-500	-500	-500	-500	-500	-500	-500	-500	-500	-500			-6000

Ok if period_1 = january I assume period_12 = december.... so what is period_13? I don't need anything past the CUR_BAL field to do the calculations, now the next step for me is can you give an example of the raw data that you are using to create the example of your finished product because you're talking about GL accounts in the 4000 range but your GLACCOUNT on this summary table is 1000.

Does the Tran_Type indicate ACTUAL vs BUDGET?
Assuming TRAN_TYPE does indicate ACTUAL vs BUDGET, are your budget amounts divided evenly across the months (1/12th of the budget per period for the year) or is it unevenly distributed?
Would I expect to see data like I've added to your original example that would be summed in your GROSS SALES line for the current year and a similar set of data for the previous year (2012)?
Would I expect to see a similar set of data for the BUDGET for the year?

[JeffGeorge]

You are correct on PERIOD_12 = December. Most financial software allows you a 13th financial period if you wish to use it but this in this example it is not used.

You are also correct that TRAN_TYPE denotes whether the values represent Actual (A) or Budgeted (B). However, from a cursory glance at the table the budgeted amounts are not strictly 1/12 of the total.

You would expect to see the summed values for both the current year (or whatever year the user input) and year - 1, i.e. the prior to the user's selected year. Same with budgets. The only real difference between the two types of data is the TRAN_TYPE. Otherwise they should be utilized exactly the same.

Here's an example of a record that would be part of the Gross Sales calculation. A quick query shows me that there are a total of 25 account records that would fall in the range of G/L accounts used for Gross Sales.

DIVISION	GLACCOUNT	FISCAL_YEAR	TRAN_TYPE	BEG_BAL	PERIOD_1	PERIOD_2	PERIOD_3	PERIOD_4	PERIOD_5	PERIOD_6	PERIOD_7	PERIOD_8	PERIOD_9	PERIOD_10	PERIOD_11	PERIOD_12	PERIOD_13	PERIOD_A	CUR_BAL
1	4010.01	2002	A	0	-480878	-463538	-450545	-533388	-579175	-563734	-514418	-524559	-610504	-694123	-535105	-462287	0		-6412256

[rpeare]

JeffGeorge.zip

I'm attaching an example database, my tblTest is a table in the layout with the field names that you have in your example data.

the GL groups I added myself. As far as I can tell you have some calculation subtotals in your report they are:

NET SALES
TOTAL COS
GROSS MARGIN
TOTAL MFG OVERHEAD
TOTAL SG&A EXPENSE
NET INCOME

I'm not sure about

OPERATING INCOME
OTHER INCOME & EXPENSE

but they appear to be gl categories not subtotal calculations

What I haven't figured out yet is how you get to your net income line based on the numbers on the report.

At any rate you can see in the query how to get your current year actual and current year budget information along with the previous year actual and previous year budget in one query.

Just open the form frm_reportsetup, put in FY 2013, pick a month and run the query, if you build a report based on that query you can put in all those percent calculations without much effort and put in subtotalling as necessary. You'd probably want to set up sub categories to aid in your subtotaling on the report as well, for instance setting up GROSS INCOME, OUTBOUND FREIGHT and SALES ALLOWANCE with an artificial SORT of 1 2 and 3 and give them a GROUP 1 1, then in your GROUP FOOTER for GROUP 1 put in your subtotaling. I haven't really worked on that part, just enough to show you how to do it in one query as opposed to a bunch of different ones.

[JeffGeorge]

Wow. I can't wait to take a long look at this. I won't be back on the job site until next week so I don't have access (no pun intended) to my source data until then. Do you do this just because you like the software a lot? I've always been an advocate of Access but I seem to always forget the things I learn in crash courses.

[rpeare]

I hang around this board because when I have downtime I'd rather look for things I don't know how to do and try and figure them out. I don't know how many projects people have posted that I didn't know how to do when I responded but went web crawling and created examples. Yours wasn't one of those but it's complex enough that it's going to be hard for a neophyte to get their head around. I've never taken a course on Access, it's all self taught/google, but over time you run into things you don't know how to do or want to be able to do and after you've done it once you know where to look again if you forget.

[JeffGeorge]

I'm attaching an example database, my tblTest is a table in the layout with the field names that you have in your example data.

the GL groups I added myself. As far as I can tell you have some calculation subtotals in your report they are:

NET SALES
TOTAL COS
GROSS MARGIN
TOTAL MFG OVERHEAD
TOTAL SG&A EXPENSE
NET INCOME

I'm not sure about

OPERATING INCOME
OTHER INCOME & EXPENSE

but they appear to be gl categories not subtotal calculations

What I haven't figured out yet is how you get to your net income line based on the numbers on the report.

At any rate you can see in the query how to get your current year actual and current year budget information along with the previous year actual and previous year budget in one query.

Just open the form frm_reportsetup, put in FY 2013, pick a month and run the query, if you build a report based on that query you can put in all those percent calculations without much effort and put in subtotalling as necessary. You'd probably want to set up sub categories to aid in your subtotaling on the report as well, for instance setting up GROSS INCOME, OUTBOUND FREIGHT and SALES ALLOWANCE with an artificial SORT of 1 2 and 3 and give them a GROUP 1 1, then in your GROUP FOOTER for GROUP 1 put in your subtotaling. I haven't really worked on that part, just enough to show you how to do it in one query as opposed to a bunch of different ones.

I traveled back to the job site this morning and I'm going to try to multitask on these reports a bit while working on some other operational implementations. I am impressed by the database you've included! It's very much appreciated as is the work that you've put into it.

I wanted to clarify a couple of things for you since you mentioned them in the above post.

OTHER INCOME & EXPENSE is, indeed, a G/L account value, though at the moment I'm not sure what account they are using for this. However, OPERATING INCOME is a calculation of the [Gross Margin]-[Total SG&A Expense]. NET INCOME is the [Operating Income]-[Other Income & Expense].

The first thing I did was look at your query and get depressed. The expressions you wrote might be a bit out of my league, though with some time and review I think I can decipher them. I'm not sure what part the Group By on GLGroup plays since there is only one record in that table. How do I run the report? I'm probably being unintentionally dense but when I opened it I was prompted for a year and month but I don't see how to make it continue after that. I definitely see now that what I'm trying to do is possible; it's just going to require learning some things about Access that I've never used before, which I'm not opposed to.

[rpeare]

The formulas are easier to look at if you break them down:

CYYTDActualTotal: Sum(IIf([tran_type]="A" And [fiscalyear]=[forms]![frm_reportsetup]![fld_fy], TRUEPART, FAILPART))

This is saying if the tran_type is A (actual) and the fiscal year matches the year on the form frm_reportsetup do the TRUEPART

In this case the TRUEPART is this formula:

,[period_1]
(January, because january will always be included)
+IIf([forms]![frm_reportsetup]![fld_month]>=2,[period_2],0)
(if the month indicated on the form frm_reportsetup is greater than 2, or february, add the period_2 number)
+IIf([forms]![frm_reportsetup]![fld_month]>=3,[period_3],0)
(same logic for march, and all subsequent months)
+IIf([forms]![frm_reportsetup]![fld_month]>=4,[period_4],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=5,[period_5],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=6,[period_6],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=7,[period_7],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=8,[period_8],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=9,[period_9],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=10,[period_10],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=11,[period_11],0)
+IIf([forms]![frm_reportsetup]![fld_month]>=12,[period_12],0),

In this case the FALSEPART is always 0 so it's not included in the Sum

it's the same basic logic for all the other columns, the previous year numbers are based on [forms]![frm_reportsetup]![fld_fy] - 1

You gave me a very limited dataset and I didn't want to put in a bunch of data myself. but the basic premise here is that as long as your GL codes go to one AND ONLY ONE group in your report you can build the query to show those GL categories, then do the subtotals/calculations based on the report, not in the query itself.

Of course you'd have to also update the table tblGLGroups to include all the GL codes and. What I'd do with that tblGLGroups is to also add a numbering system so that you could group on them more easily in a report, for instance your

GROSS SALES, OUTBOUND FREIGHT, SALES ALLOWANCE would be group 1

and you'd likely want a sort order as well so instead of being the GL Code and Group, you'd also have a GL_ReportGroup and GL_ReportSort so you could arrange the the GL groups anyway you wanted to by modifying the order on your table.

The layout of the report you showed is a bit difficult because you'd have to hide formula fields based on which group footer when you are performing calculations based on group sums (for instance NET SALES - Some other category net dollars) What I typically do in these types of reports is put in a subtotal by group then do the calculations that rely on multiple groups at the very end of the report (report footer).

If you have a text file available (so that I do not have to enter a bunch of data) I might have time to work the example a bit further. Just make sure it includes at least one GL code for each of your report categories and tell me which code goes into which group (GROSS SALES, OUTBOUND FREIGHT, etc)

[JeffGeorge]

I'm attaching a sample set of data in the form of a text file. Since I don't know all the accounts that will be used for the various categories on the report template, it's ok to focus on just the top 5, i.e. Gross Sales, Outbound Freight, Sales Allowances, Material COS, and Direct Labor. I've added a field uses these names for the appropriate accounts. For example, if the account should be include as part of Gross Sales then the accountType field will be "Gross Sales". Of course, we could just always pick random accounts to use for anything during this phase, but I thought maybe for playing around we might want to keep the items involved to a minimum.

I'm still not sure I understand everything you are saying about grouping and categorizing, but maybe I'll understand better if I see a sample that shows the Net Sales, the Total COS, and the Gross Margin.

[rpeare]

What do the columns represent, I'm working on the assumption that the third field corresponds to Period_1?

All of the data in the sample data is Actual (Tran_Type = A) so the query won't work because there's no budget data, can you create one that has both the actual and budget with multiple fiscal years?

You've got 284 GL codes with multiple fiscal years in your data which is good, I can just randomly assign some of the codes to each one of the groups, but again I don't want to do a bunch of data entry just to get a budget amount in there.

[JeffGeorge]

My bad; I forgot to include column headings. Strangely enough, I reviewed the source data there are no budget entries after 2011. Why they are asking for them in the template is anyone's guess. I imagine that their lack of familiarity with their own software means that they don't even know it's not in there. So forget the budgets. I understand how you are populating the query results so I'm now more interested in seeing how I would turn the data into a report with calculations and variances. The fields in the data I gave you are GLACCOUNT, FISCAL_YEAR, PERIOD_1, PERIOD_2, PERIOD_3, PERIOD_4, PERIOD_5, PERIOD_6, PERIOD_7, PERIOD_8, PERIOD_9, PERIOD_10, PERIOD_11, PERIOD_12, TRAN_TYPE, accountType. I notice that I failed to assign Sales Allowance account so you can forget about those as well, or you can use the range 9625.00-9626.02.a

[rpeare]

So I guess I'd ask the client why they want the budget data on there if they aren't putting any in. Do they plan to in the coming fiscal year? do they plan to back enter fy 2013 so you can actually produce a usable report?

Seriously, if you don't have the data to produce the report what's the point?

You could create a report comparing fiscal years against one another, but the budgeting part will be all 0's and all your percentages won't be worth printing.

[JeffGeorge]

I hear what you're saying. Seriously, the person that submitted that report concept took it straight off the Internet somewhere I believe he told me. And that's why I think I'm making the tactical decision to just trash the whole budget aspect. If someone wants to know why it's not there I will tell them that there is no data. Their current system-generated report doesn't include budgeted amounts any way. I think that calculating the change between one year to the next might be too much for them to absorb. I guarantee you that the guy in charge of the Financial department didn't even look to see if there were budgeted amounts in the G/L when he decided he wanted to revamp the P&L. I also guarantee that he wouldn't know where to look to find out anyway.