Returning multiple records 1

[sql99]

Hello everyone,

I have a crystal report in version 9 where I am pulling client information. Basically, these are the things it's pulling:

company name (comp_name)
company phone (comp_phone)
company address (comp_addr)
company representative (comp_rep)
company point of contact (comp_poc)

I have a view that pulls all of the above information so I'm just plugging these fields into the report. However I need to join this view with another table called "PERSONAL INFORMATION" to get the company point of contact email address, phone, and address. The comp_poc is a concatenation of the poc first name and last name (i.e. Sally Jones). The PERSONAL INFORMATION has the f_name and l_name fields. The only way I can think of doing this is to write a formula that compares the comp_poc name with the f_name and l_name in the PERSONAL INFORMATION and then get the poc_email, poc_address, and poc_phone from the table. When I do this, it returns multiple rows. For example, one row with the company representative information and then one row with the company point of contact information. I could also add these information to the existing view but don't want to make the view any bigger than it is. Is there a simple formula I can write to get one single record?

I hope I didn't confuse anyone. Please let me know if you need more information.

Thanks,
sql99

 

[GrlwBranz]

You may have to create a procedure and function as an api to pull in a single item so that you don't get multiple lines. Feel free to contact me if you need an example.

GrlwBranz

KayLynn Coleman

http://www.iccohio.com

 

[sql99]

Thanks KayLynn. Can you please show me an example?

Thanks,
sql99

 

[Madawc]

If you want to combine the data from two records from the same table, one method is to add it twice, the second time as an alias. It can then be used as if it was another table, so one detail line or 'row' can contain data from two different records. Does this help?

Another method is to do a group, suppress the detail lines and show what you want in the footer. This may be done using running totals, which can have formulas to select just particular recordss

Madawc Williams (East Anglia, UK)