Access and Excel

[JudithJubilee]

Hello there,

My friends company are looking for ways to speed up so
she asked me.....

They have lots of sales and target data in fairly large
Excel spreadsheets. With formulas, charts, etc. They have
a number of sites that each month send them their figures
for the month either via email, fax or spreadsheet. My
friend is then copying all this info into her
spreadsheet.....please bare with me..........

She then has to do a Word document with information about
each month, which she is at the moment typing in again.

They also want to stop the various branchs from updating
the data afterwards and not allow the branches to see
each other.

What could they do? I've said I would help.

I immediately thought of Access with forms, security,
etc. Her boss has now said that he will only allow her to
have Access, the other branches will only have Excel.

Has anyone got any tips for having the branches always
use Excel and for that to update a database, which can
then be queried for mail merges?? I have to say I'm still
wanting to create some sort of Access database for her as
she is reentering 3 times at the moment.

Your help is much appreciated

Judith

 

[Nikos Yannacopoulos]

Judith,

It is certainly doable, but be warned: there's more to it than meets the
eye! Apart from a robust data structure design, it will take a simple,
foolproof, intuitive user interface for input / output. The downside
here is, the simpler and esier it is for the user, the more complex it
is for the developer. My point is, even if you know your stuff inside
out, it will still take quite some time to put it together, it is not
something you can expect to set up overnight... which is why some
people/cpmanies make a living on such projects. So, are you prepared to
do this for free? Assuming that you are, here are some thoughts (for
what they are worth):

Updating:
* Sites need to send in their data in standardized spreadsheets, with
either a constant name, e.g. Site1.xls, or a name with a constant and a
changing part like Site1-Jan05.xls.
* Spreadsheets are saved in a particular folder, and linked into the
Access database. If the ssheet name is constant each month the file just
overwrites the previous one (you lose history, at least in that folder),
or if there is a time indication in the filename, some simple VBA code
can be employed to change the link each month.
* Data from the linked ssheets is imported in native Access table(s)
through append queries, with a month indication added. It's a good idea
to run a delete query for the specific ste and month prior to each
import, so you cope with revisions. All automated in VBA code.

Reporting:
* Table with siteID, site name, mail address per site;
* Universal report for all sites, based on query including siteID field.
Site filtering on criterion read off a form.
* VBA process to open above table as a recordset, browse through the
records, and for each record (a) put the siteID in the control in the
above form for the report to be filtered on, and send the report to the
mail address in the record.

Surely there may be a number of different approaches; this is how I
envisage it at first glance. If you decide to go ahead with it, post any
specific questions you may have. In any case, make sure you spend enough
time on the data structure; a robust design is only half the job, but a
bad one is a recipe for disaster, and the later down the development
process it is found out the worse!

HTH,
Nikos

 

[Steve Schapel]

Judith,

It seems likely that a properly designed and constructed Access
application would help your friend's work to be more efficient. As long
as the data in the Excel files is in a suitable structure, it will be
possible to import this data into the database, and for this process to
be reasonably automated. Possibly this would involve the use of the
TransferSpreadsheet method, followed by some data manipulation in Access
via append queries and update queries. And similarly, she will probably
be able to export the required data from her Access database to a
suitable format in Word, or PDF, or some such.