Sum of field1 where field2 is true

[todAnthony]

Field1 contains a number
Field2 contains a checkbox (true/false)

I have a textbox in the report saying (Control Source) =Sum([Field1]) to, of course give me the sum of all of that field.

I am trying to get another textbox to sum the records in Field1 for all the ones in Field2 that are checked (True).

It seems I can do this in the Control Source with an expression but I have no clue how to get there. Something like:

=Sum([Field1]) Like ([Field2]) = True

Am I barking up the right tree?

I hope my information is sufficient.

 

[Beetle]

Use DSum so you can apply criteria;

DSum("Field1", "YourTable", "Field2=True")

 

[todAnthony]

=DSum Worked perfectly.

I knew well and good this could be accomplished with a simple control source but I am super novice at creating these with the expression builder or generally knowing what the function of anything more than your basic commands do.

You help is mucho appreciated..

 

[todAnthony]

If I may bother you once more on this topic:

=DSum("Field1", "YourTable", "Field2=True") works.

What If I wanted to 'filter' it again by Field3 which is also a checkbox.

=DSum("Field1", "YourTable", "Field2=True","Field3=True") is a non working example.

Trying various combos of this and researching it.

Thank you.

 

[Beetle]

Use the And operator;

=DSum("Field1", "YourTable", "Field2=True And Field3=True")

In the above criteria example we are working with boolean fields (check boxes) that will always be either True or False (or possibly Null if it's a triple state check box), so we can write the test values directly into the string. However, lets' suppose that you need to do a comparison to another field or control in your report/form where you don't know what the value will be (like a text or numeric field). In that case you need to step outside the string (i.e. outside the quotes) to grab the value and then concatenate it back into the string. In the following example we are building the criteria string to retrieve values from two text fields in the report. Since they are text fields it's necessary to use extra quotes to delimit the text values that will be retrieved. The extra pairs of double quotes (in red) are the delimiters. You could use a single quote instead of two double quotes, but this will fail if the retrieved value has an apostrope, so I always use the paired double quotes;

=DSum("Field1", "Yourtable", "Field2=""" & [ThisField] & """ And Field3=""" & [ThatField] & """")

If the fields in question are numeric we can leave out the delimiters;

=DSum("Field1", "Yourtable", "Field2=" & [ThisField] & " And Field3=" & [ThatField])

Or, if they are dates we have to use the date delimiter (#)

=DSum("Field1", "Yourtable", "Field2=#" & [ThisField] & "# And Field3 = #" & [ThatField] & "#")


HTH

 

[todAnthony]

Date delimiters, delimiter quotes for text and none for numbers is something I am slightly getting the hang of. Your explanation makes ALOT of sense to me and I will be using it for a long time now.

I knew it would be an AND operator some sort of way but I guess it is a syntax thing I am trying to fully learn. Anyways, thanks a million for this lesson. I will get started on this and post how things go.

 

[todAnthony]

The And in =DSum("Field1", "YourTable", "Field2=True And Field3=True") is suffice for what I am doing but here is my next delima:

It is summing ALL the fields in the "YourTable" where I have a generic report coming from a command on a form (custom switchboard) generating the report from 2 fields with this VBA:

DoCmd.OpenReport "rptSurveyTabs", acViewPreview, , "[projectName]=" & Me.cboProjectName2 & " AND [TargetArea] = " & Me.cboTargetArea

So the cboProjectName2 and cboTargetArea generate in rptSurveyTabs the data for the project and area. Doing =Sum([totOccupants]) gives me the sum of totOccupants according to project and target picked on the form. But when I do =DSum("totOccupants","qrySurveyData","above=True") in a field on the report, it telles me the sum of ALL totOccupants in the qry. I thought the VBA above would be enough to 'filter' the results to just projectName and targetArea.

Now I imaging I could, in a control source, get the totOccupants where above is true and projectName is A and target area is 1 but I would need projectName and targetArea in that string to = whatever was called up to generate the report.

I have a feeling I am making this harder than what it is.

 

[Beetle]

This;

DoCmd.OpenReport "rptSurveyTabs", acViewPreview, , "[projectName]=" & Me.cboProjectName2 & " AND [TargetArea] = " & Me.cboTargetArea

only restricts the records that are returned by the report. It's essentially just a filter. It has no effect on the total recordset that is returned by the reports record source (in this case qrySurvetData). So if you then do a DSum against qrySurveyData you are going to be comparing against all the records that are returned by the query regardless of any filters that may have been applied to the report.

You have a couple of options here;

1) Add additional criteria to the OpenReport method.

[B]DoCmd.OpenReport "rptSurveyTabs", acViewPreview, , "[projectName]=" & Me.cboProjectName2 & " AND [TargetArea] = " & Me.cboTargetArea & " And [SomeOtherField]=True"[/B]

Then just use a standard Sum in the report since the result set is already restricted to records where x=True. Or;

2) If that scenario won't work for you for some reason you can add additonal criteria to your DSum;

[B]=DSum("totOccupants","qrySurveyData","[ProjectName]=" & [ProjectName] & " And [TargetArea]=" & [TargetArea] & " And FieldX=True")[/B]

You'd have to add any necessary delimiters to the above of course.

 

[todAnthony]

I'm going to go with the adding criteria to the DSum. I have quite a few calculations to make in a specific report requiring the 'totOccupants' field to be split a million different ways according to how all kind of other fields are checked True and according to projects and areas in the project....

Has anyone ever gone completely mad doing this?

Thanks again for the direction.

 

[Beetle]

'totOccupants' field to be split a million different ways according to how all kind of other fields are checked True

Not sure what you're saying here exactly, but if you've got a table (or tables) with a lot of boolean fields (i.e. check boxes), then it's possible that you have a structural design problem that is going to continue to cause you headaches now and in the future. A table with a ton of Yes/No fields is , in most cases, poorly designed.

There may be other ways to approach this using functions or calculated query fields, but would need much more detail about your db before I could offer any advice along those lines.

 

[todAnthony]

IMHO I do not believe there are too many boolean fields at maybe a dozen or so. It just happens that the particular report that needs to exist has a boat load of calculations to report from numbers in other fields according how boxes are checked. Actually the entire report consists of =SUM and =DSUM calculations.

BTW to give you an idea of why this is, the entire report has to do with census data and demographics.

Things are working out nicely since you put me straight with this lesson but I am sure I could be structured with more efficiency. It's a lot of development stage and chicken scratch but I'll post it to you later if you would not mind for a professional opinion.

 

[Beetle]

If you want to see a well designed survey database (which should be similar to the design methods one would take with a census database), you can download MVP Duane Hookoms example application called At Your Survey at this link;

http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html

 

[todAnthony]

TY and I will cross this with my work first thing in the morning. Will post results. and Thank you again.