Query not giving right percent decimal

[Guest]

Hello I have a query that I want to divide the employees delay time by the
employees worked time. I made this query which works but the decimal place is
in the wrong spot. I am looking for a percentage like this 35% but its giving
me 353% I have the decimal place set at 0. What am I doing wrong? Thanks!!

 

[Guest]

Sorry I forgot to post the SQL

SELECT Sum([DT REGULAR]/[EMPLOYEE TIME]) AS [Daily Percent],
tblMain.[EMPLOYEE NAME]
FROM tblMain
WHERE (((tblMain.[EMPLOYEE NAME])=[Enter Employees Name]) AND
((tblMain.[DAYS DATE]) Between [Enter Start Date: (mm/dd/yy)] And [Enter Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];

 

[Douglas J. Steele]

Shouldn't that be

SELECT Sum([DT REGULAR])/Sum([EMPLOYEE TIME]) AS [Daily Percent]

?

(And recognize that it'll fail if there's ever a time when there's no
Employee Time values in the table)

 

[Guest]

I will try it when I go to work tonight. What do you mean it will fail? If so
how can I fix this? Thanks!

Shouldn't that be

SELECT Sum([DT REGULAR])/Sum([EMPLOYEE TIME]) AS [Daily Percent]

?

(And recognize that it'll fail if there's ever a time when there's no
Employee Time values in the table)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry I forgot to post the SQL

SELECT Sum([DT REGULAR]/[EMPLOYEE TIME]) AS [Daily Percent],
tblMain.[EMPLOYEE NAME]
FROM tblMain
WHERE (((tblMain.[EMPLOYEE NAME])=[Enter Employees Name]) AND
((tblMain.[DAYS DATE]) Between [Enter Start Date: (mm/dd/yy)] And [Enter
Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];

 

[Douglas J. Steele]

If Sum([EMPLOYEE TIME]) equals 0, you're going to have a division by 0 error
(For that matter, you could just as easily had that error with Sum([DT
REGULAR]/[EMPLOYEE TIME]))

Try:

SELECT IIf(Sum([EMPLOYEE TIME] = 0, 1, Sum([DT REGULAR])/Sum([EMPLOYEE
TIME])) AS [Daily Percent]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I will try it when I go to work tonight. What do you mean it will fail? If
so
how can I fix this? Thanks!

Shouldn't that be

SELECT Sum([DT REGULAR])/Sum([EMPLOYEE TIME]) AS [Daily Percent]

?

(And recognize that it'll fail if there's ever a time when there's no
Employee Time values in the table)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry I forgot to post the SQL

SELECT Sum([DT REGULAR]/[EMPLOYEE TIME]) AS [Daily Percent],
tblMain.[EMPLOYEE NAME]
FROM tblMain
WHERE (((tblMain.[EMPLOYEE NAME])=[Enter Employees Name]) AND
((tblMain.[DAYS DATE]) Between [Enter Start Date: (mm/dd/yy)] And
[Enter
Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];

 

[John W. Vinson]

Hello I have a query that I want to divide the employees delay time by the
employees worked time. I made this query which works but the decimal place is
in the wrong spot. I am looking for a percentage like this 35% but its giving
me 353% I have the decimal place set at 0. What am I doing wrong? Thanks!!

I have no idea, since you have not chosen to give us any indication of
the expression you're using. It's wrong, evidently, but unless you
post it here nobody could possibly help you correct it!

Please open the query in SQL view and post the SQL text here. A couple
of sample values and the expected result would help as well.

John W. Vinson [MVP]

 

[Guest]

John, If you have read my second post it has the SQL.

 

[Guest]

Thanks you Douglas that was very helpful...

If Sum([EMPLOYEE TIME]) equals 0, you're going to have a division by 0 error
(For that matter, you could just as easily had that error with Sum([DT
REGULAR]/[EMPLOYEE TIME]))

Try:

SELECT IIf(Sum([EMPLOYEE TIME] = 0, 1, Sum([DT REGULAR])/Sum([EMPLOYEE
TIME])) AS [Daily Percent]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I will try it when I go to work tonight. What do you mean it will fail? If
so
how can I fix this? Thanks!

Shouldn't that be

SELECT Sum([DT REGULAR])/Sum([EMPLOYEE TIME]) AS [Daily Percent]

?

(And recognize that it'll fail if there's ever a time when there's no
Employee Time values in the table)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry I forgot to post the SQL

SELECT Sum([DT REGULAR]/[EMPLOYEE TIME]) AS [Daily Percent],
tblMain.[EMPLOYEE NAME]
FROM tblMain
WHERE (((tblMain.[EMPLOYEE NAME])=[Enter Employees Name]) AND
((tblMain.[DAYS DATE]) Between [Enter Start Date: (mm/dd/yy)] And
[Enter
Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];