Need to supress dups in from subreport

[arachnoid]

I have a subreport that is linked to my primary report by customer number.

My issue is that I only want to see one occurance of a record from my subreport, but due to the fact that the customer number may show up more than once on the primary report, I am getting the same record from the subreport multiple times.

I have tried every method of supressing dups that I know of. Does anyone have any suggestions to prevent this from occuring?

Thanks in advance.

 

[FVTrainer]

It sounds like you are linking by customer number but not grouping by customer number. How is your main report grouped and where are you placing the subreport in the main report?

 

[Madawc]

As FVTrainer says, you could group by customer number in your main report. Or else put it in the header or footer and suppress the subreport detail lines.

Rereading, maybe that's not what you mean. If it's the same customer on several different accounts, then that is a problem. You could maybe use 'distinct count' to total. Or do an subreport in the report footer than tots up customers by accounts matching the main-report criteria.

Madawc Williams
East Anglia, Great Britain

 

[arachnoid]

FVTrainer is correct.

In the primary report, I am grouped by order number and in the subreport, I am group by customer number.

I am placing the subreport in group footer b of the primary report.

I am suppressing the subreport detail lines, performing a select distinct records, suppressing if duplicated at the field level and group level...

At this point, I am thinking that Madawc's suggestion of placing the subreport in the report footer will be my best bet.

Thanks for your reply's, any additional suggestions will be greatly appreciated.

Thanks

 

[FVTrainer]

I'm not saying your report design in wrong, but it is unusual. A linked subreport functions much like a correlated subquery; that is, if the report appears in a group footer, it is (usually) linked, at least in part, on the field in the main report on which you are grouping.

I guess the question would be what is the subreport's purpose? If you are linking by Customer Number, what info are you attempting to show that you can't otherwise get from within the main report? And why does linking by Order Number not serve your purpose?

 

[arachnoid]

"what is the subreport's purpose?"

- To summarize back orders at the customer level.

"what info are you attempting to show that you can't otherwise get from within the main report?"

- I could pull in the back order info in the primary; however, it would be at the order level and not the customer level.

"why does linking by Order Number not serve your purpose?"

- Because I am looking for the data at two different levels of detail.

Thank you for all of your help.

 

[FVTrainer]

Given your answers, I don't see how placing the subreport in an Order ID Group makes logical sense. If your subreport's purpose is to show back orders by customer, why would you look at it on an order by order basis? Are you trying to show which customers are backordered on individual items contained in the specific order? For example, if Order Id 1 contains Product 1, Product 2, and Product 3, are you trying to show how many customers are backordered on any of those three products? If you're not doing something similar to that, then I can't see the logic in placing the report in the group footer of the Order ID group. I'd agree with Madawc that the report footer is the best place for your report.