excell and access

[rayh]

Hey all,

I am just a beginner, but need some help. I have been tasked to convert
excell into access.
the setup is this, there are three workbooks that contain several
worksheets each. the first is called price sheet, it has worksheets called
vendor name, these worksheets contain vendor name, part #, part desc, part
cost, unit of measure etc., the second is called model-bom, again it has
several worksheets called dept.*, each dept has part number, part desc, part
cost, vendor, u/m, qty used etc., the third workbook is called prod., it has
several worksheets call plant, these contain model number prod date, color
etc.
does or can anyone suggest how i design an access db to mimic the excel crud?

thanks

 

[Larry Daugherty]

Mission Impossible!

Excel and Access are distinctly different products. Some parts look
alike superficially and they both use VBA. They can be made to play
together in client/server relationships. But they are not wacky
versions of each other. You, me and a whole bunch of other people
could not convert Excel to Access.

Excel is a premier spreadsheet that is one of the best tools going for
applications that are calculate intensive. It can also be used for
data management functions. Because the learning curve slope is
shallow and incremental it is often used in data management
applications beyond the point it should be. The majority of Excel
"applications" are ad hoc creations that keep getting new additions
and frequent redesigns to adapt to growing needs. The user
contributes the missing parts of the application by real time
interaction. Excel can be used to produce some pretty sophisticated
applications. I've done some myself at client insistence.

Out of the box, Access is a Relational Database Management System with
bunches of developer tools and user interface goodies. It is a
premier low cost, low end to mid-range data management tool. The
slope of its learning curve is steep and long and therefore people
defer its use long beyond the point where it should have been brought
into play. Somewhat more rigor is required in Access to get anything
going. A lot more understanding and rigor is required to produce
applications that you would willingly inflict on your users.

Yes, I know; you meant to convert the application from Excel to
Access. That isn't what you said and that isn't what you've been
tasked to do. You need to take your new understanding back to those
who gave you the task and get them to buy into the more accurate
statement. Make sure that they understand that you are NOT going to
try to mimic the behavior of Excel.

You need a complete understanding of the problem your application is
intended to solve. You should start out by interviewing the players
and getting their input, maybe even call design meetings. You'll
continually hear: The Excel workbooks are the complete solution". To
that your response should be something like: "The truth of that
statement will be borne out by giving a new temporary employee of
average intelligence two hours of training: one hour of orientation
to our organization and work practices and one hour of training using
the Excel application to do work. Then turn them loose as a fully
functional user of the application." If the last quoted statements
are true than I suggest that you don't need to convert to an Access
application. I very much doubt that they are true. Most likely it is
incumbent on the user to know what rows and columns need what
particular values and the whole sequence of interactions with the
application.

So you develop and document a complete understanding of the problems
that exist(ed) and what must be done to solve them. Those documents
become the Problem Statement and the Product Specification.

Then you analyze the interplay of users with the application and
abstract the entities involved. Each entity type becomes a table that
will hold a record for each entity of that type within the scope of
your application. Getting the entities right is the foundation of an
application that can be readily completed and that can be extended as
new needs are revealed. If you get them wrong then your life will be
full of going back to do it over and then rippling table changes up
through everything that has been built atop them: Forms, Reports,
Queries. What is right and what is wrong? To find out you have to
learn about "normalization". Your data should be in at least 3rd
Normal Form. In your actual implementation, don't pass this point
until you have mastered 3rd Normal Form.

After your entities have been isolated and maybe even designed you can
turn to the creation of the Functional Specification. The Functional
Spec will describe the functions that resolve particular issues to
produce specific outcomes.

Notice that probably none of the above steps were taken in arriving at
the current Excel application. Seems kind of unfair, doesn't it.
However, these are the building blocks on which the rest of your
application will reside. Because you've done it right to this point
the rest will be a lot easier than it might otherwise have been.

The Forms and Reports and their Queries are yet to come. They are the
first things people see in Access and they think that's what Access is
all about. They are great tools and you can do lots of magic using
them but first comes the foundation.

You and your management would probably like to know how long it will
take you to get the project done. There is no way to know that.
Coming from where you seem to be I'd say several months at the very
least. If time is more important than money then engage a consultant
and pore over her/his work to follow along. The next best thing would
be to get management to send you to a school. There are also a few
good (not cheap) Access development courses on DVD/CD.

HTH

 

[David Cox]

