Re: Easy to the Mind - not Access

On 29 May 2007 18:08:42 -0700, Deac <mwallen@xxxxxxxxxxxx> wrote:

You make it MUCH simpler if you first redesign the second table. It
should only have these fields:
MinValue, MaxValue, Zone, Amount
4001, 6000, 0, $0.101
4001, 6000, 1, $0.112
etc.
Put the primary key over the first three fields.
You get many more rows, but a much easier query. And you have
normalized a table and are thus working towards a better database
design.

Once you have done this, the query becomes something like (off the
cuff):
select SqFtPrice + Lot1SqFt * Amount as TotalPrice
from table1 inner join table2 on table1.Zone=table2.Zone
where Lot1SqFt between MinValue and MaxValue

Now you can also see that there is no need to store TotalPrice: it is
a calculated value that can easily be computed using the above query.
If you follow this suggestion, you are making a second step towards a
better database design, one without calculated values. Of course there
are sometimes good reasons to store calculated values, for example if
the prices may change and the TotalPrice as it was then needs to be
remembered.

-Tom.


I have two Access 2003 tables, one has a "Zone" and "Lot1SqFt" that
changes with each record. (8,000+ records)
A portion of this table:
CustomerID ZONE Lot1SqFt SqFtPrice TotalPrice
21 2 27100 $1,599.00
$1,979.00
37 4 15475 $1,068.00
$1,392.00
366 1 17190 $1,187.00
$1,292.00

The other table has fields (columns) for each "Zone" plus a Minimum
and Maximum field and "Records" (rows) that denote the size
"Lot1SqFt". (14 fields and 8 records)
A portion of this table:
SqFtMin SqFtMax 0 1 2
3 4
4001 6000 $0.101 $0.112 $0.123 $0.127 $0.130
6001 7000 $0.099 $0.102 $0.116 $0.121 $0.125
7001 20000 $0.097 $0.107 $0.097 $0.087 $0.097
20001 50000 $0.090 $0.099 $0.096 $0.076 $0.086

I need to have Access find the right value, in the 2nd table, for the
two values from the 1st table and use that value to determine the
TotalPrice (as we move through the records)

Examples: (Maybe not needed)
Zone 2 with a Lot1SqFt of 27100 has a value of $0.096 which gives
a TotalPrice of $2,601.60
Zone 4: Lot1SqFt of 15475 with a value of .097 = $1,501.08
Zone 1 is 17190 and .099 gives $1,701.81

This is straight-forward to the human mind - - - but my mind doesn't
know how to have Access do this! Can anyone help me with this?

.

Relevant Pages

  • Re: how 2 update a tbl field based on result of a math calculation
    ... Thanks for you suggestion, I created the query as suggested, i am able to ... get correct data from the query output into a field called "TotalPrice" in ... > Unless there is a reason why the TotalPrice should sometimes be different ... > The calculated field is as simple as typing this into the Field row in your ...
    (microsoft.public.access.formscoding)
  • Query inconsistency (MySQL vs PHP)
    ... Giving the following result in MySQL: ... When I run the same query in PHP ... Prev by Date: ...
    (comp.lang.php)