Multiple types in one field

[mmm76]

Hi,
We have 4 different qualifiers in one field in a table. For example SSNs, HSN, MDR, and MED reference numbers all in one table. I am trying to pull a list of people and their corresponding SSNs but I want to include all people even people with out SSN's. The report is pulling only people with SSNs. I have a left outer join on the table and I really don't want a subreport because I have to export this to access. If I use a formula something like
if CID_ID_Type = "SSN" then CID_ID else "" it still returns a blank line and causes records to duplicate. I already have one grouping that I need to keep based on unique fields and I can not add any additional grouping due to how the data has be reported. Any suggestions would be greatly appreciated. Thanks!

 

[lbass]

You should use a conditional formula as you have shown, and then sort in descending order on this formula. Then in the section expert, format the detail section to suppress using this formula:

{table.person} = previous({table.person})

The SSN will then show, if it exists, and if it doesn't, there will be one record with "".

-LB