Create Report from Multiple Tables with same fields name

[hishamshahin]

I am working in a bank with more than 30 branches and all branches send me an excel sheets with the required data in order to collect all data in one file.
I unite Excel sheets for each branch, and linked excel sheets with the Microsoft Access 2010, So I have 30 excel sheets (with same Fields name, with different Data) Linked with Access But i need to generate one report with all branches data.
.
Kindly I need the detailed steps as i a not experienced enough with the Access.
Regards

[Seth Schrock]

You need to normalize your data. If you do, this problem will become non-existent. Here are a few links you should read: Database Normalization and Table Structure and Description of the database normalization basics. A little help as to what this means in your case, you need to add a field (I'll call it Branch) to your excel sheets. I also work at a bank (only 11 branches :) ) and our branch identifier is a number (Branch 1 is the main branch, Branch 2, Branch 3, etc.). So this Branch field would hold the branch number that the row of data came from. What would probably be easiest would be to insert this number when you get the Excel sheet from the branch. What you would then need to do is copy and paste all of those Excel sheets into a master spreadsheet and link the master to Access. You would still be able to use Queries to find information about a particular branch and present the data as if it was separate, but the actual storage of the data would all be in one Excel spreadsheet.

What I would do in addition to this, would be to create a table that just listed information about your branches. For example, something like the following

[I][U]tblBranch[/U][/I]
BranchID, Primary Key field
Name
Address
City
State
Zip
Region (maybe)
Anything else you track

You would then use relationships to tie the data in your Excel spreadsheets to this table. If you have specified regions for your branches, you would then be able to get data from your Excel sheet for all the branches in a specific region.

Database normalization will make your life soooo much easier. It may take a little longer to figure out, but once you have it done, you will save yourself from many problems down the road.