Creating a query, where the table could be selected by a condition. Need assistance.

[Michael R]

Hi all.

I'm writing a report that utilizes a query called qryRecords

SELECT * FROM tblRecordsORG1;

however, there is tblRecordsORG1, tblRecordsORG2, etc. qryRecords should be based on tblRecordsORG?. My attempt is:

SELECT DISTINCT IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column1],[tblRecordsORG2].[Column1]) AS Column1, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column2],[tblRecordsORG2].[Column2]) AS Column2, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column3],[tblRecordsORG2].[Column3]) AS Column3....
FROM tblRecordsORG1, tblRecordsORG2;

this is not enough, because number of columns could be changed in a tblRecordsORG? type of table, and the number of tblRecordsORG? type tables could be changed also. That's why I need to write a query that is something like:

SELECT * FROM -Name of the control value-

but it should be a query, not an SQL string:

sqlStr = "SELECT * FROM " & Forms!MyForm!MyControl

because It should be used in several different places, and to function as a base to other, more specific quieries.

Appreciate any help.
Regards.

[patjones]

Hi all.

I'm writing a report that utilizes a query called qryRecords

SELECT * FROM tblRecordsORG1;

however, there is tblRecordsORG1, tblRecordsORG2, etc. qryRecords should be based on tblRecordsORG?. My attempt is:

SELECT DISTINCT IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column1],[tblRecordsORG2].[Column1]) AS Column1, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column2],[tblRecordsORG2].[Column2]) AS Column2, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column3],[tblRecordsORG2].[Column3]) AS Column3....
FROM tblRecordsORG1, tblRecordsORG2;

this is not enough, because number of columns could be changed in a tblRecordsORG? type of table, and the number of tblRecordsORG? type tables could be changed also. That's why I need to write a query that is something like:

SELECT * FROM -Name of the control value-

but it should be a query, not an SQL string:

sqlStr = "SELECT * FROM " & Forms!MyForm!MyControl

because It should be used in several different places, and to function as a base to other, more specific quieries.

Appreciate any help.
Regards.

Why don't you just write a SQL query for each case you have to deal wtih, and then pick out the proper one using a Case structure?

Dim strSQL1, strSQL2, .... , strSQLn As String
 
Dim rst As Recordset
 
strSQL1 = "SQL query for condition 1"
strSQL2 = "SQL query for condition 2"
. . .
strSQLn = "SQL query for condition n"
 
Select Case MyControl
 
     Case 1
 
         Set rst = Currentdb.OpenRecordset(strSQL1)
 
     Case 2
 
         Set rst = Currentdb.OpenRecordset(strSQL2)
 
     . . .
 
     Case n
 
         Set rst = Currentdb.OpenRecordset(strSQLn)
 
End Select
 

This approach might seem a little bit laborious, but it's a whole lot clearer than writing one huge SQL query that's all mucked up with "IIf's", and it'll be easier to debug than the huge SQL query. Likewise, if the number of conditions and/or columns changes, you just have to add or delete branches from the Case structure accordingly...

Pat

[Michael R]

Thanks Pat.

The thing is I need the query as a base for other queries, and I can't do it using VBA recordset.

Also, there could be many tblRecordsORG? Type tables and their columns number could change. I wanted one solution only so that I won't have to fix it each time more tables or more columns have been added.

[patjones]

Thanks Pat.

The thing is I need the query as a base for other queries, and I can't do it using VBA recordset.

Also, there could be many tblRecordsORG? Type tables and their columns number could change. I wanted one solution only so that I won't have to fix it each time more tables or more columns have been added.

What exactly do you mean by "base for other queries"?

It seems like even if you go the route of writing one big SQL query with a bunch of IIf statements, you'll still need to modify that query every time the number of tables/columns changes...

Pat