SELECT INTO from multiple tables

[kidodynomite66]

Is it possible to use SELECT INTO to select from multiple tables?

I have 8 tables with various PK-FK relationships.

I want to query the tables (via user selected WHERE criteria) and create a new table (based on that criteria).

The new table will be displayed via a report.

I am having trouble with my code, and have not been able to find the solution through Google and YouTube.

So I'm beginning to doubt whether it is even possible to use SELECT INTO to select from multiple tables.



Thanks in advance for any assistance.

[isladogs]

Yes it is possible UNLESS doing so causes any limits or restrictions to be broken
e.g. the resultant table would have more than 255 fields or there would be more than one autonumber field ...etc

You can do so using joined tables ...or even using union queries (but you would need to save the union query first)
Also using outer joins will fail if the joins are ambiguous

Try building up slowly ...first with one table ...then two ...etc ...testing after each change.
If at some point, an error occurs you can pin down the issue.

See SQL SELECT INTO Statement (w3schools.com)

[kidodynomite66]

I like the incremental approach suggestion. I'll try it.

Thank you.

[isladogs]

You're welcome. Good luck

[kidodynomite66]

I am trying an incremental approach, however I'm getting the exact same error as before.

The SELECT INTO query runs just fine from the Access window (i.e., I hit "Run" then the desired table is created),

but the code in the VBE gives an error when I execute it (i.e., hit the execute button on the form).

What you see is a simplified version of the query I am trying to run, but alas I am getting the same result as with the more complex query, so perhaps I'm committing a fundamental mistake (?).

{By "more complex" I mean there are many more tables/relationships and with user selected WHERE criteria.}

[ssanfu]

@kidodynomite66,
Posting pictures of your code query SQL is not really helpful. Cannot easily read what the SQL or the code is, so cannot test anything.

Maybe make a copy of the dB, have enough records to illustrate the problem and post it.


I build my queries in VBA different than what your picture shows.
I assign the SQL to a variable, then use CurrentdB.Execute to execute the SQL

Private Sub btnExecuteQry_Click()
    Dim d As DAO.Database
    Dim sSQL As String

    Set d = CurrentDb

    sSQL = "SELECT tblAvailiability.strAvailiability, tblPlantType.IDPlantType"
    sSQL = sSQL & " INTO tblTest"
    sSQL = sSQL & " FROM tblPlantType INNER JOIN tblAvailiability ON tblPlantType.IDPlantType = tblAvailiability.IDPlantType"
    Debug.Print sSQL      '<<-- this helps the ensure the SQL is formed properly. (check the immediate window)

    d.Execute sSQL, dbFailOnError

    Set d = Nothing
End Sub

Alternate coding --

Private Sub btnExecuteQry_Click()
    Dim sSQL As String

    sSQL = "SELECT tblAvailiability.strAvailiability, tblPlantType.IDPlantType"
    sSQL = sSQL & " INTO tblTest"
    sSQL = sSQL & " FROM tblPlantType INNER JOIN tblAvailiability ON  tblPlantType.IDPlantType = tblAvailiability.IDPlantType"
    Debug.Print sSQL      '<<-- this helps the ensure the SQL is formed properly. (check the immediate window)

    Currentdb.Execute sSQL, dbFailOnError

End Sub

In your code, it looks like you are missing spaces in front of "INTO" and "FROM".

DoCmd.RunSQL = "SELECT tblAvailiability.strAvailiability, tblPlantType.IDPlantType" & _
"INTO tblTest" & _
"FROM tblPlantType INNER JOIN tblAvailiability ON tblPlantType.IDPlantType = tblAvailiability.IDPlantType"

[kidodynomite66]

@ssanfu, that was it ---- the missing spaces in front of INTO and FROM!

Just as I thought, a fundamental error.

Now on to the "more complex" query . . .

Thank you!

[ssanfu]

Happy to help... good luck with your project.