I'm creating a shipment receiving form for my client. Among other things on this form, I want to show the "Ocean Freight" company, the "Customs Broker" and the "Drayage Company" (local trucking company) for a particular shipment, along with all their respective contact info. Click on the company name field under the Ocean Freight Company heading, and a value list pops up. Choose the company, and their contact info appears below. Then choose the Customs Broker under that heading and the same thing happens there, and so on with the Drayage company too.

So far I've done that easily with 3 separate tables called Ocean Freight Company, Customs Broker and Drayage Company. Also, in the "Receiving" table where the form resides, I have 3 fields: Ocean Freight Company, Customs Broker and Drayage Company, each using a Value List built off of the "Company Name" field in each of those tables. A relationship made between this field and the company name field in the separate tables easily links up to bring the contact info onto this form by including the Address, Phone, City.... etc... fields from each of these other tables on this layout.

(I hope this all makes sense so far)

Anyway this works great.

Only problem is, sometimes they use the same company for Ocean Freight and for Customs Brokerage, or Ocean Freight and Drayage, or whatever. Because of this, with it set up the way I have it now, I will have to duplicate company contact information in these separate tables. I would rather have all of these companies in ONE table, and then have the correct contact info appear under the company name in the 3 places on the one form.

It's probably right under my nose, but so far, I haven't figured it out.

Thanks!!

Hi it seems as though you are on the rigth track. I would agree that you could consolidate your tables to normalize it. You will need another field that would designate what type of company types that they are. You can use a checkbox field here to check off what type the company basically can be (Ocean Freight, Customs Broker, Drayage).

Then you can use the concept of conditional value lists to display only the type of company that you want in the pulldown. Do a search for conditional value list and you should come across many examples including some really basic concepts.

Thanks Mr. Vodka, I'll give this a try and let you know how it works out.