Jump to content

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


n7mafia

This topic is 1208 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Mafia2020
Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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 by Mafia2020
Link to comment
Share on other sites

This topic is 1208 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.