Calculated query field

[NoviceIan]

Hi, I'm trying to create a query that calculates how much annual leave an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the fields is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Jeff Boyce]

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

 

[NoviceIan]

Yes I checked the inderlaying table.

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual leave an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the fields is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Jeff Boyce]

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual leave an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the

fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[NoviceIan]

I checked the fields data type properties meaning data type, decimal places
etc from the table design.

They both are Number, Double with two decimal places. The only difference
being the input mask of leave entitlement is 999.99 and the hours missed has
an additional digit before the decimal place.

Is that any clearer?

I'm not getting an error no, some employees details calculate correctly but
others dont for some reason. For example

One employee had 19 hours leave from a possible 202.50 the calculated field
rightly calculated that the employee has 183.50 remaining

However the second employee has taken 127.50 from a entitlement of 187.50
and the field wildly calculated 1372.50 remaining.

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual leave an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Chaim]

Why do you want to SUM those values? The hour difference per employee is
just the difference between [Leave Entitlement] and [Hours Missed]. SUMming
those would give the total of all employee's leave balances. Your original
post said:----
Chaim

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

leave

an

employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Chaim]

Never mind my last post. I missed the closing parentheses.

Blame it on my news reader.
--
----
Chaim

I checked the fields data type properties meaning data type, decimal places
etc from the table design.

They both are Number, Double with two decimal places. The only difference
being the input mask of leave entitlement is 999.99 and the hours missed has
an additional digit before the decimal place.

Is that any clearer?

I'm not getting an error no, some employees details calculate correctly but
others dont for some reason. For example

One employee had 19 hours leave from a possible 202.50 the calculated field
rightly calculated that the employee has 183.50 remaining

However the second employee has taken 127.50 from a entitlement of 187.50
and the field wildly calculated 1372.50 remaining.

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

:

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual

leave
an

employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results

in
the

thousends. I checked the fields themseleves and the format for

the
fields

is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[NoviceIan]

I wanted the field to calculate the difference to save users calculating
manually. We've had a problem with staff not calculating correctly and
allowing other staff to book to much leave as a result.

It is the difference between the two fields as you've pointed out but they
seem to be having difficulties so I thought it would be wise to calculate the
difference automatically.

I don’t understand why it is not working.

Why do you want to SUM those values? The hour difference per employee is
just the difference between [Leave Entitlement] and [Hours Missed]. SUMming
those would give the total of all employee's leave balances. Your original
post said:----
Chaim

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

:

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual

leave

an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in the
thousends. I checked the fields themseleves and the format for the fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Jeff Boyce]

An Input Mask should not affect the math you do on the underlying
actually-stored values.

Can you spot any differences between the customer that produce
accurate/inaccurate results?

Please post the entire SQL of the query.

Regards

Jeff Boyce
<Access MVP>

I checked the fields data type properties meaning data type, decimal places
etc from the table design.

They both are Number, Double with two decimal places. The only difference
being the input mask of leave entitlement is 999.99 and the hours missed has
an additional digit before the decimal place.

Is that any clearer?

I'm not getting an error no, some employees details calculate correctly but
others dont for some reason. For example

One employee had 19 hours leave from a possible 202.50 the calculated field
rightly calculated that the employee has 183.50 remaining

However the second employee has taken 127.50 from a entitlement of 187.50
and the field wildly calculated 1372.50 remaining.

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

:

When you "check the fields", are you saying that you checked the underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual

leave
an

employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results

in
the

thousends. I checked the fields themseleves and the format for

the
fields

is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[NoviceIan]

No unfortunately we do not have user level security so its difficult to
identify the culprits I'm affraid.

The sql is below:

