Hi everyone,
I'm having trouble to correctly link two tables. Let's call them Revenue and Forecast. Below you can find a
Both Revenue and Forecast are linked via the date with a 1 to many relationship with a table Calendar.
As the Revenue table has multiple transactions for the same client and the same kind on the same day, it's not possible to merge the queries (because this way I get duplicates of the Forecast table, resulting in a too high Forecast). If I try to solve it with relationship between tables, I have a many-to-many relationships. Neither way I succeed in getting the right result. At the moment the only solution I see is making a pivot table of the Revenue table but as I already have many things running with this table, I would prefer to avoid this.
Revenue table:
| Date | Client | Revenue | transaction kind |
| 01/01/2021 | A | 30 | mail |
| 01/01/2021 | A | 30 | Parcels |
| 01/01/2021 | A | 60 | Mail |
| 01/01/2021 | B | 400 | Parcels |
| 01/01/2021 | B | 200 | Parcels |
| 01/01/2021 | C | 30 | mail |
| 02/01/2021 | A | 30 | mail |
| 02/01/2021 | A | 60 | parcels |
| 02/01/2021 | B | 400 | Parcels |
| 02/01/2021 | C | 200 | Parcels |
Forecast
| Date | Client | Forecast | Transaction kind |
| 01/01/2021 | A | 100 | Parcels |
| 01/01/2021 | B | 500 | Parcels |
| 01/01/2021 | C | 2500 | Parcels |
| 02/01/2021 | A | 110 | Parcels |
| 02/01/2021 | B | 550 | Parcels |
| 02/01/2021 | C | 2000 | Parcels |
| 01/01/2021 | A | 10 | Mail |
| 01/01/2021 | C | 100 | Mail |
| 02/01/2021 | A | 50 | Mail |
| 02/01/2021 | C | 1000 | Mail |
