Can't get proper total

[alwyslpy]

Hello. I think I'm building my access database in a way that is intuitively wrong. This first picture is what I'm trying to get my report to display as. But what I'm getting is this second picture. Essentially, the data should be, Contract Number, CLIN, with a total "Committed" for the combination of contract number & CLIN. The committed represents how much that portion of the contract is worth. Secondly, there is an ACRN and "obligated". The obligated shows how much money we have put on the contract to cover the committed.

When I enter data, I want to be able to show how much has been committed on that single transaction, and how much has been obligated via each ACRN i enter. The problem i'm finding is that each "ACRN-Obligation" record that is created, creates a duplicate "committed" entry making my committed SUM wrong. Its fine in the table. But when I create a query/report, it counts each of those duplications mentioned above. Appreciate any ideas/solutions!!! View attachment 103373Want this--showing proper totals

Get this---Committed is showing up over and over.

 

[AlexN]

Not an expert, but this usually happens when there's a multi-value lookup field somewhere in the tables involved. Is that the case?

 

[The_Doc_Man]

If this occurs in a query, please open the query in design mode. Switch to SQL view. Copy and paste the actual text of the SQL in a follow-up post.

When you get doubling or higher multiples of records, it is likely that you have built an improperly constrained JOIN. In fact, this multiplication of records is quite common in something called a Cartesian JOIN (a.k.a. permutation JOIN).