Join tables with two keys

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[IanCockcroft]

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
Customernr	Subcategory	Subcategory (Groups)	Revenue in E	composite key
1	Blue Paint	Paint	€ 40	1 Paint
1	Red Paint	Paint	€ 50	1 Paint
2	Planks	Wood	€ 170	2 Wood
Contract
Customernr	Category	Amount in eur	composite key
1	Paint	€ 100	1 Paint
1	Metal	€ 250	1 Metal
1	Wood	€ 200	1 Wood
2	Paint	€ 500	2 Paint
2	Metal	€ 250	2 Metal
2	Wood	€ 200	2 Wood

[Anonymous]

@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