Jump to content

Table Lookup


Salesman0Gil

This topic is 1590 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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? 

Link to comment
Share on other sites

  • Newbies
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 by dr_john_pollard
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 1590 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.