TEMP table records from 2 other tables

[Carl23]

I have 2 tables called ReportTaracker and Sheet1. The fields are:
ReportTracker
1.Report_Num
2.Facility_Name
3.Unit
Sheet1
1.Resident
2.Physician
3.Comment

I would like to create a TEMP table. The join can be created from the number '1' at the end of the Sheet1 table name and the Report_Num. I have been able to get the Sheet# before via inputbox:

   MyValue1 = InputBox("Enter File Number", "MyInputbox")
   tblname = "Sheet" & MyValue1

I am having problems with the INSERT query language in VBA. Any directions appreciated.
Thanks,
Carl23

[Rabbit]

I see no INSERT SQL in your code.

[Carl23]

I am trying to get all of the data into the TEMP table so the SELECT Query looks as follows:

SELECT ReportTracker.Report_Num, ReportTracker.Repo_Name, ReportTracker.Facility_Name, ReportTracker.Unit, ReportTracker.Repo_Date_1, ReportTracker.Repo_Date_2, ReportTracker.Repo_Date_3, Sheet1.Resident, Sheet1.Physician, Sheet1.Comment, Sheet1.Physician_Comment
FROM ReportTracker, Sheet1
WHERE (((ReportTracker.Report_Num) Like [enter Sheet#]));

I have tried the INSERT query below and get error code SYNTAX ERROR in INSERT statement

strSql = "INSERT INTO TEMP Resident, Physician, Comment, Physician_Comment SELECT Resident, Physician, Comment, Physician_Comment FROM [Sheet1]"
   strSql1 = strSql1 & " Facility_Name, (Repo_Name), (Unit), (Repo_Date_1), (Repo_Date_2), (Repo_Date_3), (Report_Num) "
   strSql1 = strSql1 & " SELECT (Resident), (Physician), (Comment), (Physician_Comment) FROM [Sheet1] "
   strSql1 = strSql1 & " & (Facility_Name), (Repo_Name), (Unit), (Repo_Date_1), (Repo_Date_2), (Repo_Date_3), (Report_Num) FROM [ReportTracker]"
   strSql1 = strSql1 & "  WHERE ((([" & tblname & "].REPO_NUM) = 1))"

Thanks,
Carl23

[Rabbit]

Your INSERT syntax is wrong, the correct syntax is:

INSERT INTO tableName (field1, field2)
SELECT field1, field2
FROM otherTable