My Form is too large for the computers at work to run

[Guest]

Hello, I am creating a form which will when finished contain 36 columns each
column consisting of 2 sets of 15 combo boxes one side which displays a
budget figure for amounts delivered and the other side which will show
realtime amounts delivered. There will be a further 15 combo boxes at the
bottom to calculate the budget and actuall costs of the deliveries.
I have created the beta form which contains 12 columns without any of the
financial information - but even in this reduced state the computers at work
will barely open the form - and will certainly never run the completed
version.
Each of the 36 columns must display information specific to each of the 36
delivery points - including the budget numbers which are calculated using a
multiplier which is column specific.
Is there some way to shrink my overhead on this form? Please do no mention
newer computers as my work has already vetoed the idea of upgrading.
One other not so serious but still troubling issue, is that I cannot seem to
get all of my combo boxes to auto populate the first row of the list - row
zero. The first box does not, then the next 8 do, and the rest do not. I
have set the default value on all the boxes to line zero - and even set
function to refresh the queries and set the lines to the correct value - all
to no avail.

Thanks for any help you can give me on this.

 

[Larry Daugherty]

In order to get relevant advice you'll need to re-post describing in
some detail what is the real-world activity going on: Are you sure
that you don't have repeating data in your schema? My uninformed
guess is that you do; 36 pairs of columns suggests that each of those
pairs should really be a row in a table on the many side of a
relationship with the table on which your form is based on the one
side.

Assuming the above is true, change the data design. In the
Relationships window, establish Referential Integrity and enable
Cascading Deletes. Enter the data pairs into records in the new
table. Then create a subform based on the new table. Look in Help
for Form/Subform. It will walk you through the steps to creating the
subform on your form.

Post back if you hit a wall.

HTH

 

[Guest]

Either use a Value List for the Row Source of each combo box,
or get rid of the combo boxes and link the Row Source queries/tables
to the form source query.

1080 separate queries per page is too many.

1080 fields in a recordset is also too many, so that needs to be 15
records, each with 72 fields. (72 separate queries is too many, so you
still need to fix up the combo boxes), all on a subform, with the totals
on the main form.

15 records with 72 fields plus totals is still a lot, but if it's just
lookup
and multiply, it will probably work ok.

(david)

 

[Guest]

In the real world I have created a database that tracks feed deliveries to
barns. This database is based on the feed ticket delivery number and the
number of the group the feed is being delivered to.
The database is split into modules :
The delivery module captures all the information about the feed itself, the
ticket number, the type of feed, the amount of feed, and the date it was made.
the Circle check module captures all of the delivery information The truck
that delivers, the driver, which barn it is sent too, what bin at the barn it
is delivered to, how long the delivery takes, and the mileage travled.

The Group Setup module tracks the animals themselves - each group number
consists of the Barn Id the Bin Number, and the date that the group started.
The group tracks how many animals are in a room, when they start and when
they finish.
By attaching the group number to the ticket numbers I can track how much
feed is delivered and when to any given set of animals.
And all of this works great.

Now I am being asked to create a form which only displays this information.
There is an exisitng excell sheet into which the mill desk people enter all
of the information through out the day so that they can tell how much of any
given feed they have sent to a particular group, whether that amount is over
or under budget and what the cost per animal of that feed is.

To replicate this form I need one coumn per room for 4 barns each of which
have approximately 12 rooms or about 48 columns (I underestimated in my
earlier post). The top portion of which contains the date, the group number
and the number of animals in the group.
The middle portion is two subcolumns that consist of 10 to 15 fields to
allow for all the different feed types. The first subcolumn displays the the
budget amount of feed multiplied by the number of animals to give a value for
that feed type. The seond column uses the group number to pull and sum the
actual feed delivered for each type.
Beside each set of Budget and Actual deliveries is a tex field which
calculates the difference between the two and with conditional formatting
changes to red text if the feed type is over budget.
Then under each Budget and Actual pair is a field which pulls the last
delivery date for that particular feed.
then there is a field which sums all the feed delivered for that group.
The last section of each column will run a simple set of calculations for
each feed taking the amount delivered multiplying it by a cost factor and
then dividing by the number of animals to give a per animal cost budget and
actual for each feed type.

To tell you the truth, now that I have written this all out, I am really
doubtful whether I can make it small enough to run.

