Re: Excel and large numbers of linked files

[SIANNON -at- VISUS -dot- JNJ -dot- com]

Meg Ehr states:
"The boss wants me to set up the structure for a series of files that will
contain specific customer account data. She wants individual files for 800
accounts (14+ data fields for each), plus a "snapshot" file showing 
selected data from each of the account files. The "snapshot" file should 
update automatically when data in the account files is changed, and will 
be
attached to a Siebel database for viewing."

Part of the question of whether Excel is the appropriate tool for this 
depends on what the function of the doc will be.  Please let me know if I 
rephrase this correctly:  The boss wants records maintained for 800 
accounts such that individual users can access/update the records for only 
those accounts they need to access, and yet management (or whoever) can 
view selected current data (or a summary thereof) for all the accounts.

Sounds like a single database system would be the least headache for 
maintenance, as well as having the benefits of scalability (an Access 
database app. can be converted into a more robust client/server dB, say 
using an Oracle back end and an Access front end, relatively easily and 
without loss of data).

Here's how I'd think of doing it:
-- Set up an Access database with a table for the customer data, each
   account having its own record. (I think some managers define "file"
   as a discrete collection of data on a single subject--records in a 
   database can do the same thing as long as a unique identifier (i.e. 
   primary key) is used to differentiate the records).
-- Create a form to allow users to edit the data in a single record.  
   This form would draw data through a simple query that would ask the 
   user which account they wished to edit.  
   --- If additional security is needed here, Access has ways of 
       prompting for passwords, using VB code modules (make sure 
       you have the extended help files, which are not always 
       included in the default installation, or a reference manual 
       and you can figure out how to do this reasonably quickly).
-- Create a report for the summary/overview page (it will probably 
   run more quickly than the excel equivalent would take to update 
   all its links).
-- Create a form to serve as a switchboard when a user accesses the 
   Access dB from the network.  The switchboard would have a button 
   to bring up the data entry form, and another to bring up the 
   summary/overview report.  If additional reports are desired later, 
   these can be added. Set the form to come up automatically on 
   startup of the dB.

Benefits of this approach:
-- faster time to generate/access the summary (Access has easy
   queries for aggregate reporting)
-- less risk of an end user screwing up the numbers by moving the 
   wrong cell, leaving something blank or changing a cell format 
   in one of the feeder files (fields may be set as 'required' in 
   the Access data entry form), or moving/deleting a feeder file 
   (which would screw up the path and prevent the report from seeing 
   the file's data)
-- Single point of maintenance for the whole thing, instead of 
   a dependency on OLE functions and consistent network connectivity
   (if 10 if the users of the excel equivalent were on a part of the
   LAN that lost connectivity for a day, what would that do to the 
   summary/overview report?)
-- If an export to Siebel is still required, it is easier to export 
   records from a single Access database (you can set up a VB module 
   or a macro for it), than to collect data from 800 individual files 
   across a network and then transfer it.

Detriments (?) to this approach:
-- If you don't already have an advanced familiarity with Access, or 
   don't have someone in the office who already has it, you'll need 
   to learn it (I was self-taught for this level of usage, designing
   databases more complex and convoluted than this, so I know it can 
   be done reasonably quickly).
-- If the users are used to using Excel files, there will be some 
   adjustment and training involved.
-- You'll have to overcome the boss's current preference, i.e., pitch
   the idea as a cost/benefit analysis so that the boss can see the 
   consequences of each approach (with some bosses, this is easy, with
   others it is not). 


Hope this is helpful!
Shauna Iannone
---------------------
Ever have a day when...fish?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Be a published author!  iUniverse gives you: a high-quality paperback, a
custom cover design, and distribution to 25,000 retailers.  And it's
affordable.  Join our almost 10,000 published authors today.
http://www.iuniverse.com/media/techwr

Sponsored by eHelp Corporation, makers of RoboHelp - the industry standard
in Help authoring. Download a trial version today or get special savings when 
you buy the RoboHelp 2002 Holiday Edition. Visit http://www.ehelp.com/techwr

---
You are currently subscribed to techwr-l as: archive -at- raycomm -dot- com
To unsubscribe send a blank email to leave-techwr-l-obscured -at- lists -dot- raycomm -dot- com
Send administrative questions to ejray -at- raycomm -dot- com -dot-  Visit 
http://www.raycomm.com/techwhirl/ for more resources and info.