choose table in parameter query

[poulsotw]

Hi

I would like to set up a query that uses two tables. One of the tables, the 'main table', wil be used every time the query is run. Each time a new record is created in the main table a 'new table' is created containing a subset of data - the name of this table uses a ref ID from the main table. I would like to be able to select which 'new table' to use in the query as part of a parameter query.

eg. Main table record 1 has a field JID of J0001 and a corresponding 'new table' titled J0001. record 2 in main table has JID J0002 and creates a 'new table' J0002 etc

so I would like to set the query up such that when a record is chosen in 'main table' the query knows the name of the 'new table' corresponds to the JID of the record in 'main table' ....

Have seen the same question asked but no answer - is this possible ?

any pointers appreciated..

 

[Uncle Gizmo]

>>> a 'new table' is created containing a subset of data <<<

I am guessing here, but I think what you really mean is that you want a subset of data that looks like a new table, however it's not a new table, but a subset within another table.

If you actually want a new table, with this subset of data in, could you please explain why? The reason I ask this question is there may be a better way of achieving what you want.

 

[poulsotw]

the Access DB is actually linking to a paradox DB - I can't change the structure of that. So the new table is created in the paradox DB.

The new table is basically a review table that stores records that meet certain criteria - based on another query that can be changed to add additional records to the review.

What I need to do is point Access to the correct table to query.

 

[Uncle Gizmo]

>>> when a record is chosen in 'main table' <<<<

Does this happen in an MS Access subform?

 

[poulsotw]

the record will ideally actually be selected in a paramater query dialogue box - however it is possible that a main form will be set up to represent the records also - but that's not decided yet.

so If I choose a record with an ID of J00122 in the main table several of the fields in the query will reside in another table called J00122 - but the query wont actually have this table name until I select the record with ID J00122. what I need to do is tell the query that one of the tables it is querying will always have the name of the ID from the current record in main table.

thanks for your comments so far,