Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Join tables with two keys

Hi Guys,

 

I hope you can help me with the problem I'm facing.

I have two tables, let's say Contract and Revenue.

 

Revenue

| Customernr. | Subcategory | Subcategory (Groups) | Revenue in € |

| 1                   | Blue Paint     | Paint                            | €40                |

| 1                   | Red Paint      | Paint                            | €50               |

| 2                   | Planks           | Wood                          | €170              |

 

 

Contract

| Customernr. | Category | Amount in € |

| 1                   | Paint       | €100             |

| 1                   | Metal      | €250             |

| 1                   | Wood     | €200             |

| 2                   | Paint       | €500             |

| 2                   | Metal      | €250             |

| 2                   | Wood     | €200             |

 

In my Report view I want a matrix table with the customer number and category with the amount in € and the SUM of revenue in € behind it. That way I can check if the customer buys under the contracted amount.

 

The problem I am facing right now is that I cant merge these two tables because I use Groups. Groups aren't visible in the query editor. So I have to solve this problem outside the query editor.

What is the best way to sum the revenue in € based on the customer number and subcategory (groups)?

 

This is what I want:

| Customernr. | Category | Amount in € | Revenue in € |

| 1                   | Paint       | €100             | €90

| 1                   | Metal      | €250             | €0

| 1                   | Wood     | €200             | €0

| 2                   | Paint       | €500             | €0

| 2                   | Metal      | €250             | €0

| 2                   | Wood     | €200             | €170

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I figured it out with the replies on this post.

     

    I could not create a composite key in the query editor, because I have groups.

     

    So I created a new column in both tables that uses the two columns and merges them as one. This is my key.

    Now I could create a relationship between the two tables.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I figured it out with the replies on this post.

     

    I could not create a composite key in the query editor, because I have groups.

     

    So I created a new column in both tables that uses the two columns and merges them as one. This is my key.

    Now I could create a relationship between the two tables.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Please create a column by merging Customernr and Subcategory (Groups) in Revenue table.

    Similarly create one column by merging Customernr and category in contract table and create a relationship between two tables

  • IanCockcroft's avatar
    IanCockcroft
    Post Patron
    6 years ago

    Hi Tim, can you not create a compcite key in the query editor uing the two fields and then join the tables on the composite key?

     

    something like this

    Revenue    
    CustomernrSubcategorySubcategory (Groups)Revenue in Ecomposite key
         
    1Blue PaintPaint€ 401 Paint
    1Red PaintPaint€ 501 Paint
    2PlanksWood€ 1702 Wood
         
    Contract    
    CustomernrCategoryAmount in eur composite key 
    1Paint€ 1001 Paint 
    1Metal€ 2501 Metal 
    1Wood€ 2001 Wood 
    2Paint€ 5002 Paint 
    2Metal€ 2502 Metal 
    2Wood€ 2002 Wood