Interesting problem that I haven't run into in a while, and I can't remember how to do it. 

I have a reference table that contains the fields weight and zone. For each weight there is a different cost for the corresponding zone

I have a table that list the zone and weight of multiple items. I need to lookup the package cost for the items based on the weight and zone. 

A little help? 

Does the reference table have an exact match for every possible weight? Or do you need to lookup the nearest match above the given weight?

There is an exact weight for each.

Well, then define a relationship between the two tables matching on both fields (zone and weight), and define a field in the items table to lookup the cost from the reference table.

Ah. There is the catch. There is a field for each zone. So there is one weight and 6 zones. You need to cross reference the weight to the correct zone for the cost.

2 minutes ago, Salesman0Gil said:

So there is one weight and 6 zones.

And where is the cost? Are there also 6 cost fields?

Weight z1 z1 z3 z4 

1lb $1 $2 $3 $4

2lb $2 $4 $6 $8

 

Quick example.... 

4 hours ago, Salesman0Gil said:

Ah. There is the catch. There is a field for each zone. So there is one weight and 6 zones. You need to cross reference the weight to the correct zone for the cost.

Add the zone to the relationship as a second id if I understand this correctly. 

Edited November 14, 20196 yr by dr_john_pollard

4 hours ago, Salesman0Gil said:

Weight z1 z1 z3 z4 

1lb $1 $2 $3 $4

2lb $2 $4 $6 $8

This is not a good structure to have. I suggest you reorganize your data in a new table where each cost will be an individual record, with fields for weight, zone and cost. This is not too difficult to do: just import your existing data 6 times, each time importing the weight and one of the costs, and populate the zone field immediately after the import while the found set contains only the imported records.

 

Thanks. The only issue is that there are ~12 data sets, each with eight zones, so 96 imports is a bit much.

I am afraid you have lost me at this point. First you said:

13 hours ago, Salesman0Gil said:

I have a reference table that contains the fields weight and zone.

Then you said:

12 hours ago, Salesman0Gil said:

So there is one weight and 6 zones.

But going by your example, there is actually one weight and 6 costs (or maybe 4?). 

Now you seem to be saying something else again. I can't keep up.

 

23 minutes ago, Salesman0Gil said:

Thanks. The only issue is that there are ~12 data sets, each with eight zones, so 96 imports is a bit much.

It's only a on-time import that would give you a decent structure that will make your solution more extensible and less maintenance-heavy.

So I'd strongly suggest you change the structure and do the import as a one-time effort.

To close this out. I found an excel function (Index) that could accomplish this task.