Appending in CR is it possible ? please HELPPPP

[tdong]

here is what I would like to do. in my datasbase I have like 4 table not related table 1 has 1 2 3 4 table 2 has a b c d table 3 has 1 3 5 6 table 4 has a b h j

I would like to select all table and return

table 1
1
2
3
4
table 2
a
b
c
d
table 3
1
3
5
6
table 4
a
d
h
j


thanks

 

[Turkbear]

Hi, You fail to mention any Database information:
What database?
What version?
Can you create views in it?

Basically you want to create a view that joins the tables into 1 object with a added field identifying what table was the source.

 

[tdong]

Microsoft SQL server 2000, Crystal Report 9 yes I can create view for different table I don't want the cross join though.
here is what I need to do the 4 tables above are not related at all but they have a field date in common end date for example I want to select and display all the date between 3/1/2004 to 3/4/2004
table 1 has all 4 value match
1
2
3
4
table 2 has 1 value match
a
table 3 has 3 value match
1
3
5
table 4 has 2
h
j

 

[tdong]

Actual data i want to display bonds repo are different tables and they are not related related by the title hedging group but non of the data in there are realated

HEDGING
BONDS 4/22/2004
ID Entity Description Issue/Eff. Date Currency Notional/Face Settlement Amt
19 NCT CAN 2.50 4/2004 4/1/2001 CAD 100,000,000.00 101,125,000.00

REPO 4/22/2004
ID Entity Description Issue/Eff. Date Currency Notional/Face Settlement Amt
100 NCT CAN 4.5 9/2009 4/1/2004 CAD 10,000,000.00 9,537,142.34
101 NCT CAN 4.5 9/2009 3/15/2004 CAD 15,000,000.00 17,500,123.45

SWAP 4/22/2004
ID Entity Description Issue/Eff. Date Currency Notional/Face Settlement Amt
1 NCT IRS 4.40 4/22/2004 4/22/2001 CAD 15,000,000.00 200,000.00

 

[danjausten]

Hi,

You will need to use a command for this. Then you will need to use a union query. For example:

SELECT ID, Entity, Description, Issue/Eff. Date, Currency, Notional/Face, Settlement Amt
FROM BONDS
WHERE [date_field]>={ts '2004-01-03 00:00:00'} AND [date_field]<{ts '2004-04-03 00:00:00'}

UNION
SELECT ID, Entity, Description, Issue/Eff. Date, Currency, Notional/Face, Settlement Amt
FROM REPO
WHERE [date_field]>={ts '2004-01-03 00:00:00'} AND [date_field]<{ts '2004-04-03 00:00:00'}

UNION
SELECT ID, Entity, Description, Issue/Eff. Date, Currency, Notional/Face, Settlement Amt
FROM SWAP
WHERE [date_field]>={ts '2004-01-03 00:00:00'} AND [date_field]<{ts '2004-04-03 00:00:00'}

As long as all the colums are the same data type, this should work.

Hope it makes sense!

Dan

 

[tdong]

Thank you Dan I just did that and it work great hehehe

 

[kmcginn]

Why not just do a main report and three subrepports? You could put table 1 in the main report, and each of the others in a separate subreport. Use the date as a parameter in the main report and all subreports and link all the subreports to the main report using the date parameter. The users will only have to enter the date once when running the report.

Sometimes we forget that subreports can be unrelated.

You can format the reports so they line up and look like one big report.

Hope this helps.

 

[tdong]

that is a good idea too thanks