Creating an append/make table query and exporting to excel

[nshatz]

I have two source tables, one with employee info and another with client performance data. In order to map employees to performance for clients they are associated with, I have a make table query for each employee. The result is 10 tables, one for each employee.



I want to create a table that combines the results of all 10 of my employee tables, but append/make table options do not work because they repeat the fields over for each table. So if there are 5 fields in each of my 10 tables, the result is 50 columns, the same five repeated for each of my 10 employees. What I want is for all of the data to fall into the five common fields.

It would also be great to automate this process as the two source tables are updated weekly. Any macro advice on quickly updating the source tables, running the make table queries, joining them all into one table and exporting to excel would be greatly appreciated as well. Result is going to be 100,000 rows + if that matters on the export bit.

Thanks.

[June7]

That would mean a UNION query.

But why are you making tables and not just using queries? Should not do make table queries because this is a design change. Why not just insert records directly to 'master' table? Is this weekly data cumulative or only 'new' data?

[nshatz]

only new data.

each employee is connected to client data via a client ID. there are multiple employees assigned to multiple clients. client ID lives in both tables and so I made a table that returns all client data where employee name is "employee 1", repeated 10 times. I then copy paste the results of each table into one sheet in excel and pivot/slice away...if there is one query that could effectively collect this same data, but in 1 table instead of 10 that would be great.

Thank you for your patience with an Access novice!

[June7]

I don't understand why making 10 tables and not just exporting from the original dataset or from a query that joins tables. Why do you need this data in Excel and not just doing Access report?