Spawing new records

[The Mad Russian]

Hi everyone,

I'm doing some work for a client who runs a hog operation. They get faxed
(yes, faxed, and the bugger won't email me the data) reports from the
processing plant that outlines how many hogs and their weights in each
shipment. So a user has to key in about 200 hogs for each order (and there
are a lot of orders).

Presently they're doing this in Excel, but as we all know, Access is a much
better way to track this sort of stuff. I've written the db to handle it,
and a data entry form for the user, but there's one critical problem that we
have to overcome before I can sell the Access idea to the Excel users.

Generally, you enter the number of hogs and the combined weight for each
small group. Where the problem comes in is they need all of the records to
show 1 hog, 1 weight for that hog. The faxes may show "5 hogs, 1000 lbs.
combined". So the user has to take this one line and type out "1 hog, 200
lbs" five times. Of course, the combined weight is never an easy to divide
number, so the user has been entering "=1000/5" or whatever value, into
Excel, then copying the formula 4 more times.

What I want Access to do is let the user enter "5 hogs, 1000 lbs.", then
have Access enter "1 hog, 200 lbs." on five different records. Obviously we
shouldn't leave it up to the user to divide 5 into 1000 or 12 into 2785 or
whatever the fax has listed.

These quantities and weights are entered into a subform which sits on a main
form that has the shipper information. So there's 2 tables here, one for
shippers and one for hogs. Ideally I'd like to see the five different
records mentioned earlier show up in the query that combines the two forms.
From there the data can be spun out to the SQL program that they're using to
run all the various reports.

Thanks in advance for your help. I hope that I've described the problem
adequately.

Jeff

 

[Tom Ellison]

Dear Jeff:

The first suggestion I have is to allow the users to enter the data
just the way it looks on the fax. You've already agreed to that.

The second suggestion is to record the data exactly as entered. This
will allow the users to be able to check the data against the original
documents even several days later and make corrections. If you do as
you propose, they wouldn't be able to do this. The inability to see
the data in the same form it was entered would be a mistake. That
destroys the "audit trail" and prevents checking and correcting it.

This shifts the problem from one of changing how the data is recorded
to one of how the data is used and presented. Let's assume it can be
entered just as it looks but presented the way you want it to be
presented.

Does each hog have an identity? It would seem not. If they can enter
"5 hogs, 1000 lbs." then, at most, the 5 hogs in this one entry are
indistinguishable from one another. You are evidently assuming they
all way the same when you divide the total weight by the quantity.

Just how to handle this is, first of all, a user interface problem.
Why is it better for the user to see "1 hog, 200 lbs." repeated 5
times, rather than to see "5 hogs, 1000 lbs."? You could certainly
add another column to this to show that the weight per hog averages
200 lbs. In fact, you could show "5 hogs, 200 lbs. each".

I believe the question should be this: What is best for the user to
see? In terms of information, the user might want to see the shipment
sorted by ascending weight, and showing the number of hogs at each
weight. This could be by actual weight, or by "ranges of weights", in
either case giving the number of hogs at each weight, or in each
range.

I really cannot imagine a user interface where it is preferable to see
only one hog per line. Wouldn't this just be a list of weights?
After all, there's no sense in saying it is "1 hog" every time, when
it is always just 1 hog. A list of weights where 200 repeats 5 times
is more work to understand than a list showing quantities and weights.
In fact, I'd recommend it is easier to read if the computer combines 5
separate entries of 1 hog @ 200 lbs. into one row displaying 5 hogs @
200 lbs. each.

This is a sufficiently common principle of data handling that the
database is very capable of performing this "aggregation", while it is
not designed to readily perform the "disaggregation" you are
requesting.

Briefly, disaggregation could be performed by creating a temporary
table to hold the weights one hog at a time. You could create this
from the partially aggregated data (entered as I have strongly
recommended above) by iterating through the raw data in a recordset
and adding rows to the disaggregated temporary table.

Because the users generally require the ability to correct their
entries in the format they were originally entered, this temporary
table should be discarded and rebuilt from the original data every
time it is required. This will allow for any corrections to the data
that may have occurred. Trying to keep the disaggregated table
up-to-date at all times is an undesirable approach because of the very
considerable programming that can be required to respond to every
insertion, deletion, and update in the raw data.

Whew! Windy, ain't I!!!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[The Mad Russian]

Well, you seem to see what I'm trying to do, but unfortunately I'm no
further ahead.

But hold off, we may be able to negotiate an emailed version of the waybill
that is currently faxed... more news when it comes in..