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.

 

[Pat Hartman]

You would save yourself a lot of work by normalizing the tables. You should have only ONE table. Just add a date column. Since you are summarizing by month, just use the first of the month as the day for consistancy. You can format the date to show only month and year so you never see the day.

Then to produce your reports, you would need only ONE report with a query as its recordsource. The query would take a parameter specifying the date range. That would allow the query to print one month or twelve or anyother number of months. You can add month breaks and totals in the report.

This way when next January comes around, nothing needs to be done. The data stays where it is. Your form could also filter the data to show only a single month for a given year.

 

[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!

 

[Pat Hartman]

Just because your excel files are separate doesn't mean that your Access tables should be un-normalized. There is nothing to prevent you from appending the spreadsheets to a single table.