Records in Multiple groups, I want one instance of the record 1

[idd]

I am using Crystal Reports 9 and MS SQL DB.

Its a new job and Crystal is new to me, I really need some help.

The simplified version of the problem is
I have two tables
Members and PD

Members table has the following

Mem_id
Mem_Status

PD table has the following
PD_ID
PD_MEM_ID
PD_Status

My criteria is that the report should bring back all members with Mem_status = 94
Who don’t have a PD Record
As well as those who do have a pd record

The results should be grouped by the PD status.

The problem I am facing is:

Some members have multiple PD records, and the results should then only show one record for these members,
If member has PD then
if pd_Status = 30 then show that record only
if not then show for the next group
but only show one record for this person.

Basically if the member has a PD_Status of 22 then show that record if not then show any other record but only one record.

MEM table
MEM ID Mem_Status
1 94
2 94
3 94
4 94

PD Table
PD_ID PD_MEM_ID PD_Status
12 1 30
13 1 95
14 2 30
15 2 95
16 4 99

Results
MEM_ID MEM_Status PD_Status
1 94 30
2 94 30
3 94 (null) as there is no PD
4 94 99

Any help would appreciated.

Idd

 

[lbass]

Use a left join from MEM to PD and then insert a group {MEM.MEM_ID}. Then if the minimum PD_Status is 30, then insert a minimum on PD_Status, drag MEM_Status from the detail section into the group header, along with the PD Status minimum and then suppress the details and group footer sections. You would have to have a record selection formula of MEM.MEM_Status = 94.

-LB

 

[idd]

LBASS,

Thank you for your help, I will try this on Monday when I get back to work.

However being a total Newbie at Crystal (less than 2 months)

I do not yet know how to do insert a mimimum on PD_Status.

For the record though I forgot to mention that I do have a left join, from Mems to PD.

By coincidence the 30 status in PD is the lowest one, so minimum would work in this case.

and in the first post above by me I mentioned

Basically if the member has a PD_Status of 22 then show that record if not then show any other record but only one record.

The number is wrong and it should be the same as the number given in the previous paragraph of the same post. Just to save any confusion, this should have been 30

Idd

 

[lbass]

To insert a summary, you select the field in the detail section that you want to summarize, right click on it, and select insert->summary->select the type of summary.

-LB

 

[idd]

LBASS

Thank you for your guidance.

I have created a summary minimum for the PD_Status and and placed this in the group header section as well the pd_status field.

It still is counting all the records and displaying them all.

as an example What I get is the following

Status 30
Mem_ID Mem_Name PD_Status
111 john 30
222 bill 30

status 94
333 jeremy 94
444 michael 94

status 95
111 john 95
111 john 95
666 tom 95

Any help would be appreciated.

Idd

 

[lbass]

Your group should be on MEM_ID, NOT on status. Adding a group on status forces the appearance of the IDs for each status. Please identify your fields using the convention {table.field}. I cannot tell whether you have used a record selection formula of:

MEM.MEM_Status = 94

-LB

 

[idd]

LBASS thanks again for the response.

I tried the grouping by MEM_ID however I need the results grouped by the PD.PD_STATUS


I probably wasn't clear enough in my description.

I need the results to be grouped by PD_Status though.

so it would be something along the lines of

PD.PD_Status = 30
Mem.Mem_ID Mem.Mem_Name PD.PD_Status
111 John 30
112 Bill 30
113 Bob 30

PD.PD_Status = 94
115 Ben 94
111 John 94

PD.PD_Status = 95
118 Ken 95
119 Geoff 95

That is how the result come out now, I need it to NOT show the record for
mem_ID 111 in
PD_Status 94

as it has already showed a record for that member in pd.PD_status 30.

The rule is that if a member has a PD in pd.PD_status 30-current then it should not show a record for any other type of PD.PD_Status.

Idd

 

[lbass]

Then you will need to use a command so that only one record is returned per MEM_ID. Try creating a command like the following:

Select `PD`.`MEM_ID`, min(`PD`.`PD_Status`) as minpdstat
From `PD`
Group by `PD`.`MEM_ID`

Link the command to the PD table with a left outer join from the PD table to the command on both the MEM_ID fields and the PD status fields. In the record selection formula use:

(
isnull({PD.PD_Status}) or
{PD.PD_Status} = {command.minpdstat}
)

-LB

 

[idd]

LBASS

Thanks that worked a treat.

-----------------------------------------------------
Your vote for lbass for TipMaster of the Week has been submitted.

Thank you for letting lbass know this post was helpful.
------------------------------------------------------

Idd