SELECT Fullname.Expr1, Sum(Incident.[Hours Missed]) AS [SumOfHours Missed],
[District Nurse].[Leave Entitlement], Sum(([Leave Entitlement])-([Hours
Missed])) AS Expr2
FROM ([District Nurse] INNER JOIN Fullname ON [District Nurse].[Staff
Number]=Fullname.[Staff Number]) INNER JOIN Incident ON [District
Nurse].[Staff Number]=Incident.[Staff Number]
WHERE (((Incident.[Leave Date])>#3/31/2005#))
GROUP BY Fullname.Expr1, [District Nurse].[Leave Entitlement],
Incident.[Staff Number], Incident.Reason
HAVING (((Incident.[Staff Number])=[Please enter staff number:]) AND
((Incident.Reason)="Annual Leave"));

Expr1 is the name for the "Fullname" field which is a concatenation of
Forename and Surname.

An Input Mask should not affect the math you do on the underlying
actually-stored values.

Can you spot any differences between the customer that produce
accurate/inaccurate results?

Please post the entire SQL of the query.

Regards

Jeff Boyce
<Access MVP>

I checked the fields data type properties meaning data type, decimal places
etc from the table design.

They both are Number, Double with two decimal places. The only difference
being the input mask of leave entitlement is 999.99 and the hours missed has
an additional digit before the decimal place.

Is that any clearer?

I'm not getting an error no, some employees details calculate correctly but
others dont for some reason. For example

One employee had 19 hours leave from a possible 202.50 the calculated field
rightly calculated that the employee has 183.50 remaining

However the second employee has taken 127.50 from a entitlement of 187.50
and the field wildly calculated 1372.50 remaining.

Please re-read my response -- display and formatting are not the same as how
the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

:

When you "check the fields", are you saying that you checked the
underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much annual leave
an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return results in
the
thousends. I checked the fields themseleves and the format for the
fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?

 

[Jeff Boyce]

I don't see how differences in the underlying rows would show up related to
user security, but ...

Nothing jumped out at me on your SQL statement.

When one of my queries starts misbehaving, I generally try a few times to
modify it and re-test, then "junk" it and start over with a new query.

Best of luck

Jeff Boyce
<Access MVP>

No unfortunately we do not have user level security so its difficult to
identify the culprits I'm affraid.

The sql is below:

SELECT Fullname.Expr1, Sum(Incident.[Hours Missed]) AS [SumOfHours Missed],
[District Nurse].[Leave Entitlement], Sum(([Leave Entitlement])-([Hours
Missed])) AS Expr2
FROM ([District Nurse] INNER JOIN Fullname ON [District Nurse].[Staff
Number]=Fullname.[Staff Number]) INNER JOIN Incident ON [District
Nurse].[Staff Number]=Incident.[Staff Number]
WHERE (((Incident.[Leave Date])>#3/31/2005#))
GROUP BY Fullname.Expr1, [District Nurse].[Leave Entitlement],
Incident.[Staff Number], Incident.Reason
HAVING (((Incident.[Staff Number])=[Please enter staff number:]) AND
((Incident.Reason)="Annual Leave"));

Expr1 is the name for the "Fullname" field which is a concatenation of
Forename and Surname.

An Input Mask should not affect the math you do on the underlying
actually-stored values.

Can you spot any differences between the customer that produce
accurate/inaccurate results?

Please post the entire SQL of the query.

Regards

Jeff Boyce
<Access MVP>

I checked the fields data type properties meaning data type, decimal places
etc from the table design.

They both are Number, Double with two decimal places. The only difference
being the input mask of leave entitlement is 999.99 and the hours

missed
has

an additional digit before the decimal place.

Is that any clearer?

I'm not getting an error no, some employees details calculate

correctly
but

others dont for some reason. For example

One employee had 19 hours leave from a possible 202.50 the calculated field
rightly calculated that the employee has 183.50 remaining

However the second employee has taken 127.50 from a entitlement of 187.50
and the field wildly calculated 1372.50 remaining.



:

Please re-read my response -- display and formatting are not the

same as
how

the underlying fields are defined. Are you getting an error?

Jeff Boyce
<Access MVP>

Yes I checked the inderlaying table.

:

When you "check the fields", are you saying that you checked the
underlying
data in the table, or the query results?

Format concerns display, not calculation.

Regards

Jeff Boyce
<Access MVP>

Hi, I'm trying to create a query that calculates how much

annual
leave

an
employee has remaining. The expression I'm using is

Remaining: SUM ([Leave Entitlement])-[Hours Missed]

Now it seems to work for some employees but others return

results
in

the
thousends. I checked the fields themseleves and the format

for
the

fields
is:

Leave Entitlement 999.99
Hours missed 9999.99

Is the extra digit the problem here?