Table Lookup

[Salesman0Gil]

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? 

[comment]

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?

[Salesman0Gil]

There is an exact weight for each.

[comment]

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.

[Salesman0Gil]

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.

[comment]

2 minutes ago, Salesman0Gil said:

So there is one weight and 6 zones.

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

[Salesman0Gil]

Weight z1 z1 z3 z4 

1lb $1 $2 $3 $4

2lb $2 $4 $6 $8

 

Quick example.... 

[dr_john_pollard]

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, 2019 by dr_john_pollard

[comment]

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.

 

[Salesman0Gil]

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

[comment]

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.

 

[Wim Decorte]

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.

[Salesman0Gil]

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