464,820 Members | 1,120 Online
Need help? Post your question and get tips & solutions from a community of 464,820 IT Pros & Developers. It's quick & easy.

# Easy to the Mind - not Access

 P: n/a 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? May 30 '07 #1