Calculating Two Query Sums even if No data present

[michaelkhewitt]

Hi there everyone, I have a report that generates from a "Work Order" and on the Work Order is two types of line items: one for Staffing Charges and one for Extra Costs. On the report WorkOrderPrint I have made sum fields for each line item section that work great, the problem I have is if there are no Extra Costs for the work order - the Grand Total field on the report does not show anything. There will always be staffing charges but sometimes no extra costs. I need the grand total to always show the total even if there is no result or data in the extra fees section. Is there a way to force the sum of the extra costs (even if no records exist) to a zero so that it can be calculated at the report stage? sorry if this all sounds unclear... Thank you

 

[theDBguy]

Hi. Welcome to AWF!

Have you tried using the Nz() function?

 

[michaelkhewitt]

Oh, no I have not. Do you have a tip on how that is used in this scenario.

 

[theDBguy]

Oh, no I have not. Do you have a tip on how that is used in this scenario.

I can't see your scenario, so I would only be guessing. The principle is this, let's say you have two totals A and B. Also, let's say you have a grand total that uses A + B. Now when A and B have values, you get a result. But, if one of them has no value, then you don't get anything back from the grand total. However, if maybe you try using Nz(A,0) + Nz(B,0) instead, perhaps you will get a good result back. Give it a try...

 

[Pat Hartman]

=Sum(Nz(Charges,0))

 

[Mark_]

By line items, are they in a child table OR are they fields in your WorkOrder table?

 

[mike60smart]

Oh, no I have not. Do you have a tip on how that is used in this scenario.

Hi
Are you able to upload a zipped copy of your database?

 

[michaelkhewitt]

Hi all,

I could not get the Nz format to work.
I have attached the Database in Zip file here.

Basically on report WorkOrderPrint I want the Grand Total to populate even if there is not a extra cost value.
It is based of the form Work Order which has Child line items x2 (staffing and extra costs)

On the report you will see that there are unbound sum fields of each line item type and then a unbound grand total field which does not seem to calculate properly if there is no data in the extra costs.

Hopefully the attached helps.

Michael

 

[mike60smart]

Hi all,

I could not get the Nz format to work.
I have attached the Database in Zip file here.

Basically on report WorkOrderPrint I want the Grand Total to populate even if there is not a extra cost value.
It is based of the form Work Order which has Child line items x2 (staffing and extra costs)

On the report you will see that there are unbound sum fields of each line item type and then a unbound grand total field which does not seem to calculate properly if there is no data in the extra costs.

Hopefully the attached helps.

Michael

Hi Michael
The Report is the least of your problems.
You have no true relationships set at the moment.
You are using Table Lookups which should not be used.
If you try to set Referential Integrity between your tblWorkOrder and tblEmployeeLine you will get the error shown below.

 

[michaelkhewitt]

Hi Michael
The Report is the least of your problems.
You have no true relationships set at the moment.
You are using Table Lookups which should not be used.
If you try to set Referential Integrity between your tblWorkOrder and tblEmployeeLine you will get the error shown below.

Uh oh. I have to admit this is all beyond my skills / knowledge at this point. Not sure how to correct this without screwing existing records up.

Oh well.
Thank you for any help you gave. Sigh.

Michael

 

[theDBguy]

Hi all,

I could not get the Nz format to work.
I have attached the Database in Zip file here.

Basically on report WorkOrderPrint I want the Grand Total to populate even if there is not a extra cost value.
It is based of the form Work Order which has Child line items x2 (staffing and extra costs)

On the report you will see that there are unbound sum fields of each line item type and then a unbound grand total field which does not seem to calculate properly if there is no data in the extra costs.

Hopefully the attached helps.

Michael

Is this what you wanted?

 

[michaelkhewitt]

Oh my goodness YES! Thank you!!

Michael

 

[theDBguy]

Oh my goodness YES! Thank you!!

Michael

Hope you can see where and how I used the Nz() function. Cheers!

 

[michaelkhewitt]

Yes I see where I was getting the syntax wrong!
Thank you so much!

Michael

 

[theDBguy]

Yes I see where I was getting the syntax wrong!
Thank you so much!

Michael

You're very welcome. We were all happy to assist. Good luck with your project.

 

[Mark_]

Uh oh. I have to admit this is all beyond my skills / knowledge at this point. Not sure how to correct this without screwing existing records up.

Oh well.
Thank you for any help you gave. Sigh.

Michael

Now comes the hard part. Start writing out exactly what you need as outputs and what inputs are available, then start writing out what your data should look like. There are a lot of us who are willing to help, but this is difficult because only YOU can say what does or doesn't need to be in there.

Once you figure out what your tables and relationships should be, we can help you with getting your program there.