Multiple identical tables for different divisions

[HMerchant]

I am trying to report on data from 6 diferent tables with identical structures. Each table has data from different divisions. I need all of the divisions in one report with an identifier for the division on each record of the report. There is no division id in the tables, only the first three letters of the table is different.

I have CR 8.5 and MS SGL data base with ODBC connections.

 

[cjlarue]

How is the data from the six tables related? By Date range? perhaps you could give an example of the table schema. Could you use a sub report on each table in the main report. Could you create a view joining the sox table and aliasing the common names. A stored proc would probabley work as well.

 

[HMerchant]

Thank you for the quick reply, the data tables are Item Master records for inventory, with no parent child type links. Items are moved from division to division and each item is a unique item number. I could use subreports but want to list a item category then all items within that category for all division locations, so do no think, subreports by division will work well. I have not tried to use the alias to open all the tables.
Hal

 

[cjlarue]

when you say identical structures do you mean that the column names are identical in each table. If I understand correctly then each table reflects the current inventory of items for a single division. In that case I would join the tables either in a view or stored procedure and alias the common field names by adding the first three letters for the division code.

Select table1.Field1 as div1Field1
table2.Field1 as div2Field1
from table1
union all Table2

 

[IdoMillet]

You do NOT want to name the fields differently. Instead use the following SQL in a Command data source (if using Crystal 9) or in a View/SP as the report data source:
----------------------------------------------
Select table1.Field1, table1.Field2, 'Computers' as Division from table1
UNION ALL
Select table2.Field1, table2.Field2, 'Appliances'
from table2
UNION ALL
Select table3.Field1, table3.Field2, 'Chemicals'
from table3
----------------------------------------------

This assumes Table1 contains info from the "Computers" division, etc.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[cjlarue]

Thanks IDO. can't always see the forest for the trees.