How to access two different records within the same table in one detai

[TZRick]

Hello experts!

I am having a bit of an issue with one report (Crystal Reports 9). The tables I am working with are as follows:

Contact1
AccountNo, Company, Contact (aka Candidate Name or Job Order Number), Key1 (aka Record Type: Candidate, Job Order)...

ContSupp
AccountNo, Title, Contsupref, LinkRecID, LastDate, CreateBy...

The ContSupp table is used to store relationships between Contact1 records. I would like to create a report as follows:

Group Header 1: Contact1.Company (for Key1=Job Order)
Group Header 2: Contact1.Contact (aka Job Order Number; for Key1=Job Order)

Detail Items:
Contact1.Contact (aka Candidate Name; for Key1=Candidate)
ContSupp.Contsupref
ContSupp.LastDate
ContSupp.CreateBy

I know this is kind of weird, but if you have questions, please ask away. Any help I can get on this will be truly appreciated.

Thank you in advance!

Taarik.

 

[TZRick]

FYI: The Link Field used is Contact1.Accountno (for Key1=Job Order)=ContSupp.Title

 

[TZRick]

FYI: Two individals on different forums responded to this post. Here are the responses:

"You'll need to add one of the table to the report twice, when you do a dialog box will come up and ask you if you want to give it an alias. You can figure it out from there."

". Click on report menu tab
. select Section Expert
. Select Details
. Enable the check box 'Format with multiple columns' on the right side
. as you enable the 'Layout Tab' appears on the top
. Click on the layout tab and fillin the blanks according to your requirements

"let me know if you need further assistance.

"Gary

 

[TZRick]

Okay. Here is an update. I was able to create the report (with the help of a colleague) using a sub-report. Here's how it works:

Firstly, I pulled all of the Job Orders (Contact1.Key1='Job Order') and had them displayed.
Secondly, I created a sub-report that linked on the Contact1.AccountNo=ContSupp.Title.
- The sub-report pulled Contact1.Contact for that item for ContSupp.AccountNo=Contact1.AccountNo.
- The sub-report detail also pulled information for that particular ContSupp record.

I tried the first two methods, but I did not understand how to implement them. I think the sub-report is the only way, even though it may not be efficient.

Does anyone see any way around the sub-report method? I really am a Crystal newbie with an intermediate database background (I speak SQL).

Thank you!

 

[hilfy]

As suggested above, add your Contact1 table a second time to the report with an alias. Assuming that the alias is "SupportCont", you would link your tables together like this:

Contact1.AccountNo -> ContSupp.Title
                      ContSupp.AccountNo -> SupportCont.AccountNo

You could then group on Contact1 information and have the SupportCont data available in the details.

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.