Calculation in Query giving wrong results

[dargo72]

All,

I have query that I've added an expression field to calculate the total of values in columns.

When I run the query, I get values that are not only not accurate, but not even possible. This is making it hard to troubledshoot the issue.



My formula is:

Total Score: ([BB_Site_Readiness]![BB Integration Step 1 complete]+[BB_Site_Readiness]![BB Integration Step 2 complete]+[BB_Site_Readiness]![BB Integration Step 3 complete]+[BB_Site_Readiness]![Detailed Process Map Reviewed/Approved by site]+[BB_Site_Readiness]![Org Assesment and Additional Resource Req Approved by Site]+[BB_Site_Readiness]![Metrics Defined, Tracked and Results Acceptable]+[BB_Site_Readiness]![Effective QSE-Site Communication])

Most of these fields have values such as 5 or 10, but I'll get totals such as 16 or 13, which by looking at the values in the fields is not possible.

Help?

[JoeM]

Do any of your fields have NULL or Boolean values?

The easiest way to figure out where the problem is to break it down. Start with:
Total Score: [BB_Site_Readiness]![BB Integration Step 1 complete]
and check the results. If that looks good, add in the next field:
Total Score: ([BB_Site_Readiness]![BB Integration Step 1 complete]+[BB_Site_Readiness]![BB Integration Step 2 complete])
and check those results.

Keep on with this process of adding the fields back in to your formula one field at a time, until you come across one that doesn't look right. That is where you want to focus your attention.

[dargo72]

Do any of your fields have NULL or Boolean values?

The easiest way to figure out where the problem is to break it down. Start with:
Total Score: [BB_Site_Readiness]![BB Integration Step 1 complete]
and check the results. If that looks good, add in the next field:
Total Score: ([BB_Site_Readiness]![BB Integration Step 1 complete]+[BB_Site_Readiness]![BB Integration Step 2 complete])
and check those results.

Keep on with this process of adding the fields back in to your formula one field at a time, until you come across one that doesn't look right. That is where you want to focus your attention.

It messes up after:

([BB_Site_Readiness]![BB Integration Step 1 complete]+[BB_Site_Readiness]![BB Integration Step 2 complete])

When I had just:

([BB_Site_Readiness]![BB Integration Step 1 complete])

it was OK.

All fields have values (it defaults to 0). All fields are numbers, etc.

I cannot figure it out, even though it is most likely something very simple.

[JoeM]

What is the value in each of your fields?
What is it returning as the result?

It might be a case where we need to see your database to see what is going on.

[dargo72]

What is the value in each of your fields?
What is it returning as the result?

It might be a case where we need to see your database to see what is going on.

See here for example:

ID	entry date	Numbered step	BB Integration Step 1 complete	BB Integration Step 2 complete	BB Integration Step 3 complete	Detailed Process Map Reviewed/Approved by site	Org Assesment and Additional Resource Req Approved by Site	Metrics Defined, Tracked and Results Acceptable	Effective QSE-Site Communication	Total Score
1	10/23/2012	LN-01-MG-02	0	10	10	10	10	10	10	21
2	10/23/2012	LN-01-MG-03	10	10	10	10	10	10	10	22
3	10/23/2012	LN-01-MG-04	10	10	10	0	0	0	0	10
4	10/23/2012	LN-02-DC-01	10	10	10	3	10	6	3	17

[JoeM]

Is the data coming from a table inside of Access, or is it a linked table?
How are you fields formatted on the table level (what is their Data Type and Format)?

The only other thing I can think of is database corruption (in which case you might want to try a Compact & Repair, or importing your objects into a new database).

I don't think we will be able to do much without having access to your database to look behind the scenes to see what is happening.

If none of that sheds any light on your problem, your best bet would be to remove any sensitive information, and upload a copy of your database for us to see and poke around in behind the scenes.

[dargo72]

Is the data coming from a table inside of Access, or is it a linked table?
How are you fields formatted on the table level (what is their Data Type and Format)?

The only other thing I can think of is database corruption (in which case you might want to try a Compact & Repair, or importing your objects into a new database).

I don't think we will be able to do much without having access to your database to look behind the scenes to see what is happening.

If none of that sheds any light on your problem, your best bet would be to remove any sensitive information, and upload a copy of your database for us to see and poke around in behind the scenes.

I did find that data entry is being perfomed via a combo box. The bound column is #1. However, the value (0, 10, 20) is located in column #2.

I was able to change the bound column to #2 and found that the wierd total scores were coming from the ID Number (primary key). I thought if I changed the bound column from #1 to #2 it would fix the problem, but all it did was change the 10's and 20's to other numbers in the query.....

I tried playing around with the bound column and column width (3 colums, column 1 is the bound column, but its width is 0" so users only see columns 2 and 3)

I know it has something to do with this, but I'm stuck.

[JoeM]

What is the source of the Combo Box? A table or query?
Why have the ID Number be a part of this Combo Box at all? What purpose does it serve?

[dargo72]

What is the source of the Combo Box? A table or query?
Why have the ID Number be a part of this Combo Box at all? What purpose does it serve?

I removed the ID (primary key) field and it appears to have solved the problem.

Thank you,

[dargo72]

Quick Question - How do I make the query ignore nulls and still add up the values in fields that have actual values?

[JoeM]

You can use the NZ function to convert nulls to zeroes so your addition will work, i.e.

Nz([Field1],0) + Nz([Field2],0) + ...

[dargo72]

You can use the NZ function to convert nulls to zeroes so your addition will work, i.e.

Nz([Field1],0) + Nz([Field2],0) + ...

It worked, Thank you.