Repeat the same query for different tables

[janechua]

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:

SELECT [TABLE1].ID, [TABLE1].[Business Date], [TABLE1].[ Code], [TABLE1].DOB, [TABLE1].[FA], 
Len TABLE1].[FA]) AS LEN, [TABLE1].PD,
([TABLE1].PD/0.62,2) AS PD1, IIf([TABLE1].PD>=100,100,Round([TABLE1].PD/0.25,2)) AS PD2,
IIF(MID([TABLE1].[PDT],1,2)='HP','INDLE1',IIF(MID([TABLE1].[ PDT],1,8)='Staff','INDLE1','NIND')) AS CAT
FROM TABLE1;

[MikeTheBike]

Hi

I would suggest something like this perhaps

Sub UseTableVariable()
    Dim i As Integer
    Dim TableName As String
    Dim sql As String
 
    For i = 1 To 10
 
        Select Case i
            Case Is = 1
                TableName = "TABLE1"
            Case Is = 2
                TableName = "TABLE2"
            Case Is = 3
                TableName = "TABLE3"
            Case Is = 4
                TableName = "TABLE4"
            Case Is = 5
                TableName = "TABLE5"
            Case Is = 6
                TableName = "TABLE6"
            Case Is = 7
                TableName = "TABLE7"
            Case Is = 8
                TableName = "TABLE8"
            Case Is = 9
                TableName = "TABLE9"
            Case Is = 10
                TableName = "TABLE10"
        End Select
 
        sql = "SELECT [" & TableName & "].ID, " & _
              "[" & TableName & "].[Business Date], " & _
              "[" & TableName & "].[ Code], " & _
              "[" & TableName & "].DOB, [" & TableName & "].[FA], " & _
              "Len " & TableName & "].[FA]) AS LEN, " & _
              "[" & TableName & "].PD, " & _
              "([" & TableName & "].PD/0.62,2) AS PD1, " & _
              "IIf([" & TableName & "].PD>=100,100,Round([" & TableName & "].PD/0.25,2)) AS PD2, " & _
              "IIF(MID([" & TableName & "].[PDT],1,2)='HP','INDLE1',IIF(MID([" & TableName & "].[ PDT],1,8)='Staff','INDLE1','NIND')) AS CAT " & _
              "FROM " & TableName & ";"
 
        '######## USE THE SQL HERE
    Next i
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

[NeoPa]

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.