Consolidating Large amounts of data

[Corbie]

Hi need help on deciding best way to consolidate a large amount of data.

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.

Suppose 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).

Whats OLAP could this be what I'm looking for

Any help would be gratefully accepted

Corbie

 

[xlbo]

OLAP ain't exactly what you need
Access would certainly be the way to go and it's pretty easy to get data out of Access into Excel
You can use any or all of:
ADO
DAO
MSQuery

The easiest to get started with is MSQuery which is a gui that looks very similar to the Access Query Design panel

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

And you can use Pivot Tables tied to an Access database and also take advantage of the power of Pivot Tables. You can set up complex queries in Access or use QBW in the Pivot Table Wizard to do complex queries (multiple tables, expressions, outer joins)

So it seems you have ALOT of bullets in your gun storing the data in Access and using the rich analysis and reporting tools in Excel.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

...oops

that's QBE

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Corbie]

Thanks guys.

It would seem that access is the way to go.

Could you point me in the way to some descent sites so that I could learn more, or would it be best to post the question in the access forum

Thanks again

Corbie