Query not summing correctly when the two tables have different count of ids

[is800025]

Hi,

I have a query that tries to find the difference between 2 tables

Table1
Deal1 100
Deal1 200

Table2
Deal1 300

The query doesn't return 600 in 1 row for Deal1. It shows Deal1 twice and displays in column for Table2 300 twice. I have done summing and grouping / place distinct in front of Deal ID in the SQL. I thought by not displaying the two columns, but just result column would resolve the issue. But the problem remains. The join is 1 ( where the match of id is equal for both table.

Thank you in advance.

 

[alansidman]

To be sure I understand, in the current example the difference between the two tables for Deal 1 should be zero? Is that the answer you wish? If not please clarify.

 

[is800025]

Alan,

I meant summing. Not the difference. Thanks.

 

[alansidman]

Ok. No issues.

If I were to do this, then I would follow the following steps.

Create an aggregate query for each table, summing on the amounts and grouping on the Deal field.

Now create a new query joining the two queries on the deals as your recordsets and create a new field and input an expression in the new field to sum the two amounts from the two queries.

 

[is800025]

Thanks. That works.