How to link two tables without data repeat in crystal report?

[Prasadbabu]

Hi Friends,

I created one database like credit card statement. Purchases all in one table. Debited all in another one table.
I am trying to link two tables using with inner join. But its repeating the data depending on purchases record count.

Table1 = Purchases
Table2 = Received

SELECT * FROM Purchases INNER JOIN Received ON Purcheses.pAcount = Received.rAcount

I am using crystal report 9. Now the out put is coming like this (pls chk the attachment)
I dont want to show the red color rows data

Anyone can help me. thank you
Prasad

[BitBlt]

You can't really just "exclude" the data in red the way you show by using a join between the tables. There's no way to join the tables that will result in that.

You can sort of approximate it by using something like this:

select 
pDate as sortDate, 
pDate , 
pName , 
pAcount , 
pAmount ,
NULL as rDate , 
NULL as rName , 
NULL as rAcount , 
NULL as rAmount 
from Purchases
UNION
select 
rDate as sortDate , 
NULL as pDate, 
NULL as pName , 
NULL as pAcount , 
NULL as pAmount ,
rDate , 
rName , 
rAcount , 
rAmount 
from received
order by sortDate

It will give you something that looks like this (sorry for the formatting):

sortDate	pDate	pName	pAcount	pAmount	rDate	rName	rAcount	rAmount
2011-05-05	2011-05-05	xx1	AC1001	1000.00	NULL	NULL	NULL	NULL
2011-05-06	2011-05-06	xx1	AC1001	200.00	NULL	NULL	NULL	NULL
2011-05-06	2011-05-06	xx2	AC1002	1000.00	NULL	NULL	NULL	NULL
2011-05-10	NULL	NULL	NULL	NULL	2011-05-10	xx1	AC1001	500.00
2011-05-15	NULL	NULL	NULL	NULL	2011-05-15	xx2	AC1002	100.00
2011-06-07	2011-06-07	xx2	AC1002	800.00	NULL	NULL	NULL	NULL
2011-06-10	2011-06-10	xx2	AC1002	300.00	NULL	NULL	NULL	NULL

Then, in Crystal, just hide the "sortDate" column and you'll get fairly close to what you're looking for. You can, of course, play with the sort clause in the SQL statement if you want all the account data together, then use Crystal for grouping/totalling.

Hope this helps. Good luck!

[Prasadbabu]

Thank you very much, It is working fine. But one little error is coming

that is

in pAmount field data showing correctly only. but rAmount field data showing some symbols.

pls chk the attachment

Thank you
Prasad

[BitBlt]

Can't really help with that. All I can suggest is to check the data types on your table, then check and see what Crystal is doing for formatting.

[Prasadbabu]

Yes sir I solved the problem. instead of UNION I used UNION ALL. then it was coming.
Once again Thank you very much. Now its working fine.