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.

5 Replies

Related Content

Multiple reference proxy fields help

Managing record access for multiple data levels

Look for the previuos occurrence in a field and and fill another field with the found occurrence

How do you count the occurrences of specific strings in lists?

Performing Multiplication in Jinja for Pipelines

Recent Discussions

Many to Many too Many...

Selecting which fields to display from Formula URL searches

Bulk create child records from selection list

(Pipelines) Get Users in Role / Process XML from HTTP API

Help Needed with Quickbase Pipeline to Accumulate Values Instead of Overwriting