7 Replies Latest reply on Feb 26, 2017 12:35 PM by philmodjunk

# Summen aus mehreren Tabellen; spreadsheet

Hello,

I have to prepared spreadsheet for project- calculation and now I´m looking to collect all the different projektcost to an yearly overview.

How is the relation between the different tables. Maybe the pictures gives an idea of my challenge.

Thanks

Hans Fredeweß

• ###### 1. Re: Summen aus mehreren Tabellen; spreadsheet

You data model makes reporting more difficult if you want to list each transaction. If you only want one entry for each project with a total project cost and total project income, that would be fairly easy to do with what you have.

How much detail do you want in your "overview"?

• ###### 2. Re: Summen aus mehreren Tabellen; spreadsheet

I have for every project about 25 items for the costs and about 15 items for income. Every item has a categorie, the note and the sum. In the overview it´s similar. For example I want to see, that for advertising we plan in 6 projects the sum around 1.500€, so the total sum is 9.000€. This sum I want to compared with the planed budget, which is may be 8.000€. Thats all!

But you see, not only once the total for all costs.

Hear a picture from a project calculation.

• ###### 3. Re: Summen aus mehreren Tabellen; spreadsheet

Do want to list the project followed by one row for each category with a subtotal for each category?

Or do you want just one total for each category across many projects?

• ###### 4. Re: Summen aus mehreren Tabellen; spreadsheet

Yes, I want to create columns, for every projekt - and rows with each category - like you see in thes Picture

• ###### 5. Re: Summen aus mehreren Tabellen; spreadsheet

I would rethink your diagram, instead of having Kosten_Ein und Kosten_aus, just have Kosten.

Inside the Kosten table you can have a discriminatory field Ein / Aus.

In a report you can sort by this field, so you will have let's say alle Kosten_Ein first, then all the Aus.

You can have fitered portals by this field which will separe the 2 Kosten categories.

For a total sum you can have Real_Cost = Kost * If (Ein; 1; -1)

• ###### 6. Re: Summen aus mehreren Tabellen; spreadsheet

Thanks, I have to think again - it sound logic.

• ###### 7. Re: Summen aus mehreren Tabellen; spreadsheet

The report you describe is often called a "cross tab" report. If you search this site, you can find several different ways to produce such a report.