By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

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

100+
P: 176
Hi all.

I'm writing a report that utilizes a query called qryRecords
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblRecordsORG1;
however, there is tblRecordsORG1, tblRecordsORG2, etc. qryRecords should be based on tblRecordsORG?. My attempt is:
Expand|Select|Wrap|Line Numbers
  1. 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....
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 14 '08 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
Hi all.

I'm writing a report that utilizes a query called qryRecords
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblRecordsORG1;
however, there is tblRecordsORG1, tblRecordsORG2, etc. qryRecords should be based on tblRecordsORG?. My attempt is:
Expand|Select|Wrap|Line Numbers
  1. 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....
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. 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?

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL1, strSQL2, .... , strSQLn As String
  2.  
  3. Dim rst As Recordset
  4.  
  5. strSQL1 = "SQL query for condition 1"
  6. strSQL2 = "SQL query for condition 2"
  7. . . .
  8. strSQLn = "SQL query for condition n"
  9.  
  10. Select Case MyControl
  11.  
  12.      Case 1
  13.  
  14.          Set rst = Currentdb.OpenRecordset(strSQL1)
  15.  
  16.      Case 2
  17.  
  18.          Set rst = Currentdb.OpenRecordset(strSQL2)
  19.  
  20.      . . .
  21.  
  22.      Case n
  23.  
  24.          Set rst = Currentdb.OpenRecordset(strSQLn)
  25.  
  26. End Select
  27.  
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
Jan 14 '08 #2

100+
P: 176
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.
Jan 14 '08 #3

patjones
Expert 100+
P: 931
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
Jan 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.