Combined Tables

[Jerseynjphillypa]

What is the easiest way to combine 5 tables (table 1, table2, table 3 ...) that are linked into access from Excel into one table in access.



All 5 tables have the following fields
DateEnter (Date/Time)
Location (text)
First Name (text)
Last Name (text)
Used (Number)
Total (Number)

INSERT INTO [ALLTABLE] ([DATEENTER],[LOCATION],[FIRST NAME], [LAST NAME],[USED],[TOTAL])SELECT [TABLE 1].[DATEENTER],[TABLE 1].[LOCATION],[TABLE 1].[FIRST NAME],[TABLE 1].[LAST NAME],[TABLE 1].[USED],[TABLE 1].[TOTAL],
[TABLE 2].[DATEENTER],[TABLE 2].[LOCATION],[TABLE 2].[FIRST NAME],[TABLE 2].[LAST NAME],[TABLE 2].[USED],[TABLE 2].[TOTAL]
FROM [TABLE 1], [TABLE 2]
WHERE ((([TABLE 1].[LOCATION])="NJ") AND (([TABLE 2].[LOCATION])="NJ"));

I get the error (Duplicate output destination 'DateEnter'

How would I use a Union Query if I am not appending the data. I just want to make the table each time I run the macro in Access

[June7]

Hasn't this already been resolved in https://www.accessforums.net/showthr...ith-Date-Query

[Jerseynjphillypa]

Yes it's similar but I didn't know how to remove the DateEnter part since the information is already populated in the tables. I am also not appending the information from one table to another table.

I just want all the tables now to go into the same Data Table without doing the appending to another data table.

So would I do something like

INSERT INTO QueryTable
Select [Item],[Location],[Supplier]
FROM [Information]
UNION Select [Item],[Location],[Supplier]
From [Information 2];

I get a syntax error in from clause

[June7]

Follow the same syntax as shown in the other thread:

INSERT INTO QueryTable(Item, Location, Supplier) SELECT InfoUnion.*
FROM (SELECT Item, Location, Supplier FROM Information
UNION SELECT Item, Location, Supplier FROM [Information 2]) As InfoUnion;

[Jerseynjphillypa]

I get getting the error "Number of query values and destination fields are not the same"

I created the table QueryTable with the field Item, Location Supplier

I couldn't save the query since I had the error

[June7]

Looks like wildcard is not a good idea. Try:

INSERT INTO QueryTable(Item, Location, Supplier) SELECT Item, Location, Supplier
FROM (SELECT Item, Location, Supplier FROM Information
UNION SELECT Item, Location, Supplier FROM [Information 2]) As InfoUnion;

[Jerseynjphillypa]

Thanks for the help

Instead of a append table, is there a way it can me a make table?

Since i don't want the data to keep appending to the table

So the make table destroy the Querytable and create a new table every time I run the Query

[June7]

Run DELETE query to remove all records then run the INSERT for the new records. The following will delete all records in the table: DELETE FROM QueryTable;

[Jerseynjphillypa]

thanks for all the help..

Also If I want to change the Field Header can I do the following
INSERT INTO QueryTable(Item, Place, Supplier) SELECT Item, Location, Supplier
FROM (SELECT Item, Location AS Place, Supplier FROM Information
UNION SELECT Item, Location AS Place, Supplier FROM [Information 2]) As InfoUnion;

[June7]

No, can only insert data into a field that is actually already in the table. If you want the name to be Place then edit the design of the table and also adjust the query.

[Jerseynjphillypa]

thanks for your help June7