featch all data from tables into one table

  • Thread starter Thread starter Himanshu Mistry
  • Start date Start date
H

Himanshu Mistry

Hi all
I have a database in access 2000
there are 40 tables into it .
the name of all start with "GG ".

now I want to make a table and featch all the data into this big table named
as "BIGTable"

I have tried a lot.
but i am lost.
can anyone tell me how can i do it.
thanks in advanced.
himanshu
 
Why do you wish to combine the data into one table? Are all 40 current
tables recording the same type of information? What's your underlying data
structure? Have you normalized your data before determining the table
structure?

While it is possible to combine, it may/may not be a good idea to do so.

More information, please...

Jeff Boyce
<Access MVP>
 
1) Create BIGTable
2) Start a new query on the first of the 40 tables.
3) Add all the fields you want in BIGTable to the query grid
4) Choose Append Query from the Query menu.
5) When Access prompts you for the Append To table, select BIGTable from the
dropdown list and click OK.
6) Verify that the correct field names are listed in the Append To row in
the query.
7) Run the query.
8) Repeat steps 2-7 for the other 39 tables.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Himanshu,

If all you tables have the same structure, and you don't want to re-run a query 40 different times. You can write an SQL query like so:

SELECT * FROM GG_01
UNION
SELECT * FROM GG_02
UNION
SELECT * FROM GG_03
.....
SELECT * FROM GG_40

As long as all your tables contain the same fields (columns), this will work no problem. When you do a union query, you will no longer be able to use Design View, you can only go from datasheet view and SQL view.

Hope this helps,

Jim

----- Himanshu Mistry wrote: -----

Hi all
I have a database in access 2000
there are 40 tables into it .
the name of all start with "GG ".

now I want to make a table and featch all the data into this big table named
as "BIGTable"

I have tried a lot.
but i am lost.
can anyone tell me how can i do it.
thanks in advanced.
himanshu
 
Jim-

I recommended separate queries because a UNION will typically fail with this
many tables.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Jim Richards said:
Himanshu,

If all you tables have the same structure, and you don't want to re-run a
Click to expand...
query 40 different times. You can write an SQL query like so:
SELECT * FROM GG_01
UNION
SELECT * FROM GG_02
UNION
SELECT * FROM GG_03
...
SELECT * FROM GG_40

As long as all your tables contain the same fields (columns), this will
Click to expand...
work no problem. When you do a union query, you will no longer be able to
use Design View, you can only go from datasheet view and SQL view.
 
John

What would you suggestion for the maximum number of tables to introduce in a join query

Does it matter if every table has the same # of fields? or if you are doing any calculations

Just wondering

Jim
 
In early versions of JET, there was a limit of 16 tables or queries in a
query. The limiting factor is usually the size of the query compile
buffer - which I believe is still 64K. One trick when you get "query too
complex" is to assign short aliases to long table and field names - but you
can get "too complex" for other obscure reasons.

Because of all the wonderful things we can do with Access, it's easy to lose
sight of the fact that the JET engine is really a very smart inverted list
desktop file manager. It has many more limitations than does a "true"
database system like SQL Server - and this is one of them.

For this particular problem, 40 SELECT statements in a UNION query is most
likely 'way too much - regardless of the number of columns. Note that all
SELECT statements in a UNION must return the same number of columns, and the
relative columns must be of a "union compatible" data type.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top