A far better tool than Access, Excel, pencil & paper, stylus and clay
tablet and a precursor to all of them is the human brain! What I see
in the Original Post disposes me to believe that Access may be a
better solution paradigm than Excel. It is certainly worthy of
investigation and analysis: Just a few clues: there are different
entity types, there are many to many relationships, the data
population is expected to grow....
By the way, I like Excel. Some of my applications with which I'm most
pleased were done in Excel. While they all had data in them they were
really about importing data and calculating and producing new
workbooks, worksheets and charts. As a data management tool, Excel is
an also ran. For very simple data sets and a small data population
with extremely simple or no reporting requirements it's an easy, low
cost way to go.
Staying with a current paradigm on the sole justification that doing
so is *possible* is poor use of that foremost tool. As a race, we've
done a whole bunch more than just come down from the trees. When the
cost of doing business as you now do it exceeds the cost of
transforming to a new paradigm with greater efficiency and utility
then it's time to make a change. Have you noticed that production
(all those jobs) will go where the production cost is lowest for that
particular job? [Warren Buffet is now shifting the focus of his
investing toward the Orient].
The trick is in knowing all of those costs accurately. Determine what
is or should be the time for ROI within your organization. It will be
a rule of thumb rather than an absolute but the bean counters should
know it.
Most organizations are sloppy about monitoring how efficient they are
in their current operations so the metrics are blown at the start.
Try this: Every time you know you're going to do something using your
Excel (or other spreadsheet "database") application, take note of what
you're about to do and then note the time right now. Do the thing.
Record the time you finished with the task. Do that for a week. For
greater accuracy, do it for a longer period of time. Eventually
you'll be able to characterize the time to do something using that
tool. Also, you mentioned that your application will be getting more
use. That means that your monitoring should continue over time.
Maybe pick one day each week to monitor to track the increasing
utilizatization.
Next, determine the Loaded Labor Cost per hour for your organization.
This can be done even for government organizations. Gather the
information to determine all money or goods that are expended per
annum. That means the formal budget, resources consumed from other
organizations but not billed back (the value of real property used
such as buildings, furniture and equipment, parking lots ...) etc.
Divide that huge number by another one: the number of employees times
their paid hours per year.
With the above as the hourly rate, multiply the total hours per day
your current application is used by that rate times the number of
business days per year. That's your annual cost of using that
application to produce the results you get from it. What ever other
things you might have in the fire, doing the above is a very
worthwhile thing to do even if you only track the times used. If your
organization is doing things right then the bean counters can give you
an exact number for the current Loaded Labor Rate. If not, one of
them may even have the super-total cost of operations tucked away
somewhere. They'll either be pleased or shocked that you asked.
Next is to create some informal specifications for the replacement
application. While Access is a natural, I wouldn't even specify
Access unless you want to get your hand in and take over the
maintenance of an eventual Access application. Decide and define all
of the problems you'll solve and outputs required. Describe the
necessary workflows. Describe the necessary Forms and Reports. For
each critical workflow that you have measured in your current
application state a performance threshold. In most cases the
performance thresholds should be significantly higher. Also,
typographical errors should be significantly lower and accidental
damage to data due to mucking in the tables will be a thing of the
past.
Get a consultant to evaluate your prospective project and to work up
an estimate to complete it.
Now you have some things that you can compare. Some of the figures
may be pretty soft but you'll have an idea. If you already have your
standard for ROI then you'll also have an idea on which side of the
go/no-go threshold you stand.
Here's a thought: If you'll send me a populated copy of your Excel
workbook (which I would treat as confidential) and be willing to
collaborate with me via phone and email a few times then I can knock
together an Access demo application using your data. It would be done
in my free time over the next week or two. No charge. No, this is
not an offer of a free real live application. It's just to give you
an idea of the difference in performance and in results between two
ways of doing things.
That would help me discharge a New Year's Resolution.
If you're up for it, let's get started. In any case, the rest of the
post is intended for your guidance. You may know much or all of it
already.
HTH
--
-Larry-
--
Thank you gentlemen. You both are correct. I don't believe that
Access is
Excel on steroids and it has been a long time coming for this
switch. For
me, I'm probably more familiar with Access than I am with Excel. I
work with
relational databases, but I'll always think that I have a lot to
learn. I
realized after I hit 'post' that I didn't explicitly ask the
questions
(although you both provided helpful food for thought). Jeff maybe
you're
right, maybe I should stay in Excel, I'm not sure. However, I think
this
application will grow and I think it could grow nicely from Access.
I do
realize that there will be a design hump (hence this post) and that
this
beginning will later be further normalized and possibly repeated
(the long
way). I have continued reading various posts and I think (although
I know
that It'll need further work as it grows) I'll need to use one big
table and
then query it for report generation. I was trying to resist this
(main
thought for thinking I should stay in Excel) as there will be data
that is
partly repeated. I can't figure how to come up with a design that
will allow
it to be broken into peices without hiding data in the table names
or not
being able to exclude specific items for the average calculation.
Now I
think it will grow after this is accomplished and delivered, so that
each and
every bid will be entered and not just the summary used for
generalization
and estimate. If that were the case I think I could then come up
with a
better design. Until then I guess I was just throwing it to you to
see if I
was not considering one thing or another. Any further thoughts?
--
http://njgin.aclink.org
:
"Hold on there, Pardner..."
I usually agree with Jeff's posts and, in the main, do so here.
However, his (or my) assumption may be ahead of the mule:
By the time anyone seriously considers leaving Excel for Access
they
are usually years late in making that decision. People use Excel
as a
rudimentary data management facility long after the realization
hits
that there have to be better ways. There's also an emotional
attachment to something in which they've invested so much time and
effort over the years. The glue that holds their Excel application
together is the user, who knows the desired outcome of every
operation
and just what inputs must be massaged or provided to get there.
There
is usually no documentation or guidance, other than verbal.
Rarely
has anyone invested in a nice user interface with a menu of
choices
and forms or even a few command buttons.
Depending on your existing knowledge of relational database design
there may be a big hump to get over to make the transition from
Excel
to Access. Understanding normalization is the biggest key there.
Then, there is the Access interface and its universe of
development
tools.
My impression of your data management requirements is that you
have a
perfect candidate for an Access application.
For a professional Access developer, it would be a modest project.
For a neophyte starting from ground zero it could be a longish
project. Most of the neophyte's time and effort will be spend in
learning, applying new knowledge, learning some more, re-doing
earlier
work, etc. The Access learning curve is long and steep.
Jeff's admonition that "Access is not a spreadsheet on steroids"
is
absolutely, positive true. Their greatest similarity is that the
array of cells in a worksheet looks a lot like the datasheet view
of a
table in Access. Even then, they aren't as close as they look.
Also,
don't kid yourself that you'll "make an Access version of your
Excel
workbook". It can't be done. The Excel workbook becomes one
source
document that goes into the mix for creating the specifications
and
subsequent design for the Access application.
HTH
--
-Larry-
--
message Think twice before leaving Excel...
Access is NOT a spreadsheet on steroids. To get the best use of
Access'
relationally-oriented features/functions, you can't feed it
'sheet
data.
You will need to learn about and use relational database design
and
normalization. If these terms are new, you have a fairly steep
learning
curve before you.
Is there a reason you wish to turn away from something you
already
know?
Is there a reason (other than "I think...") for selecting
Access?
Good luck!
--
Regards
Jeff Boyce
www.InformationFutures.net
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
message
I want to go from an Excel workbook to an Access DB
application so
that I
can
add additional data and project items without compromising the
formulas
that
calculate the averages. I think if I persisted with Excel I
could
probably
get it to work, but I think it would be better to go Access.
The
purpose
of
this DB will be:
"To develop average costs for project items and use these
prices
to
develop
a reasonable overall average project cost. The average costs
for
project
items would be generated from criteria consisting of specified
projects
and
date ranges."
The average costs will ultimately be in the form of a report
and
will
consist of something like
Average Low Bid 2004|Average All Bidders 2004|Average Low Bid
2005|Average
All Bidders 2005|Average Low Bid 2006|Average All Bidders
2006|Average Low
Bid 2005-2006|Average All Bidders 2005-2006|Average Low Bid
2003-2006|Average All Bidders 2003-2006
if you still want to help and if