Printing Multiple field values on one row (eliminate duplicate records

[svoss]

In our database, we have numerous requirement codes that apply to each acct. number. I want to show the status of certain requirement codes without duplicate records--I want each acct. number to list only once on only one row. I would like the Req. code to be the header of the column and the status of the requirement to list beneath.
I am pulling the req.code from a separate requirements table, so when I add this field to the report I get 50+ records for each account unless I use the select expert to show me only certain req.code s, which will give me a record for each code I select.

Ex: (Currently)
Acct.# Req. Code Status
1 BOR 4
1 DAT 2
1 FLA 4
2 BOR 1
2 DAT 4
2 FLA 1

Ex: (Desired)
Acct.# Req (BOR) Req (DAT) Req (FLA)
1 4 2 4
2 1 4 1


What is the best way to do this?

I know that a cross tab report would do this for me, however I want to use formulas for the values of the requirements. For ex:
if {TB_CF_APPL.DPSTN_CDE} < 6
and ({TB_CF_REQ.REQ_CDE} = &quot;DAT&quot; and {TB_CF_REQ.STAT_CDE} = 4) and ({TB_CF_REQ.REQ_CDE} = &quot;BOR&quot; and {TB_CF_REQ.STAT_CDE} = 4) then &quot;Stage 1&quot;

 

[synapsevampire]

Group by Acct.

Create 4 formula fields that contain something akin to:

If {MyTable.ReqCode} = &quot;BOR&quot; then
{MyTable.Status}

If you want a default, add an else:

else
0

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Sorry, I overlooked the dupes requirement.

When bringing in your data, select Database->Select Distinct Records.

If this isn't an option, then group on a formula comprised of code+totext(status)

Now place all of your the fields in the header of this new group.

You'll only get one of each.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[svoss]

Thanks for your response.

I did try grouping by acct_nbr and created 3 seperate formulas for each req_cde. I added these formulas to the group header, however the formula only displays the value for the first record listed in the detail section of the group. (I still have 3 records listed in the detail for each group).

I am looking for a more versatile solution, as this is only one portion of a complex report I am trying to produce.

If I can get your suggestion to work, I would probably link it as a subreport to the main report using the acct. number, however I would probably need a few subreports and am worried about response time.

Is there any way to limit the # of records displayed for each account number other than using the select expert? I am currently using the select expert to limit the

I just got your second suggestion....
I did try select distinct records and this didn't work.
I am not sure if I understand your second suggestion-- can you elaborate?

Thanks again for your help!!

 

[synapsevampire]

Create a formula which contains:

code+totext(status)

Now insert a group based on this.

Place the fields in this group header, and suppress the details section, you'll get just one of each row.

So you have 2 groups:

Acct
code+totext(status)

The fields are placed in the latter.

I don't really see any dupes in the list you supplied, but that's one theory if you only want the 1st of each acct/code/status

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[svoss]

Thank you so much for your help.

Here is what I get when I use the code + totext(status) and group on the formula:

Acct # Status
1234 ccr3.00
1234 cpm3.00
1234 dat4.00

1125 ccr4.00
1125 cpm3.00
1125 dat3.00

This is what I would like:

Acct # Status1 Status 2 Status 3
1234 ccr3.00 cpm3.00 dat4.00
1125 ccr4.00 cpm3.00 dat3.00

I want the acct. # to print only once in the report, on one row, with all of the status fields of the requiremnt codes to print horizantally instead of vertically.

I have tried using an if statement with the formula, but when it is grouped, it only prints the first line of the group.