Subreport Based on a Query returns duplicate data

  • Thread starter Thread starter dtlabo
  • Start date Start date
D

dtlabo

Hello, and Thank You in advance for your help.

I have a report that is to be used as a packing slip for producted we
ship out. The report is made up of a main report that displays order
information (Ship To Address, Customers PO Number, Pick Tick Number
etc.), including a field called ShipmentID the Shipment ID is an number
that is generated by the system for each shipment. A shipment could be
one Pick Ticket or a consolidation of many Pick Tickets going to the
same customer. If it is a consolidated order the Shipment ID is the
same for all Pick Ticket in the consolidation.
There is a subreport that shows line level detail for each Pick Ticket
(Part Number, Description, Qty Ordered, Qty Picked etc.) The sub report
is controlled by a query that pulls data from two seperate tables. The
query parameter is ShipmentID. For the Shipment ID column I have the
following in the query criteria;

[Report]![rptPackingSlip]![ShipmentID]

When I run the query or the subreport by itself it returns the correct
number of Pick Tickets and line. However when I run the subreport as
part of the main report the subreport duplicates each Pick Ticket and
Line details.
I have search and search for an answer or resolution for this problem
but can't seem to find it. Please help!

Thanks again!
Del
 
Open the report query in desing view, now open the properties window and set
the unique values to yes, this should remove any duplication. The only
potential problem may be if you have infact duplicate items on the same
shipment. Review the data and verify that this is now correct.
Hope this helps.
Fons
 
I've set the Unique Value to Yes and the subreport still returns
duplicate data when run with in the main report.
Del

Fons said:
Open the report query in desing view, now open the properties window and set
the unique values to yes, this should remove any duplication. The only
potential problem may be if you have infact duplicate items on the same
shipment. Review the data and verify that this is now correct.
Hope this helps.
Fons

[email protected] said:
Hello, and Thank You in advance for your help.

I have a report that is to be used as a packing slip for producted we
ship out. The report is made up of a main report that displays order
information (Ship To Address, Customers PO Number, Pick Tick Number
etc.), including a field called ShipmentID the Shipment ID is an number
that is generated by the system for each shipment. A shipment could be
one Pick Ticket or a consolidation of many Pick Tickets going to the
same customer. If it is a consolidated order the Shipment ID is the
same for all Pick Ticket in the consolidation.
There is a subreport that shows line level detail for each Pick Ticket
(Part Number, Description, Qty Ordered, Qty Picked etc.) The sub report
is controlled by a query that pulls data from two seperate tables. The
query parameter is ShipmentID. For the Shipment ID column I have the
following in the query criteria;

[Report]![rptPackingSlip]![ShipmentID]

When I run the query or the subreport by itself it returns the correct
number of Pick Tickets and line. However when I run the subreport as
part of the main report the subreport duplicates each Pick Ticket and
Line details.
I have search and search for an answer or resolution for this problem
but can't seem to find it. Please help!

Thanks again!
Del
Click to expand...
Click to expand...
 
Hi Del.
Is the main report set up with a grouping by customer, and is the subreport
in the group footer?
You indicate that the subreport is basically printed twice, or is the data
in the subreport doubled?
Does this occur when there is only one pick ticket and also when you have
multiple related pick tickets?
Fons

[email protected] said:
I've set the Unique Value to Yes and the subreport still returns
duplicate data when run with in the main report.
Del

Fons said:
Open the report query in desing view, now open the properties window and set
the unique values to yes, this should remove any duplication. The only
potential problem may be if you have infact duplicate items on the same
shipment. Review the data and verify that this is now correct.
Hope this helps.
Fons

[email protected] said:
Hello, and Thank You in advance for your help.

I have a report that is to be used as a packing slip for producted we
ship out. The report is made up of a main report that displays order
information (Ship To Address, Customers PO Number, Pick Tick Number
etc.), including a field called ShipmentID the Shipment ID is an number
that is generated by the system for each shipment. A shipment could be
one Pick Ticket or a consolidation of many Pick Tickets going to the
same customer. If it is a consolidated order the Shipment ID is the
same for all Pick Ticket in the consolidation.
There is a subreport that shows line level detail for each Pick Ticket
(Part Number, Description, Qty Ordered, Qty Picked etc.) The sub report
is controlled by a query that pulls data from two seperate tables. The
query parameter is ShipmentID. For the Shipment ID column I have the
following in the query criteria;

[Report]![rptPackingSlip]![ShipmentID]

When I run the query or the subreport by itself it returns the correct
number of Pick Tickets and line. However when I run the subreport as
part of the main report the subreport duplicates each Pick Ticket and
Line details.
I have search and search for an answer or resolution for this problem
but can't seem to find it. Please help!

Thanks again!
Del
Click to expand...
Click to expand...
Click to expand...
 
Back
Top