Forum Discussion

InspectahJ's avatar
InspectahJ
Regular Visitor
7 years ago

Getting a correct sum when having multiple rows and multiple tables

Hello, I could not find the answer to my inquiry- if it already exists, I would very much appreciate a link.  I am pretty new to this so would appreciate any help I can get.   I have one table of ...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

InspectahJ

I suggest you drop some sample of your tables. It is not clear to me. For example, if the tables have relationships, then why do you want to combine them? Based on which filed they have a relationship? Which kind of a relationship they have? one-to-many? Is there a fact table or lookup table in between? 

InspectahJ's avatar
InspectahJ
Regular Visitor
7 years ago

Anonymous

Thank you for you time!

 

I created a relationship as I thought that would be enough to get the combined cost to work. There is a one to many relationship right now. Not sure what kind of samples I should provide?

 

I will try to make it clearer:

I have two tables. In the first one I have all the order rows from our ERP. On each row there is a ton of data (such as customer, what product, amounts and other). The order number is not unique in this table as an ordernumber can have several rows.

 

The second table has only two columns: ordernumber and cost per ordernumber. In the column ordernumber there are unique values. 

 

I am trying to find a way in which I could get a total cost per order number with the data from these two tables. If possible I would like to be able to perform analysis on the total cost per customer or article  etc. 

 

I have only been doing this for about a month and have an accounting background, so I am sorry if this is a bit blurry from my side...

 

 

    • InspectahJ's avatar
      InspectahJ
      Regular Visitor
      7 years ago

      Ok - here is try:

       

      The relationship: Two tables are called "transaktioner" and "StorexCostsTotal"Relationship between the two tablesTable1: transaktioner. Showing the COGS value - which I would like to add with the value LogCosts from Table 2: StorexCostsTotal (see next screen shot)Table1: transaktioner. part 1Table2: StorexCostsTotal: ordernumber is unique

      • InspectahJ's avatar
        InspectahJ
        Regular Visitor
        7 years ago

        So on a total cost level it works: see below:The total for everything is correctIf I try to divide the data into any categories - it becomes wrong (same amount in every category)