Combining SQL Tables

[rockman20]

I am running CR 8.5 accessing SQL 2000 databases through ODBC connections.

Okay, this may be beyond my scope of expertise right now, but I have a problem. (And everything I have learned has been through this site and lots of reading)I have 22 different SQL databases all with approximately the same format.

In each SQL database, there is a table called ASTUXXXX the XXXX are school and year related. So 4006 would mean 2004 and our Ben Franklin Junior High. What I need is a report that will create mailing labels for our entire district. I will have to somehow "merge" all of the ASTUXXXX tables from each database into one large table so that I can then use that information for my labels.

Can I do this in CR 8.5 or is this a SQL question that I should be asking in a different forum?

Thanks for all the help on everything I have posted!

 

[IdoMillet]

Create a View or Stored Procedure in SQL Server or use Crystal SQL Designer to create a data source that uses a UNION SQL statement to append all these tables into a single result set:

SELECT * FROM ASTU4006
UNION ALL
SELECT * FROM ASTU4007
UNION ALL
SELECT * FROM ASTU4008
...

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com