Query SUM total not correct with more than two tables

[tdoolittle]

Not sure if I'm simply just not supposed to be doing this, or if I'm doing something wrong on my side.

I have three tables:

tbl_FBA
tbl_Cases
tbl_Tracking

Tables "Cases" and "Tracking" are both linked to "FBA", with "FBA" being on the one side and the others being on the many side. Each FBA number can have multiple cases and multiple tracking numbers.

I'm creating a query with all three tables, looking at all FBA numbers, most recent case notes attached and trying to look at a SUM of the boxes we shipped with it.

Example:

FBA123 has two cases, one from 5/13 saying "Needs updated" and one from 5/20 saying "Now updated". It also has two tracking numbers, the first tracking number included 5 boxes, the second included 4. Therefore my query should show: "FBA123 / Now Updated / 9 boxes".

Now everything is working fine for the most part, and for most of my information, but the SUM of the boxes goes crazy for some of the information.



Looking at it, probably more than half of the results I'm getting are correct with a correct box count. But some information is clearly off. One shipment I show actually shipped with 55 boxes, yet my sum shows 110. I thought it was somehow simply doubling the number but then I saw a shipment showed a SUM of 24 boxes that only shipped with 6.

As soon as I remove the tbl_Cases from my query, everything is correct. Is this just a problem with multiple tables? I cant imagine why most of my information would come out correct while the incorrect information has no rhyme or reason to it? Not sure what I can load to be of help, hoping its just a simply mistake or setting I'm not ticking to fix this!

[June7]

That is to be expected when multiple tables with 'many' relationship are included in query. Options:

Queries summarize each 'many' table individually then join those queries.

Build report/subreport arrangement.