The 90% rule (true 95% :-> of the time) is that when management has decided
that it is time to convert from Excel to Access it is way past time to
convert from Excel to Access, and the reason that the decision was made is
that the Excel app is not working quite right, and the guys that invented it
do not know how to extend it, or fix it, or are no longer around.

Larry has given you a superb reply. I suggest that you show it to all of the
management involved. If you are being asked to mimic an application that has
gone beyond its capabilities you may be heading into deep doo-do territory.

Let us assume the optimistic option:-
You had better have a vendor table, some parts will come from more than one
vendor

A parts table

part #, part
desc, ..., unit of measure etc.,

everything constant to do with a particular type of part

part cost

this will vary with time and vendor, and belongs on an order line table,
(cost when bought) and perhaps in a vendor catalogue table (cost now)

Bill of Materials apps are not ones for the novice or the faint-hearted. You
may be able to impress everybody by modding one of the sample Inventory apps
that are around, but professional help will save much time, grief and money.

http://groups.google.co.uk/advanced_search?hl=en

use terms like Inventory .mdb

This sound like your catalogue, which will contain current and previous
model details, and be linked to the parts table.

HTH

Mission Impossible!

...... loads of good stuff, some of which I have filched to add to my
standard text on the subject (Thanks)

 

[mnature]

Even though you have been tasked with converting from Excel to Access, I
would like to point out some software which may have some of the
functionality that the managers are looking for. It is called DBA
Manufacturing.

http://www.dbamanufacturing.com/

You can download it freely, and use a single copy without any charge. It is
not very expensive, if your company decides it would like to implement it for
a number of people. You would still have the problem of transferring all the
data from your Excel application over, but you would have to do that after
programming an Access solution anyway. This would give you a ready-made
template that is designed for all aspects of manufacturing. There are also
some nice tutorials that help you learn how to use the different modules.

 

[rayh]

David/Larry,

i really appreciate the responses. I guess I should be more specific about
the layout and results.
lets say i work at a manufacturing plant. i need to know by vendor how many
of each parts go into each different model of trailer and what the cost is
buy each dept. that builds it. In addition, using this data and a production
sheet, i need to report what parts and qty's are needed for say the next
weeks worth of production.
i have had experience doing this but it was a long time ago and i did it
using powerhouse and an image data base. as you guys can tell, i don't know
dodo about access and need to figure it out quick or i am up the creek!! as
is a common problem, management likes the idea, but are not thrilled about
change.

ray

 

[Larry Daugherty]

Powerhouse! I thought no one else in the whole world had heard of it.
My only experience with it was in helping to convert an application
from Powerhouse into Access. The guy who was supposed to be the
analyst for the Powerhouse side of things wasn't much ahead of me
doing the Access side. Anyway ...

You've tried to minimize what you're trying to address by detailing
just a cross section some of the data and functionality. I refer you
back to my earlier recommendations viz understanding and documenting
the whole application. Your understanding is paramount and you should
have documentation to tie it down. Get the other interested parties
to help you to understand the application and to sign on to your
understanding of it. The first couple of elements don't need to be
lengthy but they do need to be accurate and complete.

I would not interpret doing those things as a sign of weakness or
inexperience. On the contrary, they demonstrate that you know what
you're doing.

Management may well be unhappy at the prospect of change but they'll
either have change or they'll continue doing things as they are doing
them now. If you showed them the responses from David and me they
have likely accepted that change is necessary.

The fact that you aren't familiar with Access is an issue that you
should address head-on with your management. If you believe there's a
significant gap between management expectations and your own
capabilities, that's a problem you'll have to resolve. However, the
things I've suggested that you begin with aren't Access related at
all. Problem Statements, Product Specifications, even Functional
Specifications are things that are common to all product development
and certainly to software development. If you already know those
things then just write them down, it will take very little time. If
you don't already have a firm grasp on them then the time you spend on
the front end of the project in getting them right will be repaid many
times over during the project. If you begin a project without knowing
its dimensions you'll never know what to do next nor when you're done.

Just the little bit you described implies that some very specific data
resides in your application, that it's complete and accurate and
readily accessible. How did it get to be that way? How do you know?

Forgive the lecturing. I think that you know these things. At a wild
guess, and it has to be wild because I'm not acquainted with your
organization nor the application at hand, a blind estimate of 8 weeks
more or less for a competent consultant to deliver an Access version
of your application (with a Help file) might fit. Where you have to
learn Access as well as get into the swing of things in schema design
and user interface design I'd estimate triple that, without a Help
file.

