W
what were they thinking
My new boss has just doubled my work load. She has me using excel for many
PLEASE HELP!!! I'm getting more gray by the day.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Vinson
My new boss has just doubled my work load. She has me using excel for many
PLEASE HELP!!! I'm getting more gray by the day.
You'll have to negotiate with your boss on this; and you might want to
ask in a newsgroup which deals with Excel, if you're programming in
Excel. The "Access" in this newsgroup's name refers to a different
program, Microsoft Access.
It *does* sound like Excel is perhaps not the ideal program for this
need, and that a good Access database could handle it much better...
but it sounds like a real case of "can't take the time to save time"
problem, in that it would take weeks of work and/or hiring a
professional Access developer to completely redo all these reports and
data storage in Access.
John W. Vinson[MVP]
T
Tom Ellison
I suppose John is of the old school way of seeing things. "There's no
substitute for doing a job right the first time."
My sympathies for the OP. The more time and effort that goes into doing the
job poorly, the more there is invested in doing it poorly, so the more
unpalatable any good solution will appear. It's hard to spit out of a deep
hole. So, the solution is, "everybody keep on digging."
Tom Ellison
substitute for doing a job right the first time."
My sympathies for the OP. The more time and effort that goes into doing the
job poorly, the more there is invested in doing it poorly, so the more
unpalatable any good solution will appear. It's hard to spit out of a deep
hole. So, the solution is, "everybody keep on digging."
Tom Ellison
V
Vincent Johns
This sounds unbearably gloomy. I agree that things look tough, but
maybe there's a way to begin using Access along with Excel without
having to produce a complete Access replacement for the Excel workbook.
For example, some of the work in Excel can maybe be reduced by forcibly
restricting the day-to-day (or minute-to-minute?) changes that people
are making to the worksheets by protecting the parts that should not be
changed. I'm guessing that it's possible to cut the data-entry parts of
the Excel worksheets down to a manageable number of cells. Any one
datum should be entered only once (maybe you're doing it that way
already, but it can be enforced) and other references to it can be done
via formulas.
The data in any one List in Excel can be imported (or linked) into an
Access Table. If the information is convoluted (= reminiscent of
spaghetti), you might need to set up a separate worksheet in Excel, with
an orderly array of references, that Access could import. Rows in a
list or worksheet correspond to records in the Access Table; columns
correspond to fields in the record.
I think I would link the Access Table instead of importing it, as the
data that Access sees would then automatically stay current -- no extra
effort would be needed to import them. Given a Table suitably linked to
an Excel worksheet or named range, you could define Queries and Reports
based on it, and after a reasonable amount of experience verifying that
a given Report in Access contains the same (or better) quality of
information as a current printable Excel Worksheet, you could quietly
abandon that old Excel Worksheet. Use the Auditing tool in Excel to be
sure you know what all the data pathways in your Workbook are.
After you have thus replaced a lot of the Excel data-display
functionality, your Excel file will probably have become smaller and
easier to maintain. It's possible that you may never want to abandon
the Excel file completely, as your data-entry people are already
familiar with it. Also, for some types of data entry, I find Excel
easier to use than Access Forms, since AutoCorrect and AutoFilter are
easy to set up and use, and there are many Excel keyboard commands that
make navigation easy. (It's possible to implement that, or something
pretty similar, in an Access Form, but it would take work, and with
Excel it's the default behavior.)
Basically, I'm trying to suggest that you can begin right now, with only
a small investment of time & energy, to use Access to handle some of the
work, and to add functionality to Access little by little. If you run
into problems with that, you can probably get help with the Access part
on this newsgroup. Good luck.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
maybe there's a way to begin using Access along with Excel without
having to produce a complete Access replacement for the Excel workbook.
For example, some of the work in Excel can maybe be reduced by forcibly
restricting the day-to-day (or minute-to-minute?) changes that people
are making to the worksheets by protecting the parts that should not be
changed. I'm guessing that it's possible to cut the data-entry parts of
the Excel worksheets down to a manageable number of cells. Any one
datum should be entered only once (maybe you're doing it that way
already, but it can be enforced) and other references to it can be done
via formulas.
The data in any one List in Excel can be imported (or linked) into an
Access Table. If the information is convoluted (= reminiscent of
spaghetti), you might need to set up a separate worksheet in Excel, with
an orderly array of references, that Access could import. Rows in a
list or worksheet correspond to records in the Access Table; columns
correspond to fields in the record.
I think I would link the Access Table instead of importing it, as the
data that Access sees would then automatically stay current -- no extra
effort would be needed to import them. Given a Table suitably linked to
an Excel worksheet or named range, you could define Queries and Reports
based on it, and after a reasonable amount of experience verifying that
a given Report in Access contains the same (or better) quality of
information as a current printable Excel Worksheet, you could quietly
abandon that old Excel Worksheet. Use the Auditing tool in Excel to be
sure you know what all the data pathways in your Workbook are.
After you have thus replaced a lot of the Excel data-display
functionality, your Excel file will probably have become smaller and
easier to maintain. It's possible that you may never want to abandon
the Excel file completely, as your data-entry people are already
familiar with it. Also, for some types of data entry, I find Excel
easier to use than Access Forms, since AutoCorrect and AutoFilter are
easy to set up and use, and there are many Excel keyboard commands that
make navigation easy. (It's possible to implement that, or something
pretty similar, in an Access Form, but it would take work, and with
Excel it's the default behavior.)
Basically, I'm trying to suggest that you can begin right now, with only
a small investment of time & energy, to use Access to handle some of the
work, and to add functionality to Access little by little. If you run
into problems with that, you can probably get help with the Access part
on this newsgroup. Good luck.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
T
Tom Ellison
Actually, my first databases were all written for Excel. If you follow
normalization rules and build things properly, the disadvantage of Excel is
mostly one of scale. If the Excel application is built as many people do
it, so it doesn't look or function as a good database would do, it's a mess
whether it's Excel or Access. Up to a certain scale, an Excel application
can be quite excellent.
The problem would not be Excel as much as it is design. The OP made me
think this is where the problem really is. I certainly don't expect an
Access "overlay", or anything else built on a bad foundation is probably
worth the effort to try to improve it. It will almost certainly need
replaced.
You're right, I'm gloomy!
Tom Ellison
normalization rules and build things properly, the disadvantage of Excel is
mostly one of scale. If the Excel application is built as many people do
it, so it doesn't look or function as a good database would do, it's a mess
whether it's Excel or Access. Up to a certain scale, an Excel application
can be quite excellent.
The problem would not be Excel as much as it is design. The OP made me
think this is where the problem really is. I certainly don't expect an
Access "overlay", or anything else built on a bad foundation is probably
worth the effort to try to improve it. It will almost certainly need
replaced.
You're right, I'm gloomy!
Tom Ellison
V
Vincent Johns
Tom said:Actually, my first databases were all written for Excel. If you follow
normalization rules and build things properly, the disadvantage of Excel is
mostly one of scale. If the Excel application is built as many people do
it, so it doesn't look or function as a good database would do, it's a mess
whether it's Excel or Access. Up to a certain scale, an Excel application
can be quite excellent.
The problem would not be Excel as much as it is design. The OP made me
think this is where the problem really is. I certainly don't expect an
Access "overlay", or anything else built on a bad foundation is probably
worth the effort to try to improve it. It will almost certainly need
replaced.
And my impression from what the OP said was that the current design is
kind of faulty. But not totally, as it appears to be working well
enough that people are getting some value from it.
You're right, I'm gloomy!
Tom Ellison
And you may be right, but something I really like about Access is that
it's often possible to improve a poor design without tossing out the
entire current system. I've worked with some tools that were not nearly
so forgiving. In the OP's system, I have the impression that there is
no current Access program to be in bad shape (yet), so with some good
design decisions there, it may be possible to rescue the Excel system
little by little.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
T
Tom Ellison
The idea that you can sometimes save part of a badly designed system sounds
great. It doesn't match my experience.
If table designs are faulty and must be changed, you're going to pretty much
lose all the forms and reports based on them. Of course, whether it's worth
trying to save parts of them may depend on just how fast your are at
building a new application. A typical form or report for us is about 2
hours work. It's unlikely we could fix a bad one nearly that quickly. This
ignores design time. How long does it take to design a form properly,
compared to how long it takes to design a fix to a bad form? That's going
to be very hard to say. So, the discussion is really quite complex.
I won't say I disagree with you. There could be circumstances where this
works out well. Indeed, a poorly designed system that works even partially
is very revealing in terms of learning what should be done.
Tom Ellison
great. It doesn't match my experience.
If table designs are faulty and must be changed, you're going to pretty much
lose all the forms and reports based on them. Of course, whether it's worth
trying to save parts of them may depend on just how fast your are at
building a new application. A typical form or report for us is about 2
hours work. It's unlikely we could fix a bad one nearly that quickly. This
ignores design time. How long does it take to design a form properly,
compared to how long it takes to design a fix to a bad form? That's going
to be very hard to say. So, the discussion is really quite complex.
I won't say I disagree with you. There could be circumstances where this
works out well. Indeed, a poorly designed system that works even partially
is very revealing in terms of learning what should be done.
Tom Ellison
V
Vincent Johns
Tom said:The idea that you can sometimes save part of a badly designed system sounds
great. It doesn't match my experience.
If table designs are faulty and must be changed, you're going to pretty much
lose all the forms and reports based on them. Of course, whether it's worth
trying to save parts of them may depend on just how fast your are at
building a new application. A typical form or report for us is about 2
hours work. It's unlikely we could fix a bad one nearly that quickly. This
ignores design time. How long does it take to design a form properly,
compared to how long it takes to design a fix to a bad form? That's going
to be very hard to say. So, the discussion is really quite complex.
Excellent point -- I usually do the Forms and Reports after ensuring
that I have Queries that do everything I need, though sometimes I'm
handed Forms or Reports that I should try to use with minimal changes.
I guess what I meant was that I rarely have to throw away even poorly
designed Tables. The data in them are usually good, and I can add (via
Update Queries) or delete fields that don't help. I usually tag my new
Queries with an identifying prefix to the name. Access 2003 (and
following) makes it easy to track most references, so not a lot of work
is involved in doing this refactoring. But I agree with you, often a
poor Form or Report is best to use just as a model of what the customer
wanted to see, and it's faster (given good Queries) to knock out a new
Report that imitates the old one.
I won't say I disagree with you. There could be circumstances where this
works out well. Indeed, a poorly designed system that works even partially
is very revealing in terms of learning what should be done.
Tom Ellison
This is reminiscent of the old dictum (from the ancient days before good
software-engineering tools) that, to develop a good system, one should
develop it to about 90% done, then toss everything but the original
specifications and start over. Some of the best computer applications
came from organizations that had suffered a fire or other disaster.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
T
Tom Ellison
Dear Vincent:
Got a chuckle from the story about the "fire" burning up everything and
starting over being a good design tool. I've seen some designs that would
profit from this approach.
It is certainly true enough that having user entered data can usually be
salvaged, and often must be. My points were meant to apply only to the
program design and application. Those go South pretty rapidly when they
aren't done well.
Tom Ellison
Got a chuckle from the story about the "fire" burning up everything and
starting over being a good design tool. I've seen some designs that would
profit from this approach.
It is certainly true enough that having user entered data can usually be
salvaged, and often must be. My points were meant to apply only to the
program design and application. Those go South pretty rapidly when they
aren't done well.
Tom Ellison
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.