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

Repeat the same query for different tables

P: 1
For example, I have 10 tables in my db and I have created a query based on table 2. Instead of copy and paste query for 10 times, is there anyway using macro or vba to replicate this for the remaining tables?

My query sql code, says is as below:
Expand|Select|Wrap|Line Numbers
  1. SELECT [TABLE1].ID, [TABLE1].[Business Date], [TABLE1].[ Code], [TABLE1].DOB, [TABLE1].[FA], 
  2. Len TABLE1].[FA]) AS LEN, [TABLE1].PD,
  3. ([TABLE1].PD/0.62,2) AS PD1, IIf([TABLE1].PD>=100,100,Round([TABLE1].PD/0.25,2)) AS PD2,
  4. IIF(MID([TABLE1].[PDT],1,2)='HP','INDLE1',IIF(MID([TABLE1].[ PDT],1,8)='Staff','INDLE1','NIND')) AS CAT
  5. FROM TABLE1;
Oct 25 '13 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
Hi

I would suggest something like this perhaps
Expand|Select|Wrap|Line Numbers
  1. Sub UseTableVariable()
  2.     Dim i As Integer
  3.     Dim TableName As String
  4.     Dim sql As String
  5.  
  6.     For i = 1 To 10
  7.  
  8.         Select Case i
  9.             Case Is = 1
  10.                 TableName = "TABLE1"
  11.             Case Is = 2
  12.                 TableName = "TABLE2"
  13.             Case Is = 3
  14.                 TableName = "TABLE3"
  15.             Case Is = 4
  16.                 TableName = "TABLE4"
  17.             Case Is = 5
  18.                 TableName = "TABLE5"
  19.             Case Is = 6
  20.                 TableName = "TABLE6"
  21.             Case Is = 7
  22.                 TableName = "TABLE7"
  23.             Case Is = 8
  24.                 TableName = "TABLE8"
  25.             Case Is = 9
  26.                 TableName = "TABLE9"
  27.             Case Is = 10
  28.                 TableName = "TABLE10"
  29.         End Select
  30.  
  31.         sql = "SELECT [" & TableName & "].ID, " & _
  32.               "[" & TableName & "].[Business Date], " & _
  33.               "[" & TableName & "].[ Code], " & _
  34.               "[" & TableName & "].DOB, [" & TableName & "].[FA], " & _
  35.               "Len " & TableName & "].[FA]) AS LEN, " & _
  36.               "[" & TableName & "].PD, " & _
  37.               "([" & TableName & "].PD/0.62,2) AS PD1, " & _
  38.               "IIf([" & TableName & "].PD>=100,100,Round([" & TableName & "].PD/0.25,2)) AS PD2, " & _
  39.               "IIF(MID([" & TableName & "].[PDT],1,2)='HP','INDLE1',IIF(MID([" & TableName & "].[ PDT],1,8)='Staff','INDLE1','NIND')) AS CAT " & _
  40.               "FROM " & TableName & ";"
  41.  
  42.         '######## USE THE SQL HERE
  43.     Next i
  44. End Sub
If the table names are truely sequetial with a common text as illustarted then the Select Case construct can be replaced with this

TableName = "TABLE" & i

but the Select Case you can specify any table name for each pass.

??

MTB
Oct 25 '13 #2

NeoPa
Expert Mod 15k+
P: 31,308
If you have the same design duplicated in multiple tables then, almost certainly, you need to look at your design in light of the concepts of Database Normalisation and Table Structures.

The concept you're using (of splitting data between tables based on an attribute rather than the fundamental design) can, and will, cause you (or whoever comes after you to fix it) a great deal of grief.

MTB has answered your question, but I would strongly suggest that you take a step back and look at redesigning rather than follow an approach that will almost certainly lead you into many troubles.

Good luck.
Oct 25 '13 #3

Post your reply

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