calculation fields by using lookup expression

[Mony]

Hi there, here is my situation ..

Table 1:

Customer ID Customer Commission1 Commission 2 Commission 3 Silver Gold Diamond
1 ...................Mony ........ 10 ........... 20 ..................... 30 ...........?.........?.........?


Table 2: (lookup)

Rate Product 1 Product 2 product 3

Silver . 2 ........... 3 ......... 4
Gold ...5............ 6 .......... 7
Diamond 8 .........9 ......... 10

my question is: Can I set up a lookup by using table 2 to calculate table 1 silver, gold, and diamond

For example: I want to know Mony's commission1 on silver, gold, and diamond ...



It would be: silver = 10 *2 gold= 10*5 diamond= 10*8


I know I can do the calculation in the query.. but the point is the rate will keep change/add .. I don't really want keep going to the design field to change or add the rate.. it makes easy just change or add the rate in the table 2.

is that possible?

I appreciate if you can tell me how or you have a better idea to help me out here


Mony

[orange]

You should research Normalization and Table design.
When you see fields with Names such as Customer1, Customer2,... CustomerX and
Product1, product2.... It's a sign that the tables are probably NOT Normalized.
You should learn the concepts involved in Table design before trying to create a database.
see http://holowczak.com/database-normalization/
http://www.phlonx.com/resources/nf3/

[June7]

As orange says, this is not a normalized data structure. Will cause lots of frustration.

However, try:

DLookup("Product1", "Table2", "Rate='Silver'")

[ssanfu]

The table structure you have is called "Committing Spreadsheet".
Spreadsheets are short and wide.
Access tables are tall and narrow.

set up a lookup by using table

I never use look up fields (different than look up tables)
See http://access.mvps.org/access/lookupfields.htm

but the point is the rate will keep change/add

Then the rate should be stored in a table.