Problems with Summaries and Relationships (?) on a different table

[n7mafia]

I'm not even sure to describe under which category my specific problems fall into, I can only advance some hypothesys, so I'll just describe it and let the wisdom of others guide me.

I have a DB with several tables, I use them to make invoices. With every invoice I input the desired price and it adds automatically 19% taxes and produces a neat invoice which I can print, these three tables are called:

Invoices 2018
Invoices 2019
Invoices 2020

Now on to my problem. I wanted to create a forth table where from every previously listed table it would calculate the total revenue of the year in question, broke down like this:

- Total Net price paid for 2018
- Taxes for 2018
- Total revenue for 2018 (net+taxes)

- Total Net price paid for 2019
- Taxes for 2019
- Total revenue for 2019 (net+taxes)

- Total Net price paid for 2020
- Taxes for 2020
- Total revenue for 2020 (net+taxes)

 

I am unsure how to proceed, I tried creating a forth layout and a new table but I have problems brining the summary field from each year's table into the forth table/report/layout.

Edited December 5, 2020 by Mafia2020

[comment]

I would suggest you consolidate your three invoice tables into a single table with a field for year. Then it's a simple matter of sorting the invoices by year and showing them in a layout with a sub-summary part by year (and no body part). To show the stats you want place these three fields inside the sub-summary part:

• a summary field totaling the invoice net mount;
• a summary field totaling the invoice tax amount;
• a summary field totaling the invoice total amount - or a calculation field adding the other two sub-totals.

---
P.S. Please update your profile to reflect your version and OS so we know what you can use.
 

Edited December 5, 2020 by comment

[n7mafia]

I consolidated the three tables into one as you suggested, I can now get a total revenue/tax/etc for all three years which is fine, how can I get, though, revenues broken down by year? Perhaps with a calculation field where I specify to take into account (sort?) records year by year? Should I use GetSummary or Sum?

I tried to add an InvoiceYear field and a Summary field which is a sum of total of each invoices which produces some sort of lifetime revenues; then I created a Calculation field with : GetSummary ( sLifetimeRevenue; InvoiceYear) but it doesn't seem to work.

Edited December 6, 2020 by Mafia2020

[comment]

As I said: if you put the summary fields in the sub-summary part and sort the records, you will see the subtotal for each sorted group.