Linking multiple tables into one 1

[kristal9]

Hi,

I have five Excel spreadsheets. Each with multiple tabs. All are identical except for the actual data.

I am using the Excel files for Sales to input information that I can then report back out. Each spread sheet represents a sales division and each tab represents the sales team. The users then input reasons there from a drop down.

My next step is to consolidate all spreadsheets and all tabs (approximately 30 in all)into one big file.

I can link the individual tabs in Access but it becomes one linked table per tab. How can I get them all consolidated to one?

The original source of the data isn't my own so I can't do the process from start to end all in Access.
I must go from Excel into Access.

Any help at all would be appreciated.

 

[PWise]

Select *,"division Name1" As division ,"sales team1" As salesteam
from linkedtable1
union all
Select **,"division Name2" As division ,"sales team2" As salesteam
from linkedtable2
.......
Select *,"division Name2" As division ,"sales team1" As salesteam
from linkedtablexx
union all
Select **,"division Name2" As division ,"sales team2" As salesteam
from linkedtablexx
.....

And so on

 

[kristal9]

thanks for the post pwise. I appreciate it. but I'm afraid my Access skills are a bit hazy. is the first step to create multiple linked tables for each tab on each file? then use this code in a query to combine them into another table?

 

[PWise]

yes