Record duplication issue

[Former Member]

I am currently having an issue linking two tables in a report. When I generate the report, the records in the detail section become duplicated. To further explain, in one table I am looking for item numbers, transaction date and transaction quantity.

In the next, I am looking for item numbers, transaction dates, and shipment quantities.

In the last I am retrieving item numbers and descriptions.

The only fields that seem to be consistent between tables are the item numbers, so I am joining on that basis.

However, what ends up happening is this:

Item # Date Trans Qty Date Ship Qty

1001 (Grouping)

5/12/09 49000 5/20/09 20000

5/12/09 49000 5/28/09 12000

6/1/09 30000 5/20/09 20000

6/1/09 30000 5/28/09 12000

2001 (Group)

5/12/09 20000 5/5/09 20000

5/12/09 20000 5/19/09 12000

5/12/09 20000 6/5/09 15000

If you know why this is happening, or better yet, a way to fix it, I would greatly appreciate your help.

[Former Member · Accepted solution]

It is happening because the item number appears multiple times in one or more of the "left tables" in the join.

How to fix it depends on what you are trying to accomplish.

If you are trying to match something like Order Details to Shipment Details against those orders, you'll need an order and line number in your Shipment Details file that reference the same fields in the Order Detail file.

If you are just looking for total ordered vs total shipped, you'll need to aggregate the ordered and shipped quantities by item BEFORE doing the join. In SQL it would look something like this (MS SQL):

select isnull(a.item_no, b.item_no) as Item_no, 
   isnull(a.ordered_qty,0) as ordered_qty, 
   isnull(b.shipped_qty, 0) as shipped_qty
from (
    select item_no, sum(ordered_qty) as ordered_qty
    from Order_Details
    where date between {?start date} and {?end date}
    group by item_no
) a
full outer join (
    select item_no, sum(shipped_qty) as shipped_qty
    from Shipment_Details
    where date between {?start date} and {?end date}
    group by item_no
) b
on a.item_no = b.item_no

HTH,

Carl

[Former Member · Accepted solution]

Not to sound like I have no idea what I am doing, but where do I enter this formula. Do I enter it in Report->Selection Formulas? Please let me know.

[Former Member · Accepted solution]

This is an SQL Command data source, so it would be entered in the Database Expert, after removing your existing table selections. Also, what I posted was not a sample SQL statement, so it will have to be revised, which will require knowledge of your database and SQL...

HTH,

Carl

[Former Member · Accepted solution]

I have CRXI, if you click on database, show query,(you should copy the query) it should show the query your report is based on.

If you click on database, set database location, you can create a "New Connection" say you select ODBC

you can then pick your database, select add command, select your original database (top window) select update

, you will be able to put a command in (Paste your query)... Anyway, quick and dirty way to add a command to a report.

Depending on what you want, you may need to add some grouping levels, and just supress the details.

[Former Member · Accepted solution]

I have CRXI, if you click on database, show query,(you should copy the query) it should show the query your report is based on.

If you click on database, set database location, you can create a "New Connection" say you select ODBC

you can then pick your database, select add command, select your original database (top window) select update

, you will be able to put a command in (Paste your query)... Anyway, quick and dirty way to add a command to a report.

Depending on what you want, you may need to add some grouping levels, and just supress the details.

[Former Member]

Hi,

Is it necessary to use tables?u may use a command insted of query.

In query u may use joins and get the desired result.

Regards,

Misra P.