looking up a value in another table (1 Viewer)

[archiecone]

Ok....I have a query that I am doing calculations in. In one of my calculations I am trying to look up values in another table that is the same value as the one in another table.

For instance, I am looking up a power factor for a motor. I have the horsepower in my query and I want to match that horsepower up with the lookup table. can anyone help me out?

 

[Jack Cowley]

You can use DLookup in your caluculations to get the required data from another table. Search Help for more details on how to use this function.

 

[Pat Hartman]

A Left Join is much more efficient than DLookup() and more flexible.

Open your query in QBE view, add the "lookup" table. Draw a join line connecting the main table with the lookup table. Then change the join line to a type of LEFT join (double click on the center of the join line and change the option). Once the lookup table has been added to the query, all of its columns will be available for use by the query.

Once you have more than a few thousand rows in either table, you should be able to see a significent performance difference between the two methods.

 

[Jack Cowley]

Pat -

Thank you for your insight. I just learned a valuable lesson!

Jack