Combining Multiple Tables

[knowshowrosegrows]

I have 24 tables with identical fields that I want to combine into one table. If I were doing it table by table, I would use the ISERT Statement below. What would the language be to combine multiple tables like tblBHCareLog into tblCombinedLog9_24_15?

INSERT INTO tblCombinedLog9_24_15( Lname, Fname, MedicaidID )
SELECT tblBHCareLog.LName, tblBHCareLog.FName, tblBHCareLog.BestGuestMedicaidID


FROM tblBHCareLog;

[ranman256]

you really should stop using table names with dates. (the date goes in a table field, not the table name)
But
INSERT INTO tblCombinedLog9_24_15 ( Lname, Fname, MedicaidID )
SELECT LName, FName, BestGuestMedicaidID
FROM tblBHCareLog;

[knowshowrosegrows]

Thanks for getting back so fast and that is good feedback on table names. That language will allow me to add one table to the master table at a time. I am looking for a query that will add many tables together into the master at once.

[JamesDeckert]

you'll need to code it in vba to build the query as ranman shows, but replacing the part of the query that you need to change. Maybe put an array with the 24 variable parts and then loop through the array building the sql string using the array?

[June7]

Why do you have 24 tables with identical structure?

Build a UNION query to combine the tables in a single dataset. This will essentially create the single table should have been to begin with.

SELECT LName, FName, BestGuestMedicaidID, "BHCare" AS Source, #9/24/2015# AS LogDate FROM tblBHCareLog
UN ION SELECT LName, FName, BestGuestMedicaidID, "something", #9/24/2015# FROM tblsomething
...;

Now use that query as source for the INSERT SELECT action.