Joining tables

[hlardieri]

Hi

I am sure this is an easy one but I just can not get the right data to come out.

Crystal 9.2

3 tables Customer, salesperson, Email

My problem is the email table.

It 3 main fields are master type & master ID and email ID

When I try to do I report to show customer id and their email address along with the rep and their email address. I can only print either the cust or the rep not both.

Any help would be appreciated.

Thanks
hlardieri

 

[synapsevampire]

Your post makes no sense to me, try supplying technical information:

Example data (show how the tables join)
Expected output

-k

 

[Turkbear]

Hi,
How are you joining the tables?( That is what fields are linked).

Where are the cust and rep data fields ( I'm assuming the Cusotmer and salesperson tables)..

When you say you can show either but not both, what happens when you try?

 

[hlardieri]

My tables are as follows

Master_Type Master_ID INET1
SLP JG0000 ekjg@.....net
CUS A00005 Lees ART SHOP

CUSTNMBR CUSTNAME SLPRSNID
A00005 LEES ART SHOP JG0000

SLPRSNID SLPRSNFN SPRSNSLN
JG0000 JUDY GREENBERG

I tried linking all different joins but nothing worked.

My problem is the first table, it holds all the email address for both salesperson & customers.

I can oly seem to get either the cust email or the sales rep not both
I need the report to show cust id, cust name, cust email
rep id, rep name, rep email.

I know sounds easy but it I can not seem to get this to work

 

[synapsevampire]

Pretty whacked looking tables. And you've still given no indication of what you need to output, I doubt if the only output is the email field.

If you join master_id to custnumbr in the cust table, and then master ID to salespersid in the sales table, and make the joins left outers, you should get everything in the email table, even if it doesn't have a match in those tables.

You might also use a Union Query.

-k

 

[hlardieri]

OK tried that and yes i do get all the info but on the email part I am only getting the cust email for both.

I think maybe it is not a joining problem maybe I need an if statement somewhere.

I want to see on the same line the cust email and the rep associated with that cust along with the reps email address. I am thinking since the data(email) is in the same table, I need to somehow tell the 2nd field inserted(same field as the first email) to give me the reps email not the cust email.

 

[fredp1]

A simple way is to create 2 subreports.
The first one is for the Customer and the respective email addrses, and the second subreport is for Salesperson and there email address.

In the 1st sub, have 2 tbales, the Customer and the email table. By the sample you have shown, Link the CUSTNMBR to Master_ID. Change the JOin Type to Left outer.
Repeat for the SAles rep, SLPRSNID =>Master_ID.
Create 2 details sections and insert 1 sub in section 'a' and the second sub in section 'b'

Now you haven't specified if you want to see all the customers/salesperson if they have no emails, i'll assume that you will.
The email table structure makes it less straight forward... but create a formulae for the Customer email address,
if {TableName.Master_Type} = 'CUS' THEN {TableName.INET1} else ' '
and for the salesperson
if {TableName.Master_Type} = 'SLP' THEN {TableName.INET1} else ' '
Note: Use your tableName where I have TableName.
Use this formulae to display the email address.

Cheers
Fred

 

[synapsevampire]

Makes no sense.

From the example data you show, if you joined accordingly you'd have gotten both emails.

The latest requirement of wanting it on one line would have been covered earlier were you to have supplied the requested Example Output.

Since you think it's something else, go ahead and do something else.

What you probably want is to group by some common element (such as a SALESID?) and then you can concatenate the names into one variable and display at the group footer.

-k

 

[fredp1]

My solution will show the information of 2 lines, one line for the customer and one for the supplier. I didn't look close enough at the tables.

synapsevampire is right, the expected output should be specified.
I you haven't worked it out yet i'm happy to continue with the tread.

Cheers
Fred

 

[lbass]

I think you could add the Master table twice. Create a left join FROM Customer to Master on the customer number. Link Customer to Salesperson on Salesperson Id and then add a left join from Salesperson to Master on SalespersonID. Then I think you could create formulas like:

if isnull({Master.SalespersonID}) then "" else {Master.Inet1}

Then insert a maximum on this to display it at the group level. Repeat for the e-mail for the Customer, again inserting a maximum. Then suppress the details section.

I think this should work...

-LB