Help with a Double Relationship

[Joseph31]

Please see attached sample:

I need to find away to check or make a relationship using two different tables with two different fields.

I need to compare the Broker state with the table "broker_license_page" then I need to compare the broker company with the table "broker license_chart".

If the broker state is Maryland but the broker company is not listed on that states page then no value given, but if the broker company is listed on the page it should return the value need.

Thank you,

Jospeh

and return the value effective date and expires date.

Policies.zip

[Withan]

First your Broker_ID is a Number field in one table and a text field in the other.

I will change this.

Next, your relationship between policies and brokers is based on the company name. A no-no! What if you change the company name just a little?

Also, this same relationship is a many-to-many relationship. Also a no-no. You need a joiner between the two. Unless only one broker to a policy? That seems more likely. Will fix.

Also, you are putting your broker into policies by name, not ID. Do you want this to be a permanent relationship? If so, you have to do it by broker ID. It is the ony way to look up broker realted data reliably, like states in which the broker is licensed.

Also, your relationships in Broker_License Page and Broker License Chart make no sense. It seems you only need a list of states as a look-up, and you need a joiner between the Broker and the licences, so you can have a many-to-many relationship. (Many brokers might have a PA license, and a broker may have licenses in many states.

I have changed Broker_License_Page to States. I have changed its key field from RecordID to StateID.

Here is the file with relationships, etc. fixed. Next I will handle your original problem with some look-ups.

[Withan]

Now we add a concatenated (based on two fields) relationship between policies and the Broker_License_Chart. Only records that match the broker AND the state will show.

We also add another separate relationship to States so that we can shot the state name and abbreviation once we choose the state of the policy. Adding some new fields to allow choosing state and broker by ID instead of name...

Of special note, by using a pop-up menu for the broker ID and state, and a special related value list that only shows the second field (company name and state abbreviation), the ID's are stored in the file, but the user only sees company names and state abbreviations, not the number ID's which would confuse.

I added fields in policies for the broker's license dates and state code (license #?). I also kept the related fields, but with the relationship fixed.

Also, you were trying to use the Broker's state for this, when where he is licensed is the issue. He might be licensed in dozens of states, right? I left the broker state on your layout, but you need to set the state of the policy using the pop-up.

Now, your "Add New" Button and seller relationships need a lot of help, too, but I can only do so much! I'll whip it up for you for $100/hour. I spent about two hours on what I've done and that's yours.

You need some lessons... not my department. Just remember: Always use sequential IDs for every table. Only relate tables by sequential IDs. Look at the stuff I did and you'll get the idea a little. Also, sometimes you need more than one box in the relational chart for the same table to do things. Note States and Broker_License_Chart appears twice, to allow special relationships for how you wish to relate your data.

Withan Lemmon

Policies_New.fp7.zip