Sum IIF Issue

[Ladyeren]

Okay I am ready to scream. I know this is probably a simple fix but I cannot see it. I have a report where I need to sum an item. Even thought I know there are multiples, it only returns a value of 1. It is like it is resetting each time rather than adding all the items with the same name (everything flagged as minor needs to be counted, some items are tested more than once and can have multiple items with minor scores). It is the central grouping so I can't do an over all type thing an the over group gives the same result.

Here is my formula - =Sum(IIf([Error]="Minor",1,0))

As I said the problem is when I have more than one item with a Minor (i.e, I have 3 testA all with Minor errors - it returns a value of 1 rather than a value of 3). I think it has something to do with my true part but googling has not answered what I should put in the formula instead.

Thank you,
Lisa

 

[james_lankford]

what are you grouping by ?

 

[Ladyeren]

the central group is by test which is nested into 2 other groups - main is city, then company, then test. once I fix the central formula I think the others will fall into place.

 

[Ladyeren]

Let me expand a little more. My DB has 5 cities (main group), it is then broken into Companies (nested into the main). Some cities only have one company some have 3. Under each Company they have Tests. In the Tests they have certain types (TestA is one I mentioned earlier). Here is an example - this is one of the smaller ones and more manageable. City is Albequerque. They have 2 companies but we are going to look at CompanyA. Under CompanyA we did 27 tests total (TestA (1), TestB(7), TestC(2), TestD(1), TestE(2), TestF(1), TestG(5), TestH(6), TestI(2)). Out of these Tests there were 5 errors - 1 major, 4 minor (TestC - 1 minor, TestG - 1 major, 1 minor, TestH - 2 minor). Using the formula above it only has that Test H has 1 minor rather than the 2 (which throws off the total for the group making 3 rather than 4).

Does that help clarify?

 

[james_lankford]

helps a little

so it looks sort of like this ?


City table
http://i.imgur.com/hmZFHi0.png


Company table
http://i.imgur.com/vEvsOe4.png


Test table
http://i.imgur.com/V37ovvK.png


ErrorType table
http://i.imgur.com/Cr89XGR.png


query design
http://i.imgur.com/CCqoOzz.png


query results
http://i.imgur.com/CZUzNEs.png


all
Album

if it looks like that that's how I'd do the query and it works

 

[Micron]

Your expression looks OK except maybe that it seems to be based on a field name rather than a control name. That is, I think you are doing this in a report because you mention 'overall' and 'over group' options. For this reason, I also don't see that your problem has to do with a query.

Two things for you to check. Your calculated textbox control has to be in a group header or footer if calculating over a group. The other is that having report controls with the same names as the underlying fields can also cause calculation issues. If you build a form or report using a wizard, you will get this naming convention. I always rename controls if I use a wizard, such as changing Error to txtError. Otherwise, you are expecting Access to figure out if Error is a field or control name.

 

[Ladyeren]

Hi James,

What you have for the Query Results looks very similar to the data set/table I am starting minus a few items that are not pertinent to this situation. The only difference is that it doesn't total the minor that way. it will list every test (because they have multiple items that have specific numbers that are being tested - that is why we can have the multiple tests with minor results - one was done on one item and the same type of test on another item. I probably should have mentioned that I am sorry). So pretty much I am taking that I cannot put this formula where it is supposed to count each minor item straight into the report and theoretically get a correct answer (without a lot of rigamarole)? I should probably do a query that is called in that grouping?

Sorry. Besides for being fairly new at this level of Access usage, I inherited this database in a broken state and am trying to fix as well as augment what it can do. It has been quite a challenge.

Thank you,
Lisa

 

[Ladyeren]

Yes the [Error] is the exact Column Header (Field) in the table where I am pulling the data. In the query that feeds this report I am working on, it has filter options to shorten the amount of data so it doesn't look for Minor Errors on the whole list just for specific items (we also count Major and Critical errors, but the Minor is giving the problem since there are more). I do have the formula in the Header for the central grouping on the report. I do also have something in the Footer (just another formula I was trying) would/could that be causing an issue?

I will see about converting the name to a control name on the report. I may have to give in and do a query that is referenced by the report to total them.

 

[Micron]

When you open this query directly, does the returned set of records look right? If not, your calculation is not the issue. If it looks OK, then it is probably a property setting on the report (group by, sum/count overall, etc.) or the location within the report. Concerrning the location, for example, the header or footer you place the calculated control in must contain the field you are trying to calculate on within that header and footer section. I have had this sort of problem before, usually due to renaming something and forgetting to change the reference in the expression. Sometimes, the width of the control hides the expression, thus the problem.

If you want to upload a stripped down version of your db to a file sharing site, I will take a look at it for you. However, I am still stuck in version 2007, so you would have to create a copy in that version if yours is higher.

 

[mathematician]

Okay I am ready to scream. I know this is probably a simple fix but I cannot see it. I have a report where I need to sum an item. Even thought I know there are multiples, it only returns a value of 1. It is like it is resetting each time rather than adding all the items with the same name (everything flagged as minor needs to be counted, some items are tested more than once and can have multiple items with minor scores). It is the central grouping so I can't do an over all type thing an the over group gives the same result.

Here is my formula - =Sum(IIf([Error]="Minor",1,0))

As I said the problem is when I have more than one item with a Minor (i.e, I have 3 testA all with Minor errors - it returns a value of 1 rather than a value of 3). I think it has something to do with my true part but googling has not answered what I should put in the formula instead.

Thank you,
Lisa

I dont see any problem in your query. The results comes because you have many groupby items/field in your query. Try to use only Error field and your last column with expression you will see what I'm trying to say.