Removing duplicate records from a report

[DavidAC]

Hi,
i have a problem i can't get my brain around. The DB contains a supplier field and a order field. multiple suppliers can have the same order no and the order no contains multiple records. The report invoice needs to be split between the individual suppliers. i need the report to show only one record per supplier not multiple records. any ideas please help

 

[Andrew Fergus]

Hi

You mentioned "I need the report to show only one record per supplier not multiple records". Did you mean one order per supplier per page? And can one order have multiple line items? Do you want the line items for each order on the same page, where they are part of the same unique order? By unique order, I mean an order unique to a supplier given the same order numbers can be used by multiple suppliers.

Andrew

 

[DavidAC]

hi,
yes it will be one supplier per page. one order can have multiple lines and if they can be on the same page then great.

 

[Andrew Fergus]

Hi
In your report design, click on View -> Grouping and Sorting. First group by supplier (sort ascending, no group header or footer, group on each value, interval 1, together no), then group by order (sort ascending, yes for group header and group footer, group on each value, interval 1, together no) and sort by line item (sort ascending, no group header or footer, group on each value, interval 1, together no).

This will give you an order header or footer in your report in which to put your order heading information (such as the supplier details and order number) and order footer information (total value etc).

Right click a blank area in the order header and click Properties -> Force New Page -> Before Section.

This report should give you an order per supplier per page, with multiple line items, if the order has more than one item.

HTH, Andrew.