Combining Many Linked Excel Tables Into One

[NateL]

Hi All,

I am VERY new and raw when it comes to Access. However, I really want to learn.

Currently we have about 15-20 uniform programs which we run bi-weekly reporting to review usage. There are about 3-4 different people running these reports and then analyzing them in Excel. What I need to do for myself and manager is to combine all of these 15-20 different reports (each in it's own Excel workbook) into one for us to have a high level review of the usage and products being utilized in each program.

All of these reports have the same number of columns and the field names are identical. At the beginning of each month we add 1 new column for the new month. I want to keep all of the same field names and just add the data from all of the spreadsheets below the field names.

Right now, I have already linked 3 of the tables into an Access Database. However, I am really struggling to figure out how I can merge all of them together without losing any of the data. Also, I wasn't sure how adding new columns would effect this.

Any help for this beginner is greatly appreciated.

Nate

[June7]

Regularly adding columns is a poor database design. It is not a normalized structure. Access has a limit of 255 fields in table. You will eventually run out.

Sounds like you need to build UNION query with the linked sheets.

[NateL]

Do you have a better suggestion for adding the new months or for this process? The one thing I thought about doing is adding all of the months for the next year so I am only adding columns one time a year.

Is there a link you have to teach me how to properly create a Union? I have tried a couple different set of instructions and I can seem to get it to work correctly.

I have attached a file which gives you 3 tabs which are examples of what I am trying to combine.

Thank you!!

[June7]

You need to understand relational database principles. Start with a review of tutorials at http://www.rogersaccesslibrary.com/

Normalized structure would be like:

tblProducts
SKU
ProductName
Category
SubCategory
Sizing
Status
SKUPref
Gender
Color
Dom_OS
DateEnterProgram

tblSales
SKU
SalesDate
Quantity


For UNION query, review http://www.w3schools.com/sql/sql_union.asp