ivory_kitten said:
i currently have a rather complex series of tables in excel and of course a
really rough looking form due to excels limited capabilites, i am interested
to know if i could successfully build a cost calculator in Access to simplify
things.
currently i have one main file with all the costings in it, there are 5
different sheets, with costs from 5 different categories: Linehaul, Vehicle,
Labour, Materials and Storage, most of these contains some base information
and some calculations, some contain 2 different sets of base information and
the same calculations in each column.
then i have the "form" file, which the user uses, they input the parameters
for which they want to produce the costs for, also hidden in this file is a
couple of sheets with formulas and tests to formulate the correct pricing
it's rather complex in excel, and i've never used access before but am
thinking it might be time to learn, is this achievable in access?
Certainly, but the "idiom" of Access is very different from that of Excel.
The essential capability of Access is to store information in an
organised way and to retrieve it according to criteria. It's vital to
structure correctly the tables which hold your data, recognising the
various objects or "entities" you are modelling (these become tables)
and relating them together in one-to-one, one-to-many or many-to-many
relationships. Get that right and the rest falls into place. If you've
taken the trouble to divide your Excel version into separate worksheets
then you won't find this as hard as some do at first.
You use queries to retrieve data from these sets of tables, often to
match a particular set of criteria, like "which of my vehicles is over 5
years old?".
That much is true of any "relational" database. Access adds to this a
capability to build interactive forms and printable reports, with a rich
programming language (VBA, same as in Excel) to add "business logic", so
that you can have some action result from the click of a button, for
example, or triggered by a change to a value.
Initially, you'd have to look at your project from the perspective of
storage and retrieval, which can seem odd when coming from Excel. It's
essential to get your table design right, and time spent on this won't
be wasted (see references below). Once you have the tables right, and
can lift out selected information using a query, it'll be time to build
your form.
Forms are most commonly built to enable the user to step through records
in the database (often the results of a query). Access provides a superb
wizard which (if you table design is right) can do most of the work for
you - a wonderful learning tool. If your form is less a view onto a
series of records than a calculating front-end, then that can still be
built very successfully in Access but the dynamic is a little less
usual: your form may be firing off lookups (using VBA) in response to
what users do with the various controls (boxes and buttons) on the form.
So, my guess is that it would be fairly easy for an experienced Access
developer to build, but a little more of a challenge for a newcomer.
My advice, for what it's worth, is that you should have a go, and the
volunteers here will provide some very solid expertise. (Be wary of the
one "contributor" here who may be pitching for work, by the way.) You
must get your tables right first, and noone will be able to give you
very specific advice on the form until the table structure is clear, so
make that your first goal. Later, if you provide a "narrative" of how
you'd want a user to interact with a form, it should be possible to make
some concrete and implementable recommendations. There will be a lot to
learn, but Access is very rewarding.
Those references:
Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)
Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial
Microsoft training: Access 2007:
http://office.microsoft.com/en-us/training/CR101582831033.aspx
Microsoft training: Access 2003:
http://office.microsoft.com/en-us/training/CR061829401033.aspx
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/
A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html
A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
Phil, London
