Combining separate excel report data

[mikebravo]

I have five separate reports generated in Excel with budgetary data (BOY, MY, Actuals, EOYProj, etc.). Each report uses different resource codes associated with the budgetary data (i.e., supplies, supplies_gen, sup, supplies1, 1supplies), but all the same budgetary information. I have established a standard set of resource codes which I want to use to combine all five separate reports into one. What I want to do is find a way to automatically convert the five separate reports to the one central set so that I don’t have to hand-jam each report and its multiple lines of resource codes into one set. I have been trying to find a method in excel without success. Someon mentioned I might try using access queries. Any thoughts? Appreciate any help.

[June7]

What do you mean by 'reports' in relation to Excel? Are these just spreadsheets with calcs? Access can link to spreadsheets and query the linked data just as if it were a table in Access. Access reports can be built from this data.

[mikebravo]

Basically they are reports generated online that were pulled from a data warehouse. Once you have filtered what you want you can export the reports to excel, pdf, or csv. The excel reports that are created from this have a column on the far left that list the many different budgetary resource codes like benefits, printing, rent, etc. Then each remaining column has a heading at the top of the page with each corresponding budget period (initial budget amount, midyear, actuals, and a projected amount). So below each column is a total dollar amount without any calculated values, just the total amount that is pulled from the central data warehouse. Like I said in the last post we have multiple regions that we pull data from to create a combined report. The problem is that there is no quick way to do it because some regions use different resource code names or more than one resource code. I just want to figure out how to combine the different reports and their corresponding dollar amounts into one standardized set of resource codes.

Sorry if that is long and a bit confusing. Can't really figure out another way to explain it. Hopefully that helps. Thanks again.

[June7]

So these 'reports' are just spreadsheets? You want to provide workbook for analysis? Follow instructions at bottom of my post.

[mikebravo]

File is attached in database (zip) form and also my excel documents. thanks again.

[June7]

Is this an example of the variation in resource codes:
Most of the IT prefix codes are the same across regions but Regions 1 & 4 have ITEQUIP and Regions 2 & 3 have IT, Region 5 has neither but one the others don't ITCOESPT. Does IT equate to ITEQUIP? Can you get the regions to standardize budget coding? Would be a pain to have to write code to standardize these codes in your db, doable but a pain.

A UNION query can combine all 5 Access tables into a single dataset. The tables have the same fields in the same order so the UNION sql is simple. There is no designer or wizard for UNION, must type in the SQL View of query designer. There is a limit of 50 lines in a UNION. Data in a UNION query is not editable. VBA has issues with UNION queries. Can use the UNION as source to create a table or reference in other queries or as RecordSource for reports.

SELECT "Region1" As Region, * FROM [Region 1]
UNION SELECT "Region2", * FROM [Region 2]
UNION SELECT "Region3", * FROM [Region 3]
UNION SELECT "Region4", * FROM [Region 4]
UNION SELECT "Region5", * FROM [Region 5];

I suggest you delete the totals row from each table.

How did you create the Access tables? Importing from the Excel using the Import wizard won't work with the multi-row header.

[mikebravo]

If we could get the regions to all use standardized resource codes then we wouldn't have this problem. Unfortunately some of the folks that use our system are in very remote sites and "have been doing it this way forever" and wouldn't be open to or even responsive to any standardization. Therefore it kind of falls on people like me to clean everything up so we can make more accurate projections. I understand there aren't a lot of differences in the different codes, but this is just one example of multiple budgets and also only a selection of budgets from one particular range of dates. If we did an end of the year report it would be far more complicated. I ran the union query and it does combine them all. How can I adapt this particular query so that not only does it combine all of the regions into one table but also combine the five regions to one where A. the resource codes are all the same and standardized (e.g. utilities)and totaled together and B. where the resource codes are unique only to one or two regions and need to be added to another resource code (e.g. visualinfo in region1 and region2 would be folded into itconstsvs).

Thanks again.

[June7]

Maybe not as bad as I thought. You would have to build a table that 'maps' the budget code associations. It would have a field that contains every possible budget code used by the regions and another field with the master code you want them to 'fold' into. Then join this table to the UNION query on the related ResourceCode fields to make the master code available for each record.