Creating a sorted group by using 3 linked tables

Need help Database
1

Hi,

i need to do a calculation (a sum) detailed per a text value.
i have 3 tables defined as is :
T1-Category:

  • Category Name

T2-Product:

  • Product Name
  • T1

T3-Sales:

  • T2
  • Sale Date
  • Sale Amount
  • Salesman

The desired result is :
retrieve the sum of “Sale Amount” for a salesman for each category, sorted by the sum (descending).

i have some SQL background, as you can see in table design. As you can imagine, i haven’t been able to get that result.

Using @NigelG way of thinking - that i read in a another post - i tried to design the tables another way :
T1-Category:

  • Category Name
  • Products as a List of T2

T2-Product:

  • Product Name
  • Sales as a List of T3

T3-Sales:

  • Sale Date
  • Sale Amount
  • Salesman

but i haven’t figured out either how to get the result.

i’ve tried to play with nested RGs, with “queued” RG (using result from one as data for the other) …

Do you have any hint, clue, solution at hand ?

Thanks a lot,

Best regards,

Guillaume

Multilayered "group by" with aggregated field