Re: Excel and large numbers of linked files

Subject: Re: Excel and large numbers of linked files
From: SIANNON -at- VISUS -dot- JNJ -dot- com
To: "TECHWR-L" <techwr-l -at- lists -dot- raycomm -dot- com>
Date: Fri, 28 Dec 2001 7:55:9

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.



Previous by Author: RE: "baseline"
Next by Author: Re: Why use screen shots at all? [WAS Survey: "Screen shots"]
Previous by Thread: Re: Excel and large numbers of linked files
Next by Thread: Re: Re: Excel and large numbers of linked files


What this post helpful? Share it with friends and colleagues:


Sponsored Ads