Duplicate Data in Report

  • Thread starter Thread starter Alex123
  • Start date Start date
A

Alex123

Hello,

I'm running a report based on a query that is based on three tables, PO,
list of products in stock, SO.
The three tables are related by product number.
I wanted to see stock status, any open PO and any open SO by product in stock.
So I created a query as such PO <- Prod in stk -> SO

I run the corresponding report, group it by product and this is what I get:

Prod1
PO1 SO1
PO1 SO2
PO2 SO1
PO2 SO2

So I get double data. Iif I consolidate by PO I get the following:
Prod1
PO1
SO1
SO2
PO2
SO1
SO2

--> I don't want the SO data to repeat itself. This is what I'm looking for:
Prod1
PO1 SO1
PO2 SO2
or
Prod1
PO1
SO1
SO2
PO2

Can you let me know how I can do this ?

Thanks,
Alex
 
Alex,

From the way it looks, you are trying to list data of fundamentally
different natures and relationships in groups in reports. IMHO this is very
unusual and will require a very complicate unusual solution. Are you sure
you don't want to go the more typical route....2 reports: Products with PO's
under them, and then products with SO's under them?
 
Hello Fred,

This is exactly what we're trying to walk away from. The goal was to combine
the two reports you describe into one so our mgmt can see the information
quickly.

Even though the data PO and SO are in two unrelated tables, listing them
next to each other would not be contradictory from a business standpoint.

If you know how to do this, I'll be more than happy to know.

Thanks,
Alex
 
Alex123 said:
Hello Fred,

This is exactly what we're trying to walk away from. The goal was to combine
the two reports you describe into one so our mgmt can see the information
quickly.

Even though the data PO and SO are in two unrelated tables, listing them
next to each other would not be contradictory from a business standpoint.

If you know how to do this, I'll be more than happy to know.

Thanks,
Alex

"Fred" wrote:

> Alex,
>
> From the way it looks, you are trying to list data of fundamentally
> different natures and relationships in groups in reports. IMHO this is very
> unusual and will require a very complicate unusual solution. Are you sure
> you don't want to go the more typical route....2 reports: Products with PO's
> under them, and then products with SO's under them?
Click to expand...

Alex123,

I can't say that I fully understand your situation. I tend to agree with Fred that 2 reports showing explicit information may be more meaningful. However, if you really are looking for information combined from 2 unrelated tables, you may want to look at a UNION query.

SELECT... stock info, product from PO where ..some condition
UNION SELECT stockinfo, product from SO where ...same condition...

I'm not sure if this is helpful, but it would allow unrelated tables to be queried... Only you can decide the relevance.

Good luck
 
have you considered the use of subreports? That is one way of combining two
sets of data with one relationship -Products.

Damon
 
Back
Top