Tips and Tricks: Get Count from another table

Get Count from another table

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14951
Printed Date: 06 May 2024 at 3:16pm

Topic: Get Count from another table

Posted By: cdmord
Subject: Get Count from another table
Date Posted: 15 Nov 2011 at 7:17pm

Hello,

I am new to crystal reports (actually thrown in deep end). I need to get the number of records from a table that matches the value of another table, for example.

Table1->OrderNumber Table2->OrderNumber

Table2->OrderNumber

I need to print the total, in this case 4, in the detail section. What is happening is this field is counting all detail lines and printing details records Table1->OrderNumber four times. I am guesing because Table2->Ordernumber is listed four times.

I would truly appreciate someones guidance and I am willing to pay for it.

Replies:

Posted By: lockwelle
Date Posted: 22 Nov 2011 at 3:34am

since the tables are linked, and table2 has 4 lines, then it would print the value from table1 4 times. Think of it as SQL select (if you know SQL) and it should help.

So, it you want a count of table2, that's easy, but if you want a count of table1, you want to use DistinctCount. If you are looking for a SUM of table1, for simplicity, I would SUM the value and then DIVIDE by the COUNT, like:

SUM({table.field}, {group})/COUNT({table.field}, {group})

why? since the sum of the table1 is being inflated by 4 times (since there are 4 rows) divide by 4 to remove the inflation.

HTH

Posted By: comatt1
Date Posted: 02 Dec 2011 at 4:51am

could also make a sql expression

(select count(a.ordernum) from table b inner join table a on

a.ordernum=b.ordernum)

Bear in mind, b would be table1 and a is table2