Complex report

[JimS]

I need to create a complex detail spending report. It has half a dozen
tabular sources, all linked by a two-field set of keys (Project/EquipmentID).
Each of the spending categories is summed up from 0 to thousands of detail
records. I want to be able to print a report (or an excel workbook) that
includes all the details. It's a bit much to ask... Each category of spending
(represented by a query) could be, what?, a subreport? What would your
strategy be?

Project 1 -- Equipment ID X1

Labor Detail... Parts Detail..... Design Detail.....
Miscellaneous Detail....

xxxxx xxxxxxxx .... YYYY YYYYYY YYY..... ZZZ... <nothing...>
xxxxx xxxxxxxx .... YYYY YYYYYY YYY... <nothing> <nothing....>

etc...

 

[Duane Hookom]

Could you share your table structures? I'm not sure about others but I don't
have a clue what "tabular sources" are.

Why would "Each category of spending" be "represented by a query"?

 

[JimS]

It's a good question...

Labor spending (in its own table, and database...) is key-entered.
Material spending comes from two sources, one key-entered (a small inventory
system), and an excel extract from mainframe-type sources (no SQL access).
Internal labor is key-entered into a mainframe-type app, then excel
downloaded.
Miscellaneous spending is extracted from SAP purchasing (again, excel).
Budgets are key-entered into a small Access app on this db.

To show composite spending, I develop (sub)queries that summarize the data
by category, then a final query that shows all the summaries as columns, and
is exported to excel.

This works fine for the final report and to some extent for management
review. It does not help so much for detailed analysis, both at the
management level, and at the level of data validation.

Since much of the data entry is out of our control, descrepencies are
common, stupid, and result from lack of discipline, training, and automated
controls on systems that are being used in ways not contemplated by the IT
folks who bought or developed them. We ought to be using SAP for this work.
IT is focused on ongoing operations. We are responsible for renovations and
construction projects that cost millions, but can't get in the way of
production.

The issue I'm workin' on here is data validation. So you have a download
from SAP of all the POs written for miscellaneous expenses. I match it to the
application's internal table of such transactions (yes, I know we're
replicating the SAP system... I don't like it, but it's work, and the client
needs it....) Due to allocations in our system that vary (reasonably) from
those in SAP, I need to juxtapose the data. I did this on a form, and it
dramatically improved their productivity. Now, I'd like to do the same thing
on a report.

I've never done a "subreport", though I've done scores of "subforms". I
tried a subreport yesterday, but I struggle a little with it. I'll keep at
it.

From you, I was looking for strategy. How would you plan it out? Can I put
two subreports side-by-side and get decent-looking results, etc? How will
Access compensate for differing report depths, etc.

Thanks for reading my rant. I appreciate your questions!

 

[Duane Hookom]

You can put two subreports side-by-side on a main report but your results
might not look good. This isn't much different than two subforms s-b-s.
Subreports are generally set to allow growing while subforms' height is set
at design time.

You can use values on your main report (fields) to filter the subreports
with the Link Master/Child (much like forms and subforms).

My company is in the process of migrating most of our business to SAP.
Several systems have one foot in the legacy apps and one foot in SAP. I feel
your pain. My general strategy is to pull information from multiple
applications into a single database system and report from there. I recommend
using SQL Server as a backend storage. The DTS and SSIS (more recent) provide
super ETL tools for connecting to multiple data sources to extract and
massage data http://msdn2.microsoft.com/en-us/library/ms141026.aspx.

 

[JimS]

I have recommended that this project be moved to a "grown-up" database like
SQL Server or the like. Since I'm a contractor working outside the IT group's
purview, I don't have access to their servers, and can't install anything but
Access. The main data sources are SAP and Maximo. Both are powerful and rich,
but as in most installations, have been installed with poor discipline with
little regard for accountability. I keep pleading that I'm replicating
functionality they paid millions for, but they just shake their heads and
write me a new PO.

Thanks for the information. I'll read it and learn.

 

[Duane Hookom]

We are in the process (maintenance managers in training room as I write) of
migrating from Maximo to SAP's AMM. We could easily connect to Maximo tables
with Access to create custom reports. We won't be allowed to connect directly
to SAP :-(

As a contractor, you are experiencing job security. A fellow Access MVP
recently commented "Sometimes it's easier to invent something stupid, than
convince customer,
that his idea is bad."

BTW: I hope you aren't contracting to the world's largest food company?

 

[JimS]

Nah, it's a semiconductor company. Billions in capital, millions in
installation costs, struggling to use history to forecast the future.