Many tables into one

[mtairhead]

I have 12 Access tables– One for each month of the year. They are named as such. (January, February, etc)

In these tables are Accounting Journal Entries, which aren’t very confusing. Just imagine that each table has column labels as follows:

Account Number Program Project Class Credit Debit

A typical entry would be:

Account Number Program Project Class Credit Debit
698 01 75 08 1785 1878


For each table, I have a report. That means I have a January report, February report, etc. Each report has a final total, which gives the total for the “Credit” column and the “Debit” column for the entire month.

Now for the tricky part: I want totals for ALL of the months combined. (The entire year) I need to consolidate the 12 tables into one, and make one final report. I know that I must do this with a query, but how? Every time I attempt to do it, it never works…And Mr. Clip-It, for once, doesn’t have the answer.

 

[dcx693]

You can use a Union query to gather up all the tables into one. Works best if all the columns in all the tables are the same. Then you can base your report on the union query.

See the Access online help topic called "Combine data in fields from two or more tables using a union query". Basically, you go into query design view, but don't work in the graphical view. You instead, write some SQL code.

 

[mtairhead]

Re

It works great – Thanks!

I ended up going with the Union Query. It took a little extra work, but at least now I know what people are talking about when they say SQL… I hadn’t used it at all.

I cannot combine the tables because it defeats the purpose of the system. What I didn’t report was that the 12 tables are really deleted and re-imported from Excel with a macro every time the database starts. That’s because the excel files are formatted to look like a journal entry… So that employees continue to enter journals exactly as they have, while the data changes for the database user.

Thank you!