Once again, any and all help on this is most greatfully appreciated.

Nicole

 

[Larry Daugherty]

Hi Nicole,

Thank you for your complete and open response. I've always enjoyed
working with people like you seem to be.

"Be of good cheer"! You can most definitely use Access to achieve
business results that will be meaningful to you and your users. But
you have to get far away from "Excel spreadsheet think" in order to
get there. Your grasp of the processes at work with the feeds,
deliveries, locations and animals is excellent.

You are also quite correct in that there isn't enough room to show
everything about everything in all of the processes all at once.
There is no need to do that. It's just that when you get used to
using Excel or other spreadsheets it's common to jest keep adding
columns and rows. You don't see everything about everything at the
same time there either - but you can scan side to side or up and down
to see the bits you want. Unfortunately, that means that the
accuracy, effectiveness and efficiency of the (Excel) application
depends entirely on the individual users.

You'll do much better with a properly designed and implemented Access
application. So will your users after they settle in to the new
paradigm.

Change your thinking from "replicating this form", which sounds like a
trivial task to "Creating an application", which is *not* a trivial
task. Your application will have many tables, relationships, forms,
queries and probably, reports.

Learning Access and relational database theory combined is not a
trivial exercise. Because the two usually go hand in hand, the
learning curve for Access is long and steep. From your questions,
it's evident that you need to learn both. Actually, you can focus on
Access and accept that from time to time you'll be told to do things
in certain ways that don't really make sense unless you understand the
relational reasons why...

My response to similar/related issues in ...tablesdesign a week ago.
The thread is quiet now but you might find it edifying:
========================================
I'm a "top poster", see the original post below my response.
---
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
--
-Larry-
--

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

==================================================

Nicole, you have to identify and isolate the entities in play in your
application into tables You'll probably need at least erson, barn,
group, room. There will be many more. The easiest clue in your Excel
spreadsheet that you need a table is that you have multiple columns
holding the same kind of data. In those cases, a new table is created
as the many element in a one-to-many relationship with the table that
holds the existing row. The new table gets a name derived from the
attribute, say "barn" and then gets a row for each of its columns in
the spreadsheet. You will discover many other entities that are
implicit in the spreadsheet that you'd be well advised to make
explicit in a table. Where an attribute has a limited range of
values, create a table on that attribute and allow your users to
select from a list rather than re-type the same data repetitively.
Think of the typos you won't have. For your guidance on feeling OK
creating so many tables: "Tables are cheap, fields are expensive".
It is really the Relational Rules that determine, for the most part,
which data requires new tables and new relationships.

When it comes to your user interface, you'll typically have one form
devoted to each user task or task area. Again, you are well advised
to have many forms, each dedicated to a particular task rather than a
few crowded and confusing forms that are difficult to understand and
use. Each form may have one or more subforms. The first one set you
create will be intimidating. After that they're a breeze. The
form/subform paradigm provides a very useful means of dealing with a
"parent" and its related "children". For example a form based on
tblBarn and a subform based on tblRoom.

Once your schema is complete you are ready to move on to later stages.
Don't do it the other way. Neophytes try to design their reports and
forms first. After all that's what they see! It sometimes takes a wh
ile before they understand what's wrong with their approach and why
they are working so hard with so little to show for it. They keep
doing the same work over and over again.

If you and your management haven't formally analyzed what you're
doing, you really should. What is the size, in dollars, of the
"continuing to use Excel" problem? What, in dollars, is the worth of
a better solution that solves (list them) the "continuing to use
Excel" issues? Those amounts should be determined without the benefit
of having an estimate for the work available. That knowledge will
likely skew the numbers based on preferred outcome.

That second number has to be large enough to cover all of the
research, design and development efforts. It also has to cover the
cost of transitioning the existing relevant data to the new format and
training all impacted parties on the new software.

An estimate of doing all the work through to completion should be made
without benefit of knowing the earlier referenced numbers. If you are
not experienced with Access than you should engage a competent
consultant to provide the estimate. Among other things, the
consultant will require a populated copy of your existing spreadsheet.
That spreadsheet isn't the definition of the project. It is just one
supporting document.

Pardon the babbling. I've got to go do a bunch of other things.

Post back or again as issues arise.

Regards,