Sum fields to hide zero values

[Sparkwoodand21]

Hi,



I have a query that appears to be producing correct results albeit with a number of records being zero in total. I want to hide all records that are zero.

For instance:

Each record is made up of 35 fields - 27 of which are numeric fields- they can contain positive numbers, negative numbers and zero's (null values have been replaced with zero). When I report from this query I only want to display records which have an 'absolute' value when looking across all the numeric fields. Thus if I have a record with two fields, one containing -10 and another with 10 I want the record to display even though the total is zero (absolute total is 20).

To solve this I added the following expression to the query:

Displaycheck: Sum(Abs([jan2014])+Abs([feb2014])+Abs([mar2014])+Abs([apr2014])+Abs([may2014])+Abs([jun2014])+Abs([jul2014])+Abs([aug2014])+Abs([sep2014])+Abs([oct2014])+Abs([nov2014])+Abs([dec2014])+Abs([jan2015])+Abs([feb2015])+Abs([mar2015])+Abs([apr2015])+Abs([may2015])+Abs([jun2015])+Abs([jul2015])+Abs([aug2015])+Abs([sep2015])+Abs([oct2015])+Abs([nov2015])+Abs([dec2015])+Abs([2015Rev]))

However the results appear unpredictable - of the 3,200 resulting records - 1,100 records have a value in displaycheck, the rest are blank (null). When checking manually for the correct result only 864 actually meet the criteria of absolute zero above. It is clear that a number of records are showing null in displaycheck when they have values in the other numerical fields. Some of these are a sequence of negative numbers, others are a mix of positive and negative and others are a sequence of positive numbers.

Has anyone experienced this and resolved this issue in the past? I have checked through earlier posts as best I can and couldn't find an answer.

Thanks for your time in reading.

Regards,

John

[pbaldy]

Adding a field with a value to a field with Null with result in Null. You probably want to wrap each field in the Nz() function to force it to be zero.

[ssanfu]

NULL plus any number results in NULL, so it would appear there is at least one NULL in a field for many of the records. Without seeing the data it is hard to give a better answer.

-----------------------------
The main problem I see is that you do not have a normalized table structure. You are "committing spreadsheet", a term that means the table is designed like a spreadsheet instead of a relational database. Spreadsheets are short and wide. Database tables are tall and narrow.

"jan2014", "feb2014", "mar2014" are data and shouldn't be field names. In a normalized table, there would be 2 or 3 fields: "MonthlyAmount" for the numeric data and "Amt_MthYr" with data "jan2014", "feb2014", etc (or two fields "Amt_Mth" and "Amt_Yr" ).

The problem with the current structure is when 2016 rolls around, you will have to redesign the whole dB: tables, queries, forms, reports and quite likely some VBA code.

[Sparkwoodand21]

Thanks for your reply Paul. The only field in the query that returns a null is the displaycheck expression. All other numerical fields in the query have already been converted to zero where the underlying table data could contain null.

Regards,

John

[Sparkwoodand21]

Thanks for your reply Steve. As mention in my reply to Paul, the only field that contains null is the result of the expression displaycheck.

The 35 fields I mentioned are a query result and not a table structure. It is the result of 12 tables being combined. As best I can, the database has been normalised, but from what you are saying I may have not normalised some of the underlying tables. I have multiple result tables (each consisting of many and varied account code and cost centre combinations) for each year that contain values for each month in the year - I need to report on historical years and months, but what I think you saying is that I should have one table with the numerical numbers each referenced by a month/year combination (and then account code and cost centre). That makes sense to me so I'll have a play and see what happens.

Really appreciate you and Paul taking the time to reply.

Regards,

John

[pbaldy]

Can you post the db here, or a representative sample?

[Sparkwoodand21]

Hi Paul,

I can't post the database because some of the data is sensitive. Attached here is the relationship structure of all tables. Hopefully it gives enough information of what I am trying to do.

Regards,

John

[ssanfu]

I don't know how much pain it would be now to change the structure, but anytime you see repeating fields in a table or multiple tables holding the same data, the tables probably are not normalized. How far to normalize table(s) is a balance between ease of entry and good design.

Looking at the relationships, I would look at having tables:

tActuals
-------------------
ActualID (PK)
AccountNumber (FK) (Long)
DeptIDNumber (FK) (Long)
FullAccount (Text)
Amount (Double)
AmtMth (Integer) (example: 1, 2,.....)
AmtYr (Integer) (2010, 2011, ....)


tBudgets
--------------------
BudgetID (PK)
AccountNumber (FK) (Long)
DeptIDNumber (FK) (Long)
Amount (Double)
AmtYr (Integer) (2010, 2011, ....)
BudgetType (Text) (ex. : Proposed, Adopted, Revised, Amended, ....)


I would probably have BudgetType in another table, in case there might be another entry added in the future. "tBudgets.BudgetType" would then be a Long type.

tBudgetTypes
---------------
BudgetTypes (PK) (Autonumber)
budgetOptions (Text)



My $0.02..........(FWIW)

[Sparkwoodand21]

Hi Steve,

Thanks for your suggestions. Whilst it is of course a nuisance to have to restructure, it will also be fun, as to be honest, this is my first attempt at a database while reading the Access Bible (part way!). So playing with it again will help no end with my understanding. I half expected to be doing that anyway once I knew more. You have given me a lift up on learning. I thought I had done quite well with normalising, as pretty much everything in the original database I took over was listed in one table - as I think you mentioned before, much like a spreadsheet.

One question to help clarify for me - after I have made these changes how will achieve my original goal? - that of removing 'absolute zero's' across each record in the particular report (or underlying query).

I set up my queries initially to contain all records in tAccountCode and tDeptID (or tFullAccountList which gets the same result) to make sure I have all possible combinations and then add data from other tables where data exist. Thus I do need to subsequently "hide" or "filter" those records that are all zero - note also: reports may have a different combination of fields depending on the purpose.

Regards,

John

[ssanfu]

I thought I had done quite well with normalising

Yes, pretty good for a first time. I look at some of my first dB attempts and cringe.

I would set the default value property for the Amount field to 0 (zero). When a new record is created, the Amount is automatically 0.


Realize that I know nothing about your dB, the purpose or the process.

One question to help clarify for me - after I have made these changes how will achieve my original goal? - that of removing 'absolute zero's' across each record in the particular report (or underlying query).

If I understand the question correctly, lets say you want to sum all of the months amounts for the year of 2014. You would create a totals query.
For example, see if this makes sense:

Displaycheck: SELECT Sum(Abs(Amount)) AS Displaycheck
FROM tActuals
WHERE (((tActuals.AmtMth) Between 1 And 12) AND ((tActuals.AmtYear)=2014))
HAVING (((Sum(Abs(Amount)))<>0));