As David suggested, you may be able to dig up an application that can
be made to fit. Beware tho' that doing so may require even more
changes than you'd like.

I wish you good luck with it whatever you do. I hope you continue to
explore Access. Once over that steep learning curve it's a great
platform. Keep posting back with specific issues.

HTH

 

[rayh]

Larry,

Boy did I date myself,,,yeah, I used powerhouse to design and implement an
ording,shipping, and Billing system for a plant I work at with P&G.
Anyway, I was the one who did all the data verification(along with
inputting it into excel), As you have quessed, i have also documented the
process and the specific needs of the business.
If I could just get the basic schema set and a basic understanding of
access, I would probably be set to rumble. Can you recommend any books that
would help me accomplish this?
Your feedback and suggestions have been great and I look forward to a
steady dialog if your up to it. thanks again!!

Ray

 

[Larry Daugherty]

Hi Ray,

My presence in these newsgroups is somewhat erratic. I'm often absent
for months at a time without prior notification. The message there is
that you should look at the newsgroup(s) as your resource, not an
individual. The general rule is one issue per thread.

A book I always recommend is The Access [YourVersion] Developer's
Handbook by Ken Getz et alia published by Sybex. 2002 was the last of
the series. You only need volume 1 (Desktop). Any version from
Access 97 onward would be useful to you. All of the editions use the
Reddick naming convention and there is an appendix devoted to it.
There is an accompanying CD with all of the sample code in
mini-applications that are really great. There are lots of other
authors and other books. It's worth a trip to Barnes & Noble in a
metropolitan area - or Borders, etc. I'd shop the bookstores to scan
the shelves for Access books and see which ones speak to you. Another
great book by Getz et al is the VBA Developer's Handbook from Sybex.
If Access becomes a large part of your life, so will VBA. Of course,
VBA works in the other Office applications.

microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign are a couple of great newsgroups
for Access newbies. I scan this one and those two every time I visit.
There are other newsgroups that address other areas.

Another fantastic resource is www.mvps.org/access It's chock full of
Access lore and has links to more. Be sure to visit.

A word of caution: I don't know Powerhouse and probably few or none
of the others here do. So you won't get far saying something like "I
did such and so in Powerhouse, how do I do that in Access?". You have
to present your issues in real-world terms or in Access terms.

Follow a naming convention. I endorse the Reddick naming convention
documented in the Developer's Handbook. I believe it's also available
as a download from MSDN. Do not accept the names that Access will
suggest as you get into using some of the tools. Change the names
that Access gives you to ones that make sense to your users in your
application.

To get started on your schema you have to identify and list every
entity in play in your application. Even if you did the original
application, list them. I recommend that you compile that list either
in Word (probably using Outline view) or in Excel. It isn't important
initially to identify relationships or relative table importance.
They're all important.

Once you have your list of entities in hand you have identified your
tables. I usually name each table for the singular of the thing it
contains and prefix the letters "tbl": tblItem, tblPerson,
tblCustomer, tblVendor, tblOrder, tblInvoice, etc...

Next you gather and list the attributes of each entity. These become
the fields of the table. Give these attributes meaningful names and a
brief description. Do a good job here so that you design to at least
3rd Normal form. By the way, I always use Autonumber primary keys.
If you intend to follow that practice, make the first field in each
table an Autonumber datatype with a name of the table root name and
the suffix "ID": ItemID, OrderID, etc.

At this point, or even before, you can open Access, give your new
application a name and commence to design your tables.

Next comes the relationships between the tables. Some new entities
may surface as you go through this step. Keep going back to the
earlier steps and make the needed changes. As relationships are
defined, go back to the field list of the many side or "child" table
and add a field for the Foreign Key. If you are using Autonumber
Primary keys it will be a long integer datatype and will have the same
name as the Primary key of the one side or "parent" table.

Once you have your table designs entered, go to Tools|Relationships.
Bring up the tables that participate in one-to-many relationships. In
each case, click and drag from the Primary Key of the one side to the
Foreign Key of the many side (should be a field with the same name).
Doubleclick the line and a dialog box will come up to help you
complete the definition of the relationship. You should turn on
Enforce Referential Integrity and enable Cascading Deletes. That
assures that you cannot create orphan records in the many table and
that deleting the one side will delete all of the records on the many
side. Again, that prevents the creation of orphan records.

Your schema is pretty well along the way.

HTH