sums too many items, need to sum the distinct values for each field in acres

[Zetheroth]

Firstly, cant figure out why it requests me to put an input in when its directly linked to the combo boxes on the main menu. But the second thing I need to figure out is how to sum the acres for my tons/acre just once for each field, because a field might have 3 inputs but doesnt mean you should count the acres 3 times. You all have always been helpful in learning how to properly format these things, as well as showing how to fix my issues, so I'm looking forward to any input on either issue, as well as the database as a whole.Sales Test.zip

[davegri]

You're getting large horse pasture 3 times because it's in the production table 3 times. It needs to be in one record showing 3 tons.
You're probably getting prompts for input because the Main Form is not loaded (or is loaded in design view) when you run the report manually.

[June7]

Large Horse Pasture is actually in table 4 times, not just 3. Were these cuts all on same date - if so, why not a single entry? Should there be a DateCut field in TBLProduction?

Query and report open just fine when the form is already open. I don't use parameterized queries. I prefer VBA to build filter criteria and apply to form or report. Checkboxes do not have True or False value when form opens. Should not need two Both checkboxes. Put 3 checkboxes (or radio buttons) in an OptionGroup so only one can be selected then use value of OptionGroup to build criteria. Actually, 4 checkboxes or radio buttons - Watered, Fertilized, Both, All (values of 0, 1, 2, 3). Consider:

WHERE (((TBLFields.Watered) LIKE IIf(Forms![Main Menu]!opt1 IN (0,2),True,"*")) And ((TBLFields.Fertilized) LIKE IIf(Forms![Main Menu]!opt1 IN(1,2),True,"*")));

Referencing checkboxes in query is tricky. Suggest you review http://allenbrowne.com/NoYesNo.html

Build aggregate query on TBLProduction then build another query that joins aggregate query to TBLFields. But if you want to see TBLProduction details, then this will not serve and report needs redesign. If you want summation by Field, then why do you have Hay header instead of Field header?

If you want tons per acre, shouldn't you divide acres by sum(tons)? Calculation in Field Name header: ="tons per acre: " & Round([acres]/Sum([tons]),2).
If you really want acres per ton, then reverse the calc.

Don't need = sign in =[acres], just the field reference.

[Zetheroth]

there should and eventually will be a datecut field, if the query worked for both of you that's great, I was having issues even with the main menu open and checkboxes selected, so I'm curious about the other options, i basically need a watered, fertilized, both, and neither but am quite new to access. After a review I believe a combo box might be the simplest solution. There will be 2 reports eventually, one for by the field, and the other for by the hay type. It is most defiantly not displaying acres per ton or that number would be considerably larger, the issue I'm having is even though I have multiple lists of the same field (large horse pasture) I need to only add its acres to the sum one time.

[June7]

See if this revision gets you close.