Splitting record and sorting by 2 distinct date fields.

[suecobb]

I am trying to create a report which sorts stock transactions by date. The problem is that the database has a single row of fields which tracks both "buy" and "sell" information for each stock certificate. I need to separate the sides of the transactions, so that the buy portion is displayed separate from the sell portion. My problem is Crystal sees the 2 sides of a transaction as a single record.
What I want is something like this:
[buydate] [certno] [selldate] [certno]
9/10/99 CertNo111
9/11/99 CertNo112
10/12/00 CertNo111
10/13/00 CertNo112
What I get now is:
9/10/99 CertNo111
10/12/00 CertNo111
9/11/99 CertNo112
10/13/00 CertNo112

I cant't get them to NOT appear on the same detail line (or an associated detail a and detail b) even when I create a different alias for the table for each side of the transaction to try to get them to read as separate records. I hope my explanation of the problem makes sense. I am using Crystal 8.0 and am working from a data dictionary I created. Any ideas?

Thanks!

 

[balves]

Well, of course, your problem is really the database design because you have multiple transactions stored in the same database record.

However, perhaps a UNION ALL will work for you.

SELECT buydate, certno, 'B'
FROM TableX
UNION
SELECT selldate, certno, 'S'
FROM TABLEX
WHERE selldate IS NOT NULL

Then, on the detail line, put the data in the correct columns depending upon the 'B' or 'S' hard-coded field in the SELECT. You can't mess with the SELECT columns in Crystal Designer so this would need to be a stored proc.

 

[HowardHammerman]

You could also enter the logic of balves SELECT statement in the Crystal SQL designer and then base your dictionary off or that.

When you run a crystal report you are running a big "do" loop that starts with the first record and continues to the last. Most Crystal reports will go through the database three times or more. Crystal Reports training, consulting, books, training material, software, and on-site support. Scheduled training in 8 cities.

http://www.hammerman.com

800-783-2269

 

[suecobb]

Thanks to both of you for your help. It worked!

 

[suecobb]

Now I have a new issue with this report. I had a column called TranFrom and a column called TranTo. These are now aligned in the same column due to the join. One belongs with the date aquired and one with the date sold. However, now I need to search for the 'from's that match the 'to's so we can follow a stock certificate from sale to sale. Also, a single 'from' certificate may be split into numerous 'to' certificates and visa versa. Is there any looping procedure I can use to look for matches. I have tried a few things, but keep getting the error that I am exceeding the allowable number of loops (30,000). I have approx. 7000 records in the file. Thanks

 

[HowardHammerman]

This is hard.
You might have a 'hierarchial group'.

Or you may have exceeded Crystal's ability to do what you want.

I am sure you have looked at the Do..While..
operator. Crystal Reports training, consulting, books, training material, software, and on-site support. Scheduled training in 8 cities.

http://www.hammerman.com

800-783-2269

 

[chelseatech]

What I would do is to do report from the UNION join just selecting the "TO" records, GROUP by Cert No, and then do the matching From repords in a linked subreport for that certificate.

Should work fine

http://www.chelseatech.co.nz

Editor of Crystal Clear