query miscalculation

A

Alex

I have some problem in the following query in the WHERE criteria:

SELECT tblAdjustedData.PayrollPunchIn AS [Day],
IIf([OT1Hrs]>0,[OT1Hrs],IIf([PayrollWorkedTime]-10>0 And
[PayrollWorkedTime]-10<=1 And [OT2Hrs]=0,[PayrollWorkedTime]-10,0)) AS
OTHrsRate1,
IIf([OT2Hrs]>0,[OT2Hrs],IIf([PayrollWorkedTime]-10>1,[PayrollWorkedTime]-10,0))
AS OTHrsRate2, tblAdjustedData.PayrollWorkedTime,
tblAdjustedData.RateToBeUsed, [RateToBeUsed]*[OTHrsRate1]*0.5 AS PayOT1,
[RateToBeUsed]*[OTHrsRate2] AS PayOT2, [PayOT1]+[PayOT2] AS TotalPay,
[PayrollWorkedTime]*[RateToBeUsed] AS RegPay, tblAdjustedData.AdditionCode
FROM tblAdjustedData
WHERE
(((tblAdjustedData.PayrollPunchIn)>=[forms]![frmLabourAnalysisPeriod]![cboStartDay]
And
(tblAdjustedData.PayrollPunchIn)<=[forms]![frmLabourAnalysisPeriod]![cboEndDay]+1)
AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));

I'm doing the calculation of the sum of PayrollWorkedTime field:
Without any criteria on the AdditionCode it calculates 6,810.5 (which is
correct).
With shown criteria for AdditionCode it gives 6,645.25.
But, if I'm entering AdditionCode only = "L", I have 65.5; AdditionCode only
= "P", I have 88; and "W" - 0. So, it should be 6,657 (6,810.5 - 65.5 - 88 -
0), it's a correct number. The difference is 11.75.
If I'm entering only <> "L", it gives me 6,733.25 instead of 6,810.5 - 65.5
= 6,745 (the same 11.75 difference). If I'm entering only <> "P", it again
gives the same 11.75 difference.

I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.

Could anybody clarify how it's possible?

Thanks
 
J

John Vinson

AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));
I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.

Well, if the code is P then it's certainly not equal to L; and if it's
L, it's certainly not W. Therefore the clause above will return all
records with non-NULL addition codes.

Change that portion of the Where clause to

AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")


John W. Vinson[MVP]
 
A

Alex

Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.
 
J

John Spencer (MVP)

A guess, but do you have any records where the AdditionCode is Null. If so,
applying criteria will drop the records with null values as well as any others
you are trying to exclude.
Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.

John Vinson said:
Well, if the code is P then it's certainly not equal to L; and if it's
L, it's certainly not W. Therefore the clause above will return all
records with non-NULL addition codes.

Change that portion of the Where clause to

AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")


John W. Vinson[MVP]
 
A

Alex

You are right, John.
I have records with Null for the AdditionCode.
How could I fix it to get it working?

Thanks

John Spencer (MVP) said:
A guess, but do you have any records where the AdditionCode is Null. If so,
applying criteria will drop the records with null values as well as any others
you are trying to exclude.
Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.

John Vinson said:
On Wed, 29 Dec 2004 16:31:03 -0800, "Alex"

AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));

I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.

Well, if the code is P then it's certainly not equal to L; and if it's
L, it's certainly not W. Therefore the clause above will return all
records with non-NULL addition codes.

Change that portion of the Where clause to

AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")


John W. Vinson[MVP]
 
A

Alex

John, I've found those records where the AdditionCode is Null (it's just two
rows from 724 rows). They look exactly the same like the rest of the rows.
But, when I've added in the query the field Test:
IIf(IsNull([AdditionCode]),"NULL","") it showed NULL only for those two rows.
Somehow it was entered with the Null value for these records in this field.

I think, I should update this field for the whole table to avoide any Null
values there.
What updating whould you advise - if it's Null -> insert " " or what?

Thanks in advance


John Spencer (MVP) said:
A guess, but do you have any records where the AdditionCode is Null. If so,
applying criteria will drop the records with null values as well as any others
you are trying to exclude.
Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.

John Vinson said:
On Wed, 29 Dec 2004 16:31:03 -0800, "Alex"

AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));

I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.

Well, if the code is P then it's certainly not equal to L; and if it's
L, it's certainly not W. Therefore the clause above will return all
records with non-NULL addition codes.

Change that portion of the Where clause to

AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")


John W. Vinson[MVP]
 
J

John Spencer (MVP)

I can't tell you what makes sense in this case. You can use an arbitrary,
otherwise unused value such as "?" to tell you that the data is missing. You
probably can't use a zero-length string or possibly just spaces depending on how
you've set up the field definitions.

You could add criteria to test for NULL in the case where you want the records
that are null.

Where tblAdjustedDate.AdditionCode IS NULL
AND TblAdjustedDate.AdditionCode NOT IN ("L","P","W")
John, I've found those records where the AdditionCode is Null (it's just two
rows from 724 rows). They look exactly the same like the rest of the rows.
But, when I've added in the query the field Test:
IIf(IsNull([AdditionCode]),"NULL","") it showed NULL only for those two rows.
Somehow it was entered with the Null value for these records in this field.

I think, I should update this field for the whole table to avoide any Null
values there.
What updating whould you advise - if it's Null -> insert " " or what?

Thanks in advance

John Spencer (MVP) said:
A guess, but do you have any records where the AdditionCode is Null. If so,
applying criteria will drop the records with null values as well as any others
you are trying to exclude.
Thanks, John.
I did it but the same result: the sum of [PayrollWorkedTime] is 6,645.25
instead of 6,657.
I'll try to find which row gives this mistake.

:

On Wed, 29 Dec 2004 16:31:03 -0800, "Alex"

AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));

I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.

Well, if the code is P then it's certainly not equal to L; and if it's
L, it's certainly not W. Therefore the clause above will return all
records with non-NULL addition codes.

Change that portion of the Where clause to

AND tblAdjustedData.AdditionCode NOT IN("L", "P", "W")


John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top