Overflow message in Query

[webmagic]

Hello,
I am working in Access 2000 and have a query based on avg percentages.There are two fields, one for usersId, and the other is percentages. What I am trying to do is to group by userId and then find the average.There are only about 332 lines of code. when I run this query I get an "overflow" error message.

I have looked thoruhg this forum and nothing really fit my problem. When I get my percentages, I get them from another query, and give about 13 digits after the decimal, is this part of the problem? Can I change the decimal remainder in a query?

Any help is greatly appreciated.

 

[khawar]

Can you post your sql

 

[webmagic]

Here you go:

SELECT OTM_40_Credit.USER_ID, Avg(OTM_40_Credit.OTM_Credit_Percent_40) AS AvgOfOTM_Credit_Percent_40
FROM OTM_40_Credit
GROUP BY OTM_40_Credit.USER_ID;

 

[khawar]

There is no problem in query syntax
can you tell about datatypes of your fields

 

[webmagic]

One is text field the other is a field that has been derived from a query-can I look at the properties of those fields? Also how can I change the amount of places after the decimal?

Thanks for looking at this and helping me, I appreciate it.

 

[khawar]

Open the table in design view and check the data types of the fields

 

[webmagic]

One is text, the other is decimal

 

[khawar]

It should work upload your table and query if possible

 

[boblarson]

the other is a field that has been derived from a query

What is the SQL for THAT query that the field is derived from?

 

[khawar]

One is text field the other is a field that has been derived from a query

I forgot that point

 

[webmagic]

SELECT OTM_STATS_40.USER_ID, OTM_STATS_40.MANUFACTURING_EQUIPMENT, Count(OTM_STATS_40.MANUFACTURING_EQUIPMENT) AS CountOfMANUFACTURING_EQUIPMENT, Sum(OTM_STATS_40.Time_Percent_worked) AS SumOfTime_Percent_worked1, Sum(OTM_STATS_40.OTM_credit) AS SumOfOTM_credit, [SumOfOTM_credit]/[SumOfTime_Percent_worked1]*100 AS OTM_Credit_Percent_40
FROM OTM_STATS_40
GROUP BY OTM_STATS_40.USER_ID, OTM_STATS_40.MANUFACTURING_EQUIPMENT;

 

[webmagic]

I have attached some of the queries and a table-unflrtunetly I can't add some of the linked tables-but you should be able to see all of my code.

Thank you so much for your help. I just can't see why it is happening.

 

[khawar]

There is no attachment dear

 

[webmagic]

Sorry-Here you go

 

[khawar]

it does not have the table on which the query is based try importing the link table into sample database other its not possible to view the find out the problem

 

[webmagic]

We'll trythis again-now I am getting an overflow error!!! UGGHHHH!!!

 

[redneckgeek]

Your overflow is likely being caused by a divide by zero error in the OTM_40_Credit query. You should always test the dividend for zero if there's any chance it could be zero.

iif(sumoftime_percent_worked1<>0,[SumOfOTM_credit]/[SumOfTime_Percent_worked1]*100,0) AS OTM_Credit_Percent_40