SQL Query for Access

[TimC]

I need a SQL query for Access. I have a table, Policies, with columns 'Type','Age', and 'Duration' . I have other tables Type1, Type2, Type3, and Type4. The rows for these 4 tables are 'Age' and the columns are 'Duration'. I need to use the info from the Policies table to call the correct 'Type' table, and get the data from the correct cell based upon the 'age' and 'duration'. How would I do this in a VB program?

Is there a way to do this in Access and have the results in a column in the same table or a query or on a form?

Thanks

[DrewDog_21]

In the POLICIES table, what are the values that appear in the Type column? Is it numeric like 1, 2, 3, 4 or text like Type1, Type2, etc.? You need to somehow match the value of Type of the current record with the table name for that type.

e.g. if the value is numeric:
"SELECT * From Type" & myRS.Fields("TYPE").Value & " Where
AGE = " & myRS.Fields("AGE").Value & " And DURATION = " & myRS.Fields("DURATION").Value


[Edited by DrewDog_21 on 08-03-2000 at 02:45 PM]

[TimC]

'Type' is a text field,as is the names of the other tables, but wouldn't that work if I changed myRS.Fields("TYPE").Value to myRS.Fields("TYPE").Text, or something as such?

[DrewDog_21]

Yep, that should work.