Another Multiple Lookups Problem

[sopmac]

I have a table with three fields: territory, limit, and rate. What I need to do is lookup the first two fields from this table against my main table's "territory" and "limit" fields and then I want to return "rate" into a new table. Any help would be appreciated.

Thanks.
Ivan

 

[dawnd3]

So you want to match up the two fields from two different tables and bring the third field from one table to the other table? How many tables are you referring to? And why do you have the same fields in two tables? Please clarify a bit more if possible. Thanks Dawn

 

[sopmac]

Yes, i want to create a new table with the third field if the other two fields match.

I am referring to only one table, this table contains my main data that I have retrieved from another database.

Sure, there is data redundancy in this case; however, it is necessary for the application I am trying to create.

Thanks.

 

[dawnd3]

Ok, so you would first create a query with the 2 tables. Drag a link line between the 2 tables (or one for each field that you want to match up) and change the join type so that it shows all the records from the main table, and only those records from the other table (the one you want to update) that are equal. I am assuming in your case that you want to match only in cases where BOTH the territory and limit field match? Next drag the territory and limit fields down to the query grid from BOTH tables and the rate field from the main table. If you run the query now, you will see the cases where they match exactly and where they don't match. So, you will go back into the query design and in the territory and limit fields for the table you want to update you will put "Is Not Null" in the criteria. This will now give you only records where those fields match with the rate for those records. Now begin a new query, using this query and the table you want to update. Draw the same link lines but leave the join type as it is. Change the query to an update query and indicate that you want to update the rate feild from the one table with the value in the rate feild in the main table. (just double click on the rate field in the table you want to update, and then in the "update to" field but the [queryname]![Rate]. This should work. Let me know if you have any trouble. Dawn

http://www.calliopeconsulting.net