sql - MS Access duplicate values using SUM -

i'm having trouble writing query in microsoft access 2016 show sum of expense particular event, sum of signs event produced, along year, event description , company name.

i think missing simple, , going feel ridiculous once points out. managed format question enough easy spot!

here tables involved, along dummy data testing with.

all_company           company_event               ------------------    --------------------------- |  id  | company |    | id |  eventdescription  | |------|---------|    |----|--------------------| |  1   | crapple |    | 1  |      concert       | |  2   | rito    |    | 2  |       party        | ------------------    ---------------------------  company_target_actual ---------------------------------------------------------------- |  all_companyid | company_eventid   | year | quarter | signed | |----------------|-------------------|------|---------|--------| |        1       |         2         | 2015 |    1    |    1   | |        1       |         2         | 2015 |    2    |    0   | |        1       |         2         | 2015 |    3    |    3   | |        1       |         2         | 2015 |    4    |    1   | ----------------------------------------------------------------  budget_company_expense --------------------------------------------------------------------------------- | id | all_companyid | company_eventid | year | category | subcategory| expense | --------------------------------------------------------------------------------- | 1  |       1       |        2        | 2015 |   abcd   |    123     |   40    | | 2  |       1       |        2        | 2015 |   abcd   |    cat     |   113   | | 3  |       1       |        2        | 2015 |   abcd   |    dog     |   71    | --------------------------------------------------------------------------------- 

this code query, broke ugly access long lines of code make easier read.

select distinctrow all_company.company, budget_company_expense.year,  budget_company_expense.company_eventid, company_event.eventdescription,  sum(budget_company_expense.expense) [sum of expense usd], sum(company_target_actual.signed) [sum of signed] company_event      inner join ((all_company      inner join company_target_actual      on all_company.[id] = company_target_actual.[all_companyid])  inner join budget_company_expense      on all_company.[id] = budget_company_expense.[all_companyid])      on company_event.[id] = budget_company_expense.[company_eventid] group all_company.company, budget_company_expense.year, budget_company_expense.company_eventid, company_event.eventdescription; 

and here result running query

result ------------------------------------------------------------------------------------------- | company | year | company_eventid | eventdescription | sum of expense usd | sum of signed| ------------------------------------------------------------------------------------------- | crapple | 2015 |        2        |      party       |       $896.00      |       15     | ------------------------------------------------------------------------------------------- 

as can see, summing if total signs (5) happened 3 times (the number of entries in company_target_actual table) , vis versa expense. on issue appreciated, , if forgot information may find mistake please let me know else can provide!

consider splitting query 2 aggregations, 1 sum signed in company_target_actual , other sum expense in business_company_expense. then, join 2 queries company, event, , year grouping factors.

below uses 2 derived tables (subqueries in from/join clause). however, can save either 1 separate query , join them in final query:

select t1.company, t1.year, t1.company_eventid, t1.eventdescription,        t2.[sum of expense usd], t1.[sum of signed]     (select ac.id companyid, ac.company, ca.year, ca.company_eventid, ev.eventdescription,             sum(ca.signed) [sum of signed]     (company_target_actual ca     inner join company_event ev            on ca.company_eventid = ev.id)     inner join all_company ac           on ca.all_companyid = ac.id     group ac.id, ac.company, ca.year, ca.company_eventid, ev.eventdescription) t1  inner join     (select ac.id companyid, ac.company, be.year, be.company_eventid, ev.eventdescription,             sum(be.expense) [sum of expense usd]     (budget_company_expense     inner join company_event ev            on be.company_eventid = ev.id)     inner join all_company ac           on be.all_companyid = ac.id     group ac.id, ac.company, be.year, be.company_eventid, ev.eventdescription) t2  on t1.companyid = t2.companyid  , t1.company_eventid = t2.company_eventid , t1.year = t2.year