conditional value lists

Hello,

I am trying to create a cost estimator. I have created 3 tables that are as follows: Company, item Category and Item Sub Category.

The "category" table is linked to "company" and the "Sub Category" table is linked to the "category" table.

Now in my 4th Table "Cost Estimator" I created 3 conditional drop-down menus that depending on what the user choses from the first and second one shows different results. So lets say the user choses company A it shows Category A, B and C but if the user choses Company B the Category shows D, E and F.

The relationships are as follows:

table 1 "Cost Estimator" has a field called "company" as a unique value and is connected to the table 2 "company" by the field "company2". the table 2 "Company" is connected to the table 3 "Category" by the field "companyID" being the unique value and "companyID-Link". and then Table 4 "Sub Category" is connected by the field "categoryID" on table 3 as the unique value and "categoryID_link" on table 4.

Now the first connection works great. I created 2 drop-down lists on "Cost Estimator" and the first one looks up the companies on table2 and the second one looks up all the "categories" depending on th company selected.

However the second connection only 'half' works. it only displays the first categories sub-category and only changes with the companies. So say if company A is chosen the categories show all the categories linked to this company (say A, B and C) but then the sub-categories only show the choices of Category A, no matter what category is chosen. So category A shows A's sub-categories as well as Category B and so on.

Does this make any sense at all? I feel like there is a very basic mistake in my connections?

Anyone? any help would be highly appreciated!

thanks

Edited January 25, 200817 yr by Guest