Re: DB design calculate averages port from Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Larry you're a gem and I thank you very much. This project is also as a
favor in my spare time and although it’s for a sister department at work,
it’s free of charge for me as well.

Although I don't have actual results for comparison, I've been considering
the hypothetical difference in results and performance between different ways
of doing things. No this hasn't gotten me too far yet, but it has made me
consider several different things I wasn't thinking about when I originally
posted this message. The rest of the post I will keep for my guidance in
future endeavors. As for this one, your last post has already proven to me
that it is probably not worth the time.

I say this because upon further examination the Excel tables that I have
really aren't as mature or refined as I once thought. The expansion of
different items that are attempting to be tracked and incorporated are just
now being entered and that data is not complete in its entirety. This may
also be another argument for why it should probably move from Excel.
However, I don't want to disappoint you if I send you these tables and you
see that the data management application isn't really all its cracked up to
be. You also made another point about calculating the time for the ROI with
the number of people that will use this application. As it was originally
requested, only a few people would use this application and the output of
this application would be utilized by several more. The data items that it
attempts to manage may grow, but the growth in the number of people that will
be reached by its product will be marginal. So really, I'm now treating this
as a learning exercise and selfishly attempting to develop this so that it
will help me more in the future when it does count (if you will).

So, with all of that ignorance in mind :) if you still want to help and if
this will still discharge a New Years Resolution that would be great. (I'd
love to know what that resolution is exactly by the way, unless of course its
classified :) I am still in the "interview the end users" phase and am
trying to figure out what all will be needed and how it could possibly be
done and what it could possibly grow into. I don't think one table is a good
idea at all and am no longer considering this method. I am a bit concerned
about relationships and how to get to the attributes I'll need, as I need
them, but I think it can be done with the way I'm considering doing it now.
Unless this no longer fits your parameters (which would be no problem and I
would perfectly understand), let me find out a few more things and attempt a
design that I think could work and then I'll send you that with the Excel
tables as a source and then you can review & decide from there.

However it works out, thank you very much for even considering such an offer.

Best Regards
--
http://njgin.aclink.org


"Larry Daugherty" wrote:

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-
--

"scratchtrax" <scratchtrax@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A0C8C51D-2BF1-4555-A4FE-013908958453@xxxxxxxxxxxxxxxx
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


"Larry Daugherty" wrote:

"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-
--

"Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:uYbt$FUTIHA.5524@xxxxxxxxxxxxxxxxxxxxxxx
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
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"scratchtrax" <scratchtrax@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:31606744-D04E-484A-B952-7304608F491E@xxxxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: DB design calculate averages port from Excel
    ... A far better tool than Access, Excel, pencil & paper, stylus and clay ... cost way to go. ... Excel (or other spreadsheet "database") application, ... Depending on your existing knowledge of relational database design ...
    (microsoft.public.access.tablesdbdesign)
  • Re: entering bills and invoices
    ... He probably chose "Excel Spreadsheet" as the object type he's pasting ... cost of the target task from my example, Paste Special, Paste Link, object ... > "Steve House" wrote in message ... >> task that procures it actually gives you more accurate cash flows IMHO ...
    (microsoft.public.project)
  • Re: Is Excel the best MS Office tool to use for this business applicat
    ... Most people are familiar with the concept of a spreadsheet and learning to use Excel is just taking that and offering more tools, but always using the same mindset. ... change in availability as well as cost on a regular basis, ... and build a usable Project Quote sheet that will include his company's ...
    (microsoft.public.office.misc)
  • Re: DB design calculate averages port from Excel
    ... I inferred from your first post that you were already using an Excel ... evident low cost, low effort solution. ... is involved and some rudimentary design is employed. ... developers to develop applications which users will then use. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: DB design calculate averages port from Excel
    ... I inferred from your first post that you were already using an Excel ... evident low cost, low effort solution. ... you want to track is actually entered into the spreadsheet. ... is involved and some rudimentary design is employed. ...
    (microsoft.public.access.tablesdbdesign)