How to handle duplicated value? URGENT HELP

[sommererdbeere]

Hi,

Here is the data:

Cust# Invoice# BookQ InvoiceQ
1234 10099 9000 3000
1234 10100 9000 2000
1234 10101 9000 0

from the above data, BookQ repeated 2 times. From my report, I will have to pull out all the invoice # and InvoiceQ, however, there should be 2 9000 BookQ instead of 3.

my question here is how to eliminate duplicate/repeated value in a field?

how would i go about if i only want to show 2 9000 BookQ instead of 3?

many many thanks to those who help..
this is very urgent if anyone can help..
it will be great..
mm

 

[Turkbear]

Hi,
If the reason you do not want the 3rd BookQ is the 0 value
for the InvoiceQ field, use your selection ceriteria to just return records where InvoiceQ > 0.

 

[synapsevampire]

Your problem is probably in the joins of the tables, you're creating a cartesian product.

Since the data is showing 3 distinct invoices, you'll have to describe what you want to demonstrate and why (called business rules), the data makes sense to me.

-k

 

[sommererdbeere]

in the system file, there are only 2 9000 for BookQ, it got repeated because there are 3 invoices.

i was thinking an if statement, but it doesn't work.

if invoice# = invoice# then
book# = "" else
book#

does anyone have any idea on how to appoarch this?

many many thanks

 

[synapsevampire]

What's a BookQ?

It seems a daunting task, what if they have 3 valid 9000 BookQ's?

In other words, you're getting the wrong data, and if you can't better describe business rules that will know when a BookQ is valid, you're in deep kim chee.

I'd guess that you need a subreport to pull the BookQ, or write custom SQL.

-k

 

[sommererdbeere]

ummm.. let c..

there are alot of repeated/duplicated values in the report. the data that i pull out is corrected. let me give the overall layout first. i pull data from 2 different table.

CustOrd table:

Cust# Line# BookQ
1234 1 9000
1234 1 9000

CustOrdInvoice table:

Cust# Line# Invoice# BookQ InvoiceQ
1234 1 10099 9000 3000
1234 1 10100 9000 2000
1234 1 10101 9000 0

here is the sql code (retrieve data from oracle database):

select
co.cust_no,
co.bookq,
coi.cust_no,
coi.bookq,
coi.invoice_no
from cust_ord co, cust_ord_invoice coi where
co.cust_no= coi.cust_no(+)

from the above 2 tables, the bottom 9000 is placed because of the cust#. how would i go about if i want sth to look like:

Cust# Line# Invoice# BookQ InvoiceQ
1234 1 10099 9000 3000
1234 1 10100 9000 2000
1234 1 10101 0

if i have to use subreport, what i have to do? because i don't know how to use subreport. please be advice.

many many mnay thanks to your help..

 

[sommererdbeere]

sorry..the outcome of the table is like:

Cust# Line# Invoice# BookQ InvoiceQ
1234 1 10099 9000 3000
1234 1 10100 9000 2000
1234 1 10101 0
1234 1 10099 9000 3000
1234 1 10100 9000 2000
1234 1 10101 0
1234 1 10099 9000 3000
1234 1 10100 9000 2000
1234 1 10101 0

as you can tell, it repeated 2 times. this is a very big effect on my report, because the totaling is not correct. do u have any suggestion as to how to appoarch this? also, not just 1 field got affected. it affect 4 of the fields in my report.

it will be great if any of you can show me the steps..
many many many thanks..

 

[lbass]

It sounds like you don't want invoices if invoiceQ = 0 so why not take TurkBear's suggestion and exclude those in your record selection statement?

Next I would try Grouping by Invoice#. Please try those two steps and then report back with a sample of the results.

-LB