| Author |
Message |
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
   Topic: How to get rid of the duplication data?
Posted: 11 Sep 2008 at 9:46pm |
Hi there~
Im facing a problem on displaying my reports data accurately due to the duplication of data. Im creating a report whereby I need to compare Purchase Requisition data and also Purchase Orde Data. Below are my report output and also what I wish to get.
My current report output:
Req. No. Desc. Qty Req. Po No. Qty PO
001 Eraser 10 PO-01 12
001 Eraser 10 PO-01 20
001 Paper 30 PO-01 12
001 Paper 30 PO-01 20
002 Pencil 40 PO-02 40
002 Pencil 40 PO-02 20
002 Pen 20 PO-02 40
002 Pen 20 PO-02 20
Desired Output:
Req. No. Desc. Qty Req. Po No. Qty PO
001 Paper 30 PO-01 20
002 Pencil 40 PO-02 40
002 Pen 20 PO-02 20
So can anyone guide me on how or what I should do in order to get the desire output?
Thanks in advance~ 
|
|
3Lish@
|
 IP Logged |
|
|
sign_seventh
Newbie
Joined: 14 Aug 2008
Location: Philippines
Online Status: Offline
Posts: 11
|
Posted: 11 Sep 2008 at 10:57pm |
your records duplicate bcoz of different value in qty po.
i think changing your query would be great. if you plan in getting 1 entry only disregarding what their qty po.
try this
select tbl1.reqno,(select top 1 qtypo from tblname where reqno=tbl1.reqno)
from tblname tbl1
group by reqno HAVING COUNT(*) > 1
|
|
IP Logged |
|
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
Posted: 23 Sep 2008 at 2:15am |
|
Im not using sql query to do. So, if using your query, where should i put this query to?
|
|
3Lish@
|
|
IP Logged |
|
Krishna Kumar
Newbie
Joined: 21 Jan 2008
Location: India
Online Status: Offline
Posts: 10
|
Posted: 23 Sep 2008 at 3:00am |
Hi,
Introduce a group on the report for Description and place all teh fields in the Group Footer. Also remember to sort the desierd field (EG: Q PO) in the desiered order.
Krishna
|
|
Thanks and regards
Krishna
|
|
IP Logged |
|
Yume
Newbie
Joined: 29 Nov 2007
Location: United Kingdom
Online Status: Offline
Posts: 11
|
Posted: 29 Sep 2008 at 5:55am |
Are you using more than one table, if so it could be the linking of the tables that's causing it
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 29 Sep 2008 at 1:23pm |
It looks like this is a table linking problem. You're getting quantities for all PO numbers under each PO number.
What is your table structure and how are you currently linking them? Also, as a general rule of thumb - SmartLinking isn't smart. It causes problems because it links based on matching field names. I always turn it off and do my own linking.
-Dell
|
|
|
|
IP Logged |
|
gavinkil
Newbie
Joined: 01 Oct 2008
Online Status: Offline
Posts: 3
|
Posted: 01 Oct 2008 at 1:38am |
|
I have a similar problem
The basic problem is we generate reports from SAP over 6 month time
periods. In this period people may have left and been reappointed and therefore come up twice in the output
list with different figures for each time spent employed.
Was just wondering if there is a way in Crystal to only produce one line for these people with combined figures.
Therefore instead of
Pers # Name Abs Spells Days
10001013 Stewart 1 2 3
10001013 Stewart 2 3 1
It reads
Pers # Name Abs Spells Days
10001013 Stewart 3 5 4
This is currently been taking into Crystal via Excel and only using one table to do so.
|
|
IP Logged |
|
Yume
Newbie
Joined: 29 Nov 2007
Location: United Kingdom
Online Status: Offline
Posts: 11
|
Posted: 01 Oct 2008 at 1:46am |
|
You could sort the by the Pers # first and group by Pers #, inserting a sum in that group to get the total
|
|
IP Logged |
|
gavinkil
Newbie
Joined: 01 Oct 2008
Online Status: Offline
Posts: 3
|
Posted: 01 Oct 2008 at 2:17am |
|
If I sort by Pers# then group by Pers# and sum the Pers# it will just count the amount of personnel numbers.
Will I have to make the fields I want to total into groups and therefore change the template to display only totals?
|
|
IP Logged |
|
Yume
Newbie
Joined: 29 Nov 2007
Location: United Kingdom
Online Status: Offline
Posts: 11
|
Posted: 01 Oct 2008 at 2:27am |
|
Insert a summary against the Abs etc showing the sum in the group section. Right Click on the fields you want the summary for and insert in the group line
|
|
IP Logged |
|
|
|
