Duplicate Data in Report

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

"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?

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

Damon

"Alex123" wrote in message
...
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?

How about creating the main report, which just has your Prod in Stk info.

Then, have two subreports sub_PO and sub_SO, where you place these subs in
the details section of your main report. They should be linked (master/child)
to the main report on the product number field. This would look something
like:

Product 1
PO1
PO2
SO1
SO2

HTH
Dale

Alex123 wrote:
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

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200906/1

The subreports work great.
Thanks Everyone!
Alex


"Dale_Fye via AccessMonster.com" wrote:

How about creating the main report, which just has your Prod in Stk info.

Then, have two subreports sub_PO and sub_SO, where you place these subs in
the details section of your main report. They should be linked (master/child)
to the main report on the product number field. This would look something
like:

Product 1
PO1
PO2
SO1
SO2

HTH
Dale

Alex123 wrote:
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

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200906/1