Squaring Up (Surveyors/Estimators)

[Guest]

I have a Schedule of Rates stored in a table.

tblRates:

Code (pk)
Description (Text)
Unit (Text)
Price (Currency)

I then have a table called OrderDetails which stores the detail of an order.

tblOrderDetails:

OrderDetailsID (pk)
Code
Description
Quantity (number)
Unit
Price

Code, Description, Unit and Price use a DLookup (in their default value) to
populate them from the Rates table. I do it like this because the rates are
subject to change, but the order must remain the same, and the fact that
one-off rates may be used.

Currently, the quantity field is populated by calculating the quantity on
paper. I would like to provide a form to do this automatically. This requires
a table (maybe 2?) with all the measurement information stored (namely the
measurements themselves and a description), with each entry related to the
appropriate OrderDetailsID.

Each set of measurements has adds and deducts. Those measurements that are
adds, get added to the total quantity, and those that are deducts get
deducted. There are typically three different units used (IT - Item, SM -
Square Metre, CM - Cubic Metre).

If the rate has IT units, every measurement entered goes to the line total
(to be summed at the end. eg.

Description Measure Total

Doors 1 1
Windows 2 2
_____
3

If the rate has SM units:

Description Measure Total

LH Elevation 12
2 24

FR Elevation 4
3 12
____
36

If the rate has CM units:

Description Measure Total

Concrete 3
2
2 12
______
12

Now for the questions. Firstly, any idea of the data structure required to
do this? (I envisage tblMeasurements, and tblMeasurementDetails). Secondly,
the form and its coding. For a SM rate (for instance), I would like to be
able to enter these keystrokes:

2 <enter>
4 <enter>
5 <enter>
3 <enter>
and have the form automatically split it up into:

Description Measure Total
2
4 8
5
3 15
_____
23

The final quantity at the bottom (23 in this case), should then go into the
quantity field in the OrderDetails table. I know what you guys say about
storing calculated results in a table, but, as I hope I have demonstrated,
the quantity field is not always calculated (I may enter an arbitary value, I
may calcualte it, or I may calculate it then ammend the total). Is there a
way to get the forms to do this? I don't need the actual code, just an
indication of the methods/functions required. I'm not even sure it is
possible to do this in Access, because of the limited controls it offers.
Would it be a case of coding my own control? If so, where should I look to
learn how to do this?

Thanks for any input,

Dave

 

[John Nurick]

Hi`David,

Think in terms of a table with one record per Unit (IT, SM etc.) that
stores the rules for dealing with the measurements entered for each
OrderDetail (or maybe a pair of tables related 1:M, tblUnits and
tblCalculationSteps).

The general idea would be to write VBA code that collects the
measurements the user enters, looks up the calculation rules, and
assembles them all into a string that can be evaluated. For that, check
out the VBA Eval() function.

As for the UI, it's pretty much up to you. For instance, you could have
a biggish textbox into which the user enters something cryptic like

2<enter>
4<enter>
5<enter>
3<enter>
<enter>

where the blank line is the signal to parse the input and apply the
rules. Or you could have one textbox for each value to be entered:
design the form with the greatest number that will be needed, and adjust
their Visible properties to hide those that aren't required for the
current Unit type. There's no obvious need for non-standard controls.