Calculated fields not showing totals when one of the items in the expression is a null value

[Dennis9412]

Hi, I'm a total Access newbie with no experience with databases at all. I am a 72 Year old retiree looking for something to occupy my time. Boy did I pick one!!

I am trying to develop a small repair order database for my son who owns a small trucking company. I have developed a bunch of tables and linked them with primary and foreign keys and everything seems to work quite well. I have a Repair Order entry form that has 3 subforms, one for Parts, one for Labor and one for Tires. The main form contains 4 calculated fields, with total costs for each of the three subforms and the fourth is a total of the the three for a grand total. If each calculated field contains data everything works as it should. But if one is blank (ie) no data because there were no parts used, or there were no tires replaced, the grand total field is blank. (The correct answer is there because it appears momentarily, but then gets hidden). This field's formula is "[ =([PartsCost]+[LabourCost]+[TireCost])"] How do I get this field to display the total if one of the three parts is an empty (or null, I guess) field?

Of course, the same thing happens when I try to print a report called a ["Monthly Repair Order Summary"]



I have managed to get the database this far, but can't get meaningful reports out of it yet. Any assistance you can provide would be greatly appreciated.

[isladogs]

Use the Nz function to return zero when you have a null value

=Nz([PartsCost],0)+Nz([LabourCost],0)+Nz([TireCost],0)

[Dennis9412]

Thank You Ridders52, that fixed it up. Will the same thing work in a report?

[June7]

Tables may not be optimally normalized. Why 3 subforms?

Yes, the expression should work in report as well.

If you need more assistance, might want to provide db for analysis - follow instructions at bottom of my post.

[Dennis9412]

Hi June7,

Why 3 subforms? My son said he wants to keep track of the high dollar items.
He is a licenced mechanic and looks after most of the repairs himself because labour rates are high in outside shops, but he does want to track repairs done by a third party.
Tires are his biggest expense at the moment so he wants to track them separately as well.

I'm still trying to figure out what "Normalization" is. As I said in my first post, I have never had any experience with a relational database until now.
I would love to have my database analysed. I can use all the help I can get.

I just thought that this was the easiest way to do it. I am definitely looking for easier ways to do this, so any improvements you can suggest will be implemented.
I will attach the database on my next post.

[Dennis9412]

Hi June7,

Attached is the zip file containing my database for your analysis.

Please note that not all of my repair orders print in the repair order reports, probably because of null values.

I don't know how to fix that. I await your analysis.

Thank you,


Dennis

DoubleD Doug accdb.zip

[Dennis9412]

All of the records in the above database are test records of no value.

Regards,

Dennis

[June7]

Took a quick look and I do think db is reasonably normalized. I now understand use of 3 forms. However, I did expect to see a lookup table for tires.

Recommend you apply the 'no spaces' naming convention for all objects. See spaces snuck into some field names as well - [Tread Type].

Should not repeat drivers name in Discipline table. This is unnecessary duplication of data.

Why does ROParts have both PartsID and PartNum fields? Also, should not have PartsDescription field.

I never build lookups in tables. When I view tables I prefer to see the actual value, not alias. Many developers abhor the Calculated type field but shouldn't be any issues unless you want to someday migrate to another db platform.

[Dennis9412]

Thank you June7 for the quick response.

Sounds like I got lucky with the "Normalization" even though I still don't know what it is.

I will make those changes to the tables as you suggested.

Can you please have a look at the repair order reports and tell me why I'm only getting 2 repair orders in the report when there are in fact six repair orders?

Thank you in advance for any assistance you can give me.

Regards,

Dennis

[June7]

Search web on 'database normalization'.

Change the report's query from INNER to LEFT JOIN:

SELECT RepairOrdersTable.UnitNumber, RepairOrdersTable.RONumber, RepairOrdersTable.RepairDate, RepairOrdersTable.RepairDescription, ROPartsTable.[Quantity]*[UnitPrice] AS PartsCost, ROLabourTable.ServiceProvider, ROLabourTable.[LabourHours]*[LabourRate] AS LabourCost, ROTireTable.Quantity*[TirePrice] AS TireCost
FROM ((RepairOrdersTable LEFT JOIN ROLabourTable ON RepairOrdersTable.RepairID = ROLabourTable.RepairID) LEFT JOIN ROPartsTable ON RepairOrdersTable.RepairID = ROPartsTable.RepairID) LEFT JOIN ROTireTable ON RepairOrdersTable.RepairID = ROTireTable.RepairID;


Also recommend avoiding memo field. I have used them when absolutely necessary but they have issues.

[isladogs]

@June

Also recommend avoiding memo field. I have used them when absolutely necessary but they have issues.

Many developers abhor the Calculated type field but shouldn't be any issues unless you want to someday migrate to another db platform.

Memo fields aren't searchable but otherwise I'm not sure what you mean by 'issues' in Access. Upsizing is possible but a bit tricky
Whereas calculated fields are in my opinion both unnecessary and a major mistake - they can't be upsized as you pointed out

[ssanfu]

Here are things I saw


I removed all look up fields in the tables.
I removed spaces in object names (tables, queries, forms, reports
In the relationship window, I removed all inner joins.
I removed the cascading update and deletes.


Any PK/FK field should not be displayed on a form. I added "_PK" and "_FK" suffixes to the appropriate fields.

Once I added data and fixed existing data, the forms/reports seemed to return the correct data I did have to adjust for object name changes.




Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

===============================================
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp <<<-- Read 3 or 4 times
About calculated table fields - http://allenbrowne.com/casu-14.html

===============================================

Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts
Normalization http://www.utteraccess.com/wiki//Normalization


What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html


Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...ming-part.html
Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...-i-ii-and.html

[Dennis9412]

Wow Steve, Thank you!!

As a beginner fumbling around in nowhere land wondering what I had got myself into,

I was hoping that someone would tell me what I did wrong, and you came along and did just that!!

I really appreciate you taking the time to do what you did in this post.

You have taken my database and fixed all the errors.

Did you by any chance keep a copy of the "new database" after you fixed it up?

If you did, I would appreciate a copy so I can compare and find out what I did wrong and try to find out why it works better.

Thank you so much for this most appropriate post.

Regards,

Dennis

[ssanfu]

Sorry, I lost you for a while.

OK, I made recommendations, but I didn't delete the fields that I had a line through.

Here is the (slightly modified) dB......

Hope this helps.......

[Dennis9412]

Thank you Steve, much appreciated!!

Regards,

Dennis