1 query pulling info from multiple identicle tables

[blizma]

Hi guys.
For my coursework I was told I need complex queries to gian high grades.

This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.

I have tried everything, I fidled with the relationship but can't understand it.

Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.

I would really really apretiate all responses as this needs to be done asap.

Thanks in advanced.

 

[FoFa]

Use a UNION or UNION ALL select to pull the data.

 

[stallyon]

question: why would you want to link multiple copies of the same data and then query them together? surely the whole point of queries is to link data from 2 completely different tables by using a field or fields that can be found in both tables.

 

[blizma]

Right.. im not even 16 i have no clue what that is but thanks for reply

I think ill give more detail.

Say i have 5 table, 0001, 0002, etc.
the field names are, Income or Expenditure, Item Type, Amount, Item Price, Name.
The reason i have so many tables is because my coursework "problem" i have to solve is:

Your head office for a company, each week you get excel files sent to you with the shops progress. Create a database which alows the head office to import, and proccess the data... bla bla rets talks about switch bords"

So, so far, i have linked these excel tables.
I need to either be able to do a query using loads of tables, or make a 1 form import all data from those tables, or somehow merge all tables into one.

To me the easiest way would be to make a query that grabs info fom all the tables, right?

Could you maybe give me an already existing tutorial link? or sql code (/e that is, as i see othe peopel ask for that), or simular.

please?

 

[neileg]

Use a UNION or UNION ALL select to pull the data.

The man said it. Read up on union queries, they do exactly what you propose.

 

[blizma]

Ya man. I read it up and gives me sql examples. which i cant read orunderstand

 

[stallyon]

union queries is totally the wrong thing to do here.

you say you are given tables each week which you need to import into the database - what you need is a master table. this would be where all the data ends up. the master table would contain all the fields from the imported tables. then what you need is to import the data from one excel file into a temporary table, create an append query to add all the data from the temporary table into the master and then run the append query. when you need to do the next file, import that data into the same temporary table and re-run the append query

 

[blizma]

stallyon, thats what I though. It sounds easy, but I cant get it to work

 

[blizma]

Ok I read up on this stuff and i used the append method, it works brill =]

Thanks guys loads.

 

[neileg]

Unfortunately the assignment details you've quoted are ambiguous. What we don't know is whether the Excel sheets for a given week contain just that weeks data or a cumulative position with all the weeks up to the current week.

If it's one week at a time, the append model is probably the best bet. If the sheets are cumulative, appending the data will duplicate existing data. In this case linking the Excel sheets and doing a union is more appropriate.