Return only one occurrence into a table report from multiple occurrences in a table.

Question

I have a table that has, for simplicity, a client name, related agency, related product and price.
One client can have many related agencies, and the agencies may have 1 to 5 products (all 5 are the same) . I want to create a table report that basically ignores the agency but brings back only one occurrence of the client/product/price combination. example:
client-a agency-a prod-a price 5.00
client-a agency-b prod-a price 5.00
client -b agency-c prod-a price 8.00

I would like the table report to show only one client-product-price combination. Reason is each product has a related price, but the price is set at the client level so I really don't care about the agency. I want the report to tell me for each client, show me one occurrence of the product and related price.
client-a prod-a price 5.00
client-b prod-a price 8.00

How can I accomplish this? Thank you

------------------------------
Barbara Alvarez
------------------------------

stevenshweky · Answer

There are more complicated solutions which involve Formula Queries, but have you tried making a Summary Report?------------------------------Steven Shweky------------------------------

barbaraalvarez1 · Answer

I didn't think of that!&nbsp; The summary report gives me exactly the results what I want, BUT, it is only allowing be to include&nbsp; 5 'then by'.&nbsp; I need to include about 4 more more 'Then By' in my Group By.&nbsp; &nbsp; &nbsp; Possible?&nbsp;------------------------------Barbara Alvarez------------------------------

stevenshweky · Answer

There are more complicated solutions using formula queries but have you considered using a simple summary report?------------------------------Steven Shweky------------------------------

markshnier__you · Answer

Ah right, the limit is 6 group bys.&nbsp; The only other option is to make a few concatenated fields to join two or more text fields together.&nbsp; Would that work?------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

barbaraalvarez1 · Answer

I could create more than one summary report to include the group by(s) that I'm missing.&nbsp; &nbsp; &nbsp;The end results of this report(s)&nbsp; -&nbsp; I will export to a spreadsheet and use as data to populate another table.&nbsp; So I think this will solve the problem.&nbsp; &nbsp; You can still provide other suggestions!&nbsp; &nbsp; Thank you------------------------------Barbara Alvarez------------------------------

Forum Discussion

Return only one occurrence into a table report from multiple occurrences in a table.