Records display multiple times

[ThaoVy]

Hello,

I have a tblProjects where each Project# is entered once. I also have a tblProjectBusinessTypes where Project# is entered multiple times with different BusinessType i.e.
Consulting, Product Demos, or Training and etc. I am using CR 10 and on the report, under the detail section, it will display Project# multiple times. For example, 1 is for Product Demos and the other would be for Training.

I grouped the report by Project# and placed a formula for Product Demos something like If BusinessType = "Product Demos" Then {tblProjectBusinessTypes.CompletedDate}. Another formulas is If BusinessType = "Training" Then {tblProjectBusinessTypes.CompletedDate}. Since I grouped the report by Project#, it only show me one {tblProjectBusinessTypes.CompletedDate} either for Product Demos or Training. Is there a way I can do where it would show {tblProjectBusinessTypes.CompletedDate} for each BusinessType?

CustName Proj# RecDate Demo Completed Training Date
Thomson West 9447SC 03/02/2007 03/11/2007

If it would show correctly, I should have a Complete Date for Training as well.

I tried to do an sql expression; however, I got an error because of multiple complete date.

Thanks so much for your help in advance

 

[monkeybarfan]

You need another grouping on the Business Type formula

 

[ThaoVy]

If I do antoher groupong on business type formula, I will then have 2 records for the same Project if it has 2 business type:

CustName           Proj#      RecDate         Demo Completed    Training Date
Thomson West    9447SC    03/02/2007         03/11/2007
Thomson West    9447SC    03/02/2007         03/11/2007 04/05/2007

How can I have the report show:
CustName           Proj#      RecDate         Demo Completed    Training Date
Thomson West    9447SC    03/02/2007         03/11/2007 04/05/2007

Thanks so much!

 

[lbass]

Place the fields in the new group header and then insert a maximum on the training date and drag the result into the group header.

-LB

 

[ThaoVy]

Hello,

If I do a maximum training date, it would show only max date. I actually want to show both dates: one for Demo Competed and the other is training date. Right now, it would give me 2 records. I need it to show as one record with dates for Demos and Training:

CustName Proj# RecDate Demo Completed Training Date
Thomson West 9447SC 03/02/2007 03/11/2007 04/05/2007


Thanks so much!

 

[lbass]

If there is only one date, then use your conditional formulas from your first post (if business type = "training" then date), and place them in the detail section and insert maximums on each at the Project group level. Place your fields in the project group header. You don't need the business type group after all.

-LB

 

[ThaoVy]

Thanks so much! It works.