Trouble with relationships

October 30, 200817 yr

Newbies

I'm setting up a database for estimating the cost of all of the built-in's in a house that we're building. The problem that I am having has to do with pricing options for different finishes and materials for specific parts of a built-in.

The DB is setup with one main table that has a record for each built-in unit. With in that record, I have it setup to choose all of the different options and variables for that specific unit via pop-up menus. The pop-ups menus pull there values from separate 'reference' tables and a relationship is formed to effect the price of the unit based on which options are chosen.

Where I'm running into trouble is when I'm trying to use the same reference table to choose multiple options for diferent parts of the built-in.

Example:

Reference table set up with 2 fields. one field is the type of finish and the other field is the multiplier associated with that finish.

From the main table there are 4 fields, (box finish, face frame finish, Drawer face finish, and door face finish). Each of those fields have a pop-up menu of the records in the finish field from reference table. In the main table there are 4 additional fields that need to get the value of the multiplier for each specific finish from the reference table, based on what is chosen from the pop-up menus.

What I'm finding is that multiplier field in the main table isn't getting the correct value, or is getting no value at all.It is setup to look-up the value via a relationship. The relationship that I have setup relates all 4 of the finish fields in the main table to the same field in the reference table. I'm assuming that this is where the problem lies, but I don't know what to do about it.

Thanks for your help.

I'm sure that I'm diving in way over my head, as this is the first time that I've worked with FM in about 10 years... so please bear with me if I don't understand certain things that are suggested

-Dale