Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: Get Count from another table Post Reply Post New Topic
Author Message
cdmord
Newbie
Newbie


Joined: 15 Nov 2011
Online Status: Offline
Posts: 1
Quote cdmord Replybullet Topic: Get Count from another table
    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
                                                 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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.