Creating Report From Multiple Sources in Access 2010

[rpeare]

So what's the upshot then? what do you want your output to be if you're not comparing to the budget, just a variance from the previous year but with the same subtotaling scheme?

[JeffGeorge]

Correct. I've decided that I'm not going to worry about the budget numbers if they aren't even aware that they have no data for that.

I've attached an image of a rough idea of how I'm trying to output the data. It only includes a few of the values at this point and only one column without any column heading. I was trying to create a test report using the query format you provided in your sample database, but I couldn't figure out how to select a specific part of the sum field, e.g. just the Gross Sales portion of the CMActual field. I'm starting to realize how completely inadequate my Access skills are.

[rpeare]

That's what I was talking about in setting up groups

So let's say you have the following GL codes:


1000.1
1000.2
1000.3
1001.1
1001.2
1001.3
1002.1
1002.2
1003.1
1004.1
1004.2
1004.3

where 1000.x is going to go to gross sales
1001.x is going to go to outbound freight
1002.x is going to go to sales allowance
1003.x is going to material COGs and so on

in your GL setup table you'd want something like:

GLAccount	ReportHeader	ReportGroup
1000.1	Gross Sales	1
1000.2	Gross Sales	1
1000.3	Gross Sales	1
1001.1	Outbound Freight	1
1001.2	Outbound Freight	1
1001.3	Outbound Freight	1
1002.1	Sales Allowance	1
1002.2	Sales Allowance	1
1002.3	Sales Allowance	1
1003.1	Material COGS	2
1003.2	Material COGS	2
1003.3	Material COGS	2

Then you'd create a group footer on the REPORTGROUP field and perform your group sums there

[JeffGeorge]

Isn't the field that I've named accountType, which corresponds to your ReportHeader, enough to serve that purpose? Or do I need a second identifying field? Using the image I posted above, are you saying that those values and labels would appear in the group footer instead of the detail of the report?

[rpeare]

I was working with the data you originally showed in your example and ignoring the 'group' you put into the text file because you don't really need it as long as you have some method of breaking your individual GL account codes into which group and subgroups you want to see on your report.

In your case you want to see a subtotal for gross sales (assuming it covers more than one GL code) but you also want to see a subtotal for gross sales, outbound freight and sales allowance subtotaled as NET INCOME. You've got at least 2 levels of subtotals in the report you're looking to create. Does that make more sense?

[JeffGeorge]

It makes sense, but I'm just not sure how to do it. Every value, other than a sub-total, that appears on the report shown is calculated from multiple G/L accounts. Using Gross Sales as an example, are you saying that I should put that on the report as a group footer? If I understand correctly, I would probably never actually be showing the referenced field values on the report, as every value used requires multiple records, and only the total of those qualifying accounts is needed.

[rpeare]

That's right, for instance in the text file you uploaded you have these lines (just taking cuts of data)


"4030.02","2013",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales"
"4030.04","2012",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales"
"4030.04","2013",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales"
"4100.00","2013",7202.57,73444.87,152309.81,113280 .01,65376.88,4769.45,0.00,0.00,0.00,0.00,0.00,0.00 ,0.00,"A","Outbound Freight"
"4100.01","2013",0.00,0.00,0.00,5366.00,0.00,0.00, 0.00,0.00,0.00,0.00,0.00,0.00,0.00,"A","Outbound Freight"
"4100.02","2013",0.00,11793.26,15779.69,11477.31,1 6151.48,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,"A ","Outbound Freight"


you would not have a DETAIL section of your report you would only have a GROUP FOOTER GL Group
you would also have a footer for the NET INCOME which would include BOTH gross sales AND outbound freight

The same would go for a NET INCOME subfooter, so for the purposes of this example you'd have something like:

