Consolidating Large Amounts of Data

[Corbie]

My company has 200 retail outlets that each prepare their own management accounts, They each send a download to head office consisting of a column of 500 numbers which is then consolidated into huge spreadsheet and then 100 or so reports are generated from this. All this is done in 123 release 5.

The problem is that it is very combersome and inflexible, adding or deleting outlets takes ages and is highly error prone.

I could move it all to excel, which would help a bit, But what I really need is some way to have a data engine and then generate the reports off that.

I could use access for it all, but I'm not sure I could get the flexibility in reporting I need (as with spreadsheets). Could I use access to store the data and excel to report on it. If so, can somebody point me in the direction of some help in this (a descent site or book - I've quite alot of experience in Excel and VBA (in excel), but am not so good at access - but am willing to learn - So would need to go to a site that would spooon feed me quite abit)


Any help would be gratefully accepted

Corbie

 

[cheerio]

It is very easy to generate pivot table reports in Excel from an Access database.

In addition to your main data table you may find it helpful to create tables that hold structural information. For example, if your 250 branches are split over 5 regions you can have a table with a branch code in one column and a region code in another. You then create a query that joins the data table to the structure table and use that to drive the Excel pivot table. That way Excel will allow you to choose to see data for a particular region.

In the same way if the rows of your data can be grouped into blocks such as sales, direct costs, operating costs, payroll costs etc you can have a table that links the codes for the lines to the blocks so that people can view the data in detail or at a summary level.

A database solution coupled with Excel pivot tables can meet a very wide range of reporting needs.

Ken

 

[JohnLowell]

Access reports are pretty flexible - I'm guessing you could get what you want out of them. Importing your data to Access should definitely be easier than using a spreadsheet.

If you really want to do the reports in Excel, you could export the data in Excel format, then run your reports. It wouldn't be too hard to use VBA to semi-automate it.

 

[MichaelRed]

General thoughts:

[tab]Any use of multiple applications IS going to be slower than a single app which consolidates the functionality. This is regardless of the details, such as "office automation", interoperability, manual functions, of progrmming (which is just another 'name for the preceeding).

[tab]Programming in multiple languages or dialects will be more of a challengs than maintaing the a single language version. The 'similarity' of dialects of VBA helps t some degree, but also offers MANY opportuniies for abuse / misuse.

[tab]One glaring fault w/ Ms/ A. is the inabillity to 'publish' ANY graphics. So reporting in any form other than "PAPER" becomes an exercise in frustration. As long as ye olde paper mill is acceptaable, Ms. A. does nice reports, other wise, something else is absoloutly necessary.

From my experience, Power Point is a better report generator than Excel, for these exercises -but it generally requires move investment (in learning) than Excel.

I am only a LOT paranoid about actually publishing reports from a db which I am considered responsible for. On more than an occassional occassion, "Reports" generated through either PP or XLS have 'appeared' at some destinations with 'faulty' (read ALTERED) data. I HAVE resorted to including check sums and other "signature" devices to demonstrate that changes have occured, but even the showing that the data has changed does not remove the 'blame game', it only makes it more detailed (?devious?).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places