Count records based on condition 1

[foru11]

CR 10, SQL Server 2008

I have created a report which group by despatch_note and I have data as below (despatch table):
Despatch_Note Order_Number Order_Line_Number
100553 100364 1
100553 100364 1
100553 100364 2
100553 100364 2
100553 100364 3

There is another table linking to despatch table, named Line_Comments. The key to link both tables are Order_Number and Order_Line_Number. Data as below:

Order_Number Order_Line_Number Line_Comments_Seq Comments
100364 1 1 Test 1
100364 1 4 Test 2
100364 1 5 Test 3

The Line_Comments_Seq is not in sequential because users are allowed to delete the record after they have keyed in the comments. I would have to do a sorting by Line_Comments_Seq.

Here I need to count how many records having the same Order_Number and Order_Line_Number. In the above example, the count will be 3 for Order_Number = 100364 and Order_Line_Number = 1. For Order_Line_Number 2 and 3, the count will be 0.

I need to do this count is because I need to print the 3 comments in the report. If the count = 0, then there will be no comments to be printed. I am planning to reserve few lines to print the comments. This is the only way I can think of.

Appreciate for any help/suggestions provided. Thanks in advanced.

 

[Madawc]

I don't follow your logic: what you give as the correct result does not seem to match the data.

However, you can do a suitable count using a Running Total using a formula: this is one of the options when you use Crystal's automatic totalling. Or you can have an independent formula that chooses 1 if the conditions are met and 0 if not, and then do a summary count on that field.

Whichever method, it is best to display the result while developing the test. Add an extra test detail line if necessary.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

It would help if you displayed the data as it appears in the detail section AFTER linking the tables. Then show the count you expect to see--although I'm not sure you need a count. Are you saying that you only want to show rows where there is a comment?

-LB

 

[foru11]

Madawc: I have thought of using Running Totals but not sure how to set the setting as I need to count based on the Order_Line_Number and Order_Number. Because 1 Order_Number could have many Order_Line_Number.

LB, the data should be something like below:

Details a Order_Line_Number_1 Desc
Details b Order_Line_Number_1 Comment1 "Test1"
Details c Order_Line_Number_1 Comment2 "Test2"
Details d Order_Line_Number_1 Comment3 "Test3"

Details a Order_Line_Number_2 Desc

Details a Order_Line_Number_3 Desc

Yes, I will only want to print the Comments when there is a count according to the Order_Number and Order_Line_Number.

Hope this is helpful for you.

 

[lbass]

I think you are confusing things with this display. Aren't the individual comments in one comments field? If so, they would not print in detail_a to detail_d sections (which are just items from the same row), so I think you are not really referring to separate detail sections. How about showing what the actual data looks like instead of verbiage?

I don't understand why you don't just place the comments field on the report. It will print when there are comments and remain blank when there are not. If you want to suppress rows where there are no comments, then use a formula like this:

isnull({table.comments})

Or if you want to do this in all but the first row per group (order number), then use a suppression formula like this:

isnull({table.comments}) and
{table.order_number} = previous({table.order_number})

-LB

 

[foru11]

Oops, I think I really confusing myself here. LB, you've cleared my mind now. So sorry for asking such stupid question here.

Thanks for the help.