two tables, one table has value of 2nd table name

[Kirby Bakken]

I've got three tables, person_per, person_custom, and person_custom_master.

The idea is that person_per has unique ID values in the per_ID column, as does person_custom. The person_custom table then has 'n' columns, c2, c3, c4.... cn. The 'name' of the c2, c3, etc. columns is contained in the person_custom_master table. That table has a custom_Field column, and a custom_Name column. The values of the custom_field column are c2, c3, c4, etc. I need to do a query of the values in person_custom, using the name of those c2, c3, etc. fields from person_custom_master.

So, for example, if the person_custom_master table had a row where custom_Field was equal to 'c3', and the custom_Name was 'Birth Date'. I can do a query like this:

SELECT per_Name FROM person_per LEFT JOIN person_custom ON person_per.per_ID=person_custom.per_ID WHERE c3='2010-03-10'. But I'd like to do a query where I said Birth_Date='2010-03-10'. I don't know how to associate the table value of person_custom_master for Birth Date with the 'c3' name in the person_custom table.

Thanks for any help,

Kirby