Is a DLookup the rigth way to go?

[Deac]

I was asked to help a friend with a database and am in over my head!
The customer table has been in place and they have used a paper copy
of the pricing table to make manual changes and computations in the
past (a lot, lot, lot of work!) I'm pretty new at this and am looking
for some help!

There are two tables in a database (of over 8,000 records) - one is
"t_Customers" the other is "t_PricingByZone" (data in this table
changes often).

The "t_Customers" table has two fields "Zone" and "Lot1SqFt" that are
used to determine cost. This part works pretty good and gives what
they need!

sample from "t_Customers"
CustomerID ZONE Lot1SqFt LinearFeet SqFtPrice
LinearPrice TotalPrice
2040 9 10100 50
$696.90 $25.00 $721.90
^^^ ^^^

The t_PricingByZone table has entries for each Zone (0 to 11) while
the fields have headers representing different sizes.

complete table "t_PricingByZone€"
Zone 1200 4000 6000 7000 20000 50000
100000 10000000
0 140 0.12 0.1 0 .089 0.067
0 0 0
1 140 0.12 0.12 0.12 0.069
0.056 0.054 0.052
2 140 0.12 0.125 0.12 0.06
0.057 0.056 0.054
3 140 0.12 0.12 0.142 0.065
0.058 0.056 0.054
4 140 0.13 0.13 0.12 0.067
0.059 0.059 0.057
5 140 0.135 0.135 0.132 0.07
0.06 0.058 0.057
6 140 0.136 0.12 0.12 0.069
0.061 0.056 0.052
7 140 0.137 0.12 0.121 0.068
0.06 0.054 0.052
8 140 0.138 0.111 0.12 0.067
0.059 0.052 0.057
9 140 0.12 0.11 0.12 0.069
0.059 0.051 0.057
10 140 0.12 0.12 0.12 0.069
0.058 0.051 0.052
11 140 0.12 0.12 0.12 0.069
0.059 0.052 0.052
A customer in "Zone" 9 with a "Lot1SqFt" of 10100 square feet has a
multiplier of .069 and would have a "SqFtPrice" of $696.90

(Find the Zone - then the 1st size (Field Name) that the Lot1SqFt is
less than - this will give you the multiplier.)

I'm not even sure if a number field can be used to designate a column
header (or Field Name)

I haven't been able to get the right multiplier using DLookup - I'm
not sure how to use it and don't know if this is the best way to do it
or not!

Any assistance that anyone can give me would be a great to me!!!

[rkc]

Deac wrote:

I was asked to help a friend with a database and am in over my head!
The customer table has been in place and they have used a paper copy
of the pricing table to make manual changes and computations in the
past (a lot, lot, lot of work!) I'm pretty new at this and am looking
for some help!

There are two tables in a database (of over 8,000 records) - one is
"t_Customers" the other is "t_PricingByZone" (data in this table
changes often).

The "t_Customers" table has two fields "Zone" and "Lot1SqFt" that are
used to determine cost. This part works pretty good and gives what
they need!

sample from "t_Customers"
CustomerID ZONE Lot1SqFt LinearFeet SqFtPrice
LinearPrice TotalPrice
2040 9 10100 50
$696.90 $25.00 $721.90
^^^ ^^^

The t_PricingByZone table has entries for each Zone (0 to 11) while
the fields have headers representing different sizes.

complete table "t_PricingByZone€"
Zone 1200 4000 6000 7000 20000 50000
100000 10000000
0 140 0.12 0.1 0 .089 0.067
0 0 0
1 140 0.12 0.12 0.12 0.069
0.056 0.054 0.052
2 140 0.12 0.125 0.12 0.06
0.057 0.056 0.054
3 140 0.12 0.12 0.142 0.065
0.058 0.056 0.054
4 140 0.13 0.13 0.12 0.067
0.059 0.059 0.057
5 140 0.135 0.135 0.132 0.07
0.06 0.058 0.057
6 140 0.136 0.12 0.12 0.069
0.061 0.056 0.052
7 140 0.137 0.12 0.121 0.068
0.06 0.054 0.052
8 140 0.138 0.111 0.12 0.067
0.059 0.052 0.057
9 140 0.12 0.11 0.12 0.069
0.059 0.051 0.057
10 140 0.12 0.12 0.12 0.069
0.058 0.051 0.052
11 140 0.12 0.12 0.12 0.069
0.059 0.052 0.052
A customer in "Zone" 9 with a "Lot1SqFt" of 10100 square feet has a
multiplier of .069 and would have a "SqFtPrice" of $696.90

(Find the Zone - then the 1st size (Field Name) that the Lot1SqFt is
less than - this will give you the multiplier.)

I'm not even sure if a number field can be used to designate a column
header (or Field Name)

I haven't been able to get the right multiplier using DLookup - I'm
not sure how to use it and don't know if this is the best way to do it
or not!

Any assistance that anyone can give me would be a great to me!!!

In a nutshell what you are doing is not the way things are done in a
relational database. You have data in your field names.
Square footage is data. The values 1200, 4000, 6000 are data not field
names.

Closer to what you want is

PricingByZone(Zone*, SqFtMin*, SqFtMax*, Multiplier)

0 1200 3999 140
0 4000 5999 0.12
0 6000 6999 1.0
9 20000 4999 0.69
Then, since I find writing a query easier than figuring out how
Dlookup works, something like

[Air Code]

Parameters SqFt Long;
SELECT Multiplier
FROM PricingByZone
WHERE [SqFt] BETWEEN SqFtMin AND SqFtMax

A shot in the dark at a Dlookup to do the same:

DLookup ("[Multiplier]", "PricingByZone","
& SqFt & " Between SqFtMin And SqFtMax")

[/Air Code]