Null Value for calculation stops whole result

[Chelcone]

Hi Everyone



Im building a database for a payroll system, its for a healthcare organization and they have some bonus rates that are dependent on staff doing certain shift patterns. The way i have got the database to say if the person has done those things is with a simple yes/no box. That was the easy bit, now i needed to get the system to calculate the total wage after the boxes had been ticked yes or no.

This is how i did the individual calculations:

query for flexible allowance:
Flex Ammount: ((((([hourly rate]*[contracted hours])/100)*1.49)*52.178)/12)
using the query window i set the below (the true section is in the criteria for the field) to filter out staff who had not had this box ticked.
[flexible allowance] = true

I used this for each one as the calculation is the same only with one different number, these are stored in three Independent query's. To get the sum of all these results i started another query with the following calculation

Total income: (((([core earnings])+[allowance money])+[rotation ammount])+[flex ammount])

and assuming that all of the yes/no boxes are set to true this works great... but as soon as one of them is set to false the whole calculation fails.

Can anyone tell me how to get round this? Of if over complicating things.

Thanks in advance
Chelcone

[recyan]

Check out Left Join & NZ.
If that doesn't help, post your query & if possible some dummy data.

Thanks

[Chelcone]

Thats the current query, im not that good at SQL so this is built in the query builder, i looked at the left join an NZ but i would need help with the syntax.

SELECT (((([core earnings])+[allowance money])+[rotation ammount])+[flex ammount]) AS [Total income]
FROM qry_rotation_ammount, qry_shift_allowance, qry_flex_ammount, qry_core_earnings;

Not sure how i can put dummy data because its all based on multiple fields having different values.


Thats how its set up in the form.

[recyan]

May be wrong, but I think basically, your using the final query in that manner results in something like Cartesian Product of a set with an Empty set, which will give an empty set.
Can you post the SQL for the other queries which you have shown in your final query & the relationship between these queries (eg. they are linked by a common ID like EmployeeID, etc).

Thanks

[Chelcone]

Ok here is the other queries:

qry_core_earnings:
SELECT ((([Contracted Hours]/7)*365.25)/12)*[hourly rate] AS [Core Earnings], tbl_staffinfo.ID, tbl_staffinfo.Title, tbl_staffinfo.[First Name], tbl_staffinfo.[Last Name]
FROM tbl_staffinfo;

qry_rotation_ammount:
SELECT tbl_staffinfo.ID, tbl_staffinfo.Title, tbl_staffinfo.[First Name], tbl_staffinfo.[Last Name], ((((([hourly rate]*[contracted hours])/100)*3.28)*52.178)/12) AS [Rotation Ammount], tbl_staffinfo.[Rotation Allowance]
FROM tbl_staffinfo
WHERE (((tbl_staffinfo.[Rotation Allowance])=True));

qry_shift_allowance
SELECT ((((([hourly rate]*[contracted hours])/100)*4.98)*52.178)/12) AS [Allowance money], tbl_staffinfo.ID
FROM tbl_staffinfo
WHERE (((tbl_staffinfo.[Shift Allowance])=True));

qry_flex_ammount:
SELECT ((((([hourly rate]*[contracted hours])/100)*1.49)*52.178)/12) AS [Flex Ammount], tbl_staffinfo.ID, tbl_staffinfo.Title, tbl_staffinfo.[First Name], tbl_staffinfo.[Last Name], tbl_staffinfo.[Flex Allowance]
FROM tbl_staffinfo
WHERE (((tbl_staffinfo.[Flex Allowance])=True));

qry_totals
SELECT (((([core earnings])+[allowance money])+[rotation ammount])+[flex ammount]) AS [Total income]
FROM qry_rotation_ammount, qry_shift_allowance, qry_flex_ammount, qry_core_earnings;

All are linked by [ID] which is the staff ID number that is all

[recyan]

Just check if below gives some guidelines :

SELECT 
    [coreEarnings] AS TheCoreEarnings, 
    NZ([allowanceMoney],0) AS TheAllowanceAmount, 
    NZ([rotationAmount],0) AS TheRotationAmount, 
    NZ([flexAmount],0) AS TheFlexAmount, 
    NZ([coreEarnings],0)+NZ([allowanceMoney],0)+NZ([rotationAmount],0)+NZ([flexAmount],0) AS TheTotalAmount
FROM 
    (
        (
            qry_core_earnings 
            LEFT JOIN 
            qry_shift_allowance 
            ON 
            qry_core_earnings.ID = qry_shift_allowance.ID
        ) 
        LEFT JOIN 
        qry_flex_ammount 
        ON 
        qry_core_earnings.ID = qry_flex_ammount.ID
    ) 
    LEFT JOIN 
    qry_rotation_ammount 
    ON 
    qry_core_earnings.ID = qry_rotation_ammount.ID;

Have taken qry_core_earnings on the Left Side (assuming that there always will be core earnings).
Advisable to avoid space in Table Field names.

Edit : Wondering whether we can avoid the need for the 4 queries.

Thanks

[Chelcone]

That works spot on! thank you!

One more thing, I need to place the results onto a sub-form (shown in previous post), its asking me for the related field what would i put into the code here to link these results to the ID of the staff member?

In the query designer i would just add the ID field on tbl_staff_info onto the designer but it wont let me do that.

Thanks

[Chelcone]

Ignore what i said... being a moron. Don't worry. This is now resolved!

Thanks again

[recyan]

Glad you got things working.
Do not know how things are at your end, but just a few thoughts :
Preferably, we should have the Staff Personal Info & the hourly rates, etc as seperate tables, as for a particular Staff, the hourly rates might change over a period of time. Ignore my words, in case you have things set up correctly & you had only provided dummy data to get an idea on how to proceed.

Thanks

[Chelcone]

thanks for the thoughts but they are indeed set up how you suggested haha. They call the value from another table using a look up.

I am having one small issue... for some reason TheAllowanceamount, Theflexamount and therotationamount wont let me change the formatting of the text box on the subform to get them to show as currency, the others do though. I have even tried setting the formatting to currency in the root query and that seems to make no difference. any ideas?

Thanks

[recyan]

Sorry, Not in to Forms & Reports. Some one else should be able to get you moving. In the meantime, just check if the Format of the input box on the Form can be changed in the Properties.

Thanks

[Chelcone]

Yea that's where I'm looking, there is nothing on the drop down, very strange.

Thanks for the help anyway.

I'll post in the right place in the form.