"4030.02","2013",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales","Net Income"
"4030.04","2012",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales","Net Income"
"4030.04","2013",0.00,0.00,0.00,0.00,0.00,0.00,0.0 0,0.00,0.00,0.00,0.00,0.00,0.00,"A","Gross Sales","Net Income"
"4100.00","2013",7202.57,73444.87,152309.81,113280 .01,65376.88,4769.45,0.00,0.00,0.00,0.00,0.00,0.00 ,0.00,"A","Outbound Freight","Net Income"
"4100.01","2013",0.00,0.00,0.00,5366.00,0.00,0.00, 0.00,0.00,0.00,0.00,0.00,0.00,0.00,"A","Outbound Freight","Net Income"
"4100.02","2013",0.00,11793.26,15779.69,11477.31,1 6151.48,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,"A ","Outbound Freight","Net Income"

IF you were running this report from an imported text file.

What I'm saying is that you do not run it from a text file you create a table that lets you group and subgroup data however you want.

[JeffGeorge]

Let me see if I can wrap my brain around this. I have the table called tblP&L which stores the source data, including an accountType field that designate which portion of the report that account is used for. It looks like I should be building the report from this table instead of the summary query based on this table. So what I'd want to do is position a field on the report that is [PERIOD_1]...[period_whatever] where [accountType]="Gross Sales", make this field non-visible and then show only the footer of this group of records. And then do likewise for [accountType]="Outbound Freight", etc. Is this correct? Of course, now I'm back at the spot where I still need user input for year and month before I can do any of this, so maybe my report source has to be the query instead of the table. The query expression you supplied has given me the values I need for the report, but I just can't figure out how to get them on to the report. This is what my half-built query is producing using January 2013 as the source. The problem is that I don't know how to isolate one of the groupings for one of the fields as a report object.

accountType	CYCMActualTotal	CYCMBudgetTotal	PYCMActualTotal	CYYTDActualTotal	CYYTDBudgetTotal	PYYTDActualTotal
	667237.309999999	0	676431.25	667237.309999999	0	676431.25
Advertising & Sales Promotion	4931.92	0	5159.37	4931.92	0	5159.37
Depreciation	61373.82	0	85778.09	61373.82	0	85778.09
Direct Labor	223504.53	0	265131.55	223504.53	0	265131.55
Dues & Subscriptions	5378.73	0	5895.38	5378.73	0	5895.38
Gross Sales	-5648837.6	0	-7019384.61	-5648837.6	0	-7019384.61
Leases	23597.33	0	20832.26	23597.33	0	20832.26
Licenses & Fees	374.69	0	3074.41	374.69	0	3074.41
Maint. & Repairs	60168.04	0	43289.41	60168.04	0	43289.41
Material COS	4267083.6	0	5574293.73	4267083.6	0	5574293.73
Mfg. O/H Benefits	95220.59	0	52709.08	95220.59	0	52709.08
O/S Services	14323.45	0	22233.86	14323.45	0	22233.86
Office Supplies	8820.63	0	6881.04	8820.63	0	6881.04
Outbound Freight	7202.57	0	0	7202.57	0	0
S&GA Benefits	35051.97	0	61524.6	35051.97	0	61524.6
Sales Allowances	-97.81	0	-216.89	-97.81	0	-216.89
Supplies	68928.05	0	94877.93	68928.05	0	94877.93
Travel	1695.44	0	5024.74	1695.44	0	5024.74
Utilities	19769.07	0	33030.36	19769.07	0	33030.36
Wages	84273.67	0	63434.4400000001	84273.67	0	63434.4400000001

[rpeare]

So you're changing the premise of the problem. Your original problem revolved around GL codes and getting them into the correct 'buckets' on your report which is the problem I thought you still had. In your most recent data example you already have things grouped into their account type but you do not have them grouped in the same order/meta grouping. In your most recent example GROSS SALES, OUTBOUND FREIGHT and SALES ALLOWANCES are not on consecutive rows so tell me. Are we working on the original problem where you had a GL code, a year and dollars by month split into periods or are we working on trying to turn the query that you've shown in the previous post into a report? If we are working on the query I would like to know how you got the account types in there, are you physically coding in your query something like

iif(GLAccount = "1000.1", "Advertising & Sales", iif(GLAccount = "2000.1", Depreciation, etc...))

[JeffGeorge]

I think it's safe to say that the target has shifted a little. My ultimate is to get the data I need into a report that can be used by a person sitting at a who can just click a shortcut and then input a month and year. The first part of the problem was extracting the proper data once I realized I couldn't embed SQL statements into a field on a report. Your sample table and query showed me how to write the expression needed to fill the buckets based on the user input. What I've been doing since then is flagging the various accounts with their type. To do that I've been using statements like

UPDATE [tblP&L] SET [tblP&L].accountType = "Misc"
WHERE ((([tblP&L].GLACCOUNT)>="7143.00" And ([tblP&L].GLACCOUNT)<="7145.04")) OR ((([tblP&L].GLACCOUNT)>="7355.00" And ([tblP&L].GLACCOUNT)<="7355.04"));

I apologize if I wasn't clear where I was going with this. Unfortunately, once I get an idea in my head I tend to think that I've communicated it with those around me and only later realize I was the only person I spoke to. It's just so frustrating to me because this is stuff I should know how to do and I feel like I'm forgetting everything I once knew. Maybe I'm just getting old....

[rpeare]

ok this is exactly my point

what you're doing is creating a 'temp' table which you do NOT need to do.

Your original table is fine, what you need is a table that has the GLACCOUNT, the ACCOUNT TYPE it goes into (Gross Sales, etc) the sales META group (gross sales, outbound freight, sales allowances all go into the META group of NET INCOME). You'll also want a sort so that you can put them out in the same order every time. What you have done is hard coded GL accounts to categories so that if there is EVER any change to your gl codes you will have to search your query for the GL account and make changes appropriately or, likewise if a new GL Account is ADDED you would have to modify your code as well, this is a huge waste of time when you can simply create a local table that tells you which GL code goes to which group/meta group.

So let's go back to your original set of data (the text file that you posted several posts ago)

tell me which GL codes go into which ACCOUNT TYPES and I can work the example with the data you posted. if you can create a text file that has the information that's even better

[JeffGeorge]

The original table on the SQL database does not have the information I need for grouping purposes. I also cannot make changes to its structure which is why I created my own table (tblP&L) from it. For all intents and purposes, this is my source table. However, this means that I will have to refresh this table monthly so that the Access tools will still be accurate. The only information that the SQL table has that tells me which bucket an account belongs to is the account number. If anything beyond that is needed to create the report output I am looking for then I must have an intermediary table. Any changes to the source SQL database table is going to mess up my query/report. The source table has no field that will tell me if it should be part of Gross Sales, Benefit, etc. other than the account number.

I can tell you exactly which G/L accounts I'm assigning to the various report fields. Is there some sort of special way you would like this presented? For example:

accountType "Supplies" = G/L accounts 6320.00 through 6330.04 AND 6535.00 through 6540.04

There are a total of 26 line items on the report. I could give you a file containing a similar reckoning for each line item or in any other form you would like to see it.

Forgive me if I misunderstand what you are saying about using a temp table. I'm thinking of a 'temp' table as something that would be generated at run time, whereas my tblP&L is more of a source table and would only require periodic updates from the SQL database.

[rpeare]

Let me try one more time here

you are going to CREATE the groups with a local (ACCESS ONLY) table. which you are going to link to your SQL table, the query you create by connecting these two tables will then drive the report.

The easiest thing for me would be a list like this:

6320.00, Supplies
6320.01, Supplies
6320.02, Supplies
etc

basically a line for each possible GL code, if you can't provide that then as close to that as you can get.

[JeffGeorge]

This file has the list of accounts and the corresponding account type, comma-separated.

[rpeare]

I'm enclosing a list of GL codes that is was in your file tblP&L.txt that are not included in the list of GL codes in the file temp.txt. I'm going to assume that anything that does NOT have a matching gl code in your temp.txt file should not be on the report. If some (or all) of the GL codes *should* be on the report please update the list with the correct category and I'll work them in if I'm still working on the example.

Query2.txt