how to join many tables without ending with huge number of records

[kuanli]

I have a database with 10 tables and I need to put them into one big table so that they can be exported to Excel as a spreadsheet. The problem is that when I use a query to do it, the number of records mushroomed.

What I have is something like this:
ID field1 field2 field3
1 A H AA
1 A H AB
1 A H AC
1 A G AA
1 A G AB

I wonder if I could expand the fields so that I can have just one record for all information above. It should look something like this:
ID field1 field2A field2B field3A field3B field3C
1 A H G AA AB AC

Is it possible to achieve this? If yes, how can I do it?

Thank you very much.

Kuan

 

[thornmastr]

Consider a Union query. If you’ve never used them the explanation of Union queries follows.


Accessing and Changing Relational Data
Combining Results with UNION
The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types. For more information, see Guidelines for Using UNION.
UNION is specified as:
select_statement UNION [ALL] select_statement
For example, Table1 and Table2 have the same two-column structure.
Table1 Table2
ColumnA ColumnB ColumnC ColumnD
char(4) int char(4) int
------- --- ------- ---
abc 1 ghi 3
def 2 jkl 4
ghi 3 mno 5

This query creates a UNION between the tables:
SELECT * FROM Table1
UNION
SELECT * FROM Table2
Here is the result set:
ColumnA ColumnB
------- --------
abc 1
def 2
ghi 3
jkl 4
mno 5
The result set column names of a UNION are the same as the column names in the result set of the first SELECT statement in the UNION. The result set column names of the other SELECT statements are ignored.
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause. For more information about the effects of different collations, see SQL Server Collation Fundamentals.
Any number of UNION operators can appear in a Transact-SQL statement, for example:
SELECT * FROM TableA
UNION
SELECT * FROM TableB
UNION
SELECT * FROM TableC
UNION
SELECT * FROM TableD
By default, Microsoft® SQL Server™ 2000 evaluates a statement containing UNION operators from left to right. Use parentheses to specify the order of evaluation. For example, the following statements are not equivalent:
/* First statement. */
SELECT * FROM TableA
UNION ALL
( SELECT * FROM TableB
UNION
SELECT * FROM TableC
)
GO

/* Second statement. */
(SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
)
UNION
SELECT * FROM TableC)
GO
In the first statement, duplicates are eliminated in the union between TableB and TableC. In the union between that set and TableA, duplicates are not eliminated. In the second statement, duplicates are included in the union between TableA and TableB but are eliminated in the subsequent union with TableC. ALL has no effect on the final result of this expression.
When UNION is used, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. There can be only one ORDER BY or COMPUTE clause after the last SELECT statement; it is applied to the final, combined result set. GROUP BY and HAVING can be specified only in the individual SELECT statements.
See Also
UNION
©1988-2001 Microsoft Corporation. All Rights Reserved.

 

[kuanli]

Thanks for the reply.

I'm afraid UNION won't solve my problem. UNION can get rid of duplicate records. But my records are not duplicates. They contain the same values for many fields except one or two. In addition, the tables are of different structures.

 

[thornmastr]

OK, now that I know the Union has fallen by the wayside, the other method, which will work is going to be more work for you. Create a table. Define your record structure as you want to see your final product.

In VB code, you will need one recordset for each of the ten tables plus one more for your new table. Read the first record for table one and set those equivalent values on your new table to those values. Now go to table 2 and do the same. Continue through table ten. At this point, the first record of your new table is created. Now go to the next record for all ten tables and build the second record of your new table. Continue until you have exhausted all ten tables and you should end up with one table exactly as you want it to be.

Robert Berman

 

[kuanli]

Thanks Robert. This is exactly what I want.