Easy to the Mind - not Access

[Deac]

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?

[Tom van Stiphout]

On 29 May 2007 18:08:42 -0700, Deac <mw*****@kconli ne.comwrote:

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=tab le2.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?

[ManningFan]

On May 29, 9:08 pm, Deac <mwal...@kconli ne.comwrote:

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?

We answered this already. If you don't want to take our advice, don't
ask for it. I spent 10 minutes laying it all out for any idiot to
understand. Apparently you're the exception to the rule.

The table is designed incorrectly. Get that through your thick
skull. Redesign the table as Tom (and some of us before him) has
suggested and then come back and ask the question if it still exists.
However, once you properly redesign the table you won't have this
question anymore.

Database layout is best left to professionals. This is a prime
example I'm going to use from now on.