Need to count records in query and use that count in formula

[Repent]

I have a query that returns records that occur during the current week. The criteria for this is: DatePart("ww", [ProductionDate]) = DatePart("ww", Date()) and Year( [ProductionDate]) = Year(Date())

In the query are several calculated fields:
Waste: [LF Run]-[LF Produced]
Waste%: Sum(([Waste]/[LF Run]))
Productivity: Sum(([LF Produced]/33.75))

There is another field called "Time" with the criteria of: In ("END-Days","End-Nights") so that only records pertaining to the end of shift for days and nights is considered.

What I'm trying to do is to sum up all the Waste% numbers in the query and then divide that by the total number of records to give me the average of Waste%.

Can someone help me please?

 

[MarkK]

Is that valid math, to take an average of a percentage? Consider...

500/2000 = 25%
  3/   4 = 75%
503/2004 = 50% [COLOR="Green"]'obviously not true[/COLOR]

What does the average of a percentage mean?

 

[Repent]

Is that valid math, to take an average of a percentage? Consider...

500/2000 = 25%
  3/   4 = 75%
503/2004 = 50% [COLOR=Green]'obviously not true[/COLOR]

What does the average of a percentage mean?

I was hoping this post had an answer for me.

Yes for what I'm doing the math is correct. You take 5 averages for example, add them together then divide by the number of records. In this case 5. This gives you an average of the percentages. What's so weird about that? I'm not trying to use a 100 point (or 100%) scale.

 

[Repent]

A little more background may help. I have a chart, several really, that show about a dozen products listed along the "X" axis. On the "Y" axis is the waste percentage of each product through the manufacturing process. Workers enter records every two hours showing what product they made along with how much was waste.

At the end of the shift each products waste% numbers (end-days, end-nights) are added up then divided to get an overall waste percentage. This is not on a 100 point scale though.

What I need to do is be able to add up all the percentages from the "Y" axis then divide by the number of records/products.

For Example:

Product A = 2.67%
Product B = 3.69%
Product C = 4.51%
Total of percentages = 10.87%
Average Percentage = 3.62% overall waste (10.87% divided by 3)

Hope that helps.

 

[Taruz]

A little more background may help. I have a chart, several really, that show about a dozen products listed along the "X" axis. On the "Y" axis is the waste percentage of each product through the manufacturing process. Workers enter records every two hours showing what product they made along with how much was waste.

At the end of the shift each products waste% numbers (end-days, end-nights) are added up then divided to get an overall waste percentage. This is not on a 100 point scale though.

What I need to do is be able to add up all the percentages from the "Y" axis then divide by the number of records/products.

For Example:

Product A = 2.67%
Product B = 3.69%
Product C = 4.51%
Total of percentages = 10.87%
Average Percentage = 3.62% overall waste (10.87% divided by 3)

Hope that helps.

Hi..

select 
sum(field)
/
(select count(*) from table_name) 
from table_name

Try this sql..

 

[Repent]

Hi..

select 
sum(field)
/
(select count(*) from table_name) 
from table_name

Try this sql..

Sorry but I'm still new to a lot of this. Where do I put that code?

 

[Taruz]

change the name of the table and field, paste the sql view, a new query.

field, will be the percent field.. (Waste%)

So you're talking about the first message use the query..:

select 
sum([Waste%])
/
(select count(*) from query_name) 
from query_name

Even if you use a new field, the same logic..

 

[Repent]

Looks like that is working splendidly. I'll have to plug it into the chart to see it working but I just checked manually and the math is coming out correct.

I had been trying to create a calculated field using the expression builder in that same table with all the other numbers. I saw "that" SQL statement and didn't know where to plug it in in there.

I created a brand new query, changed the generic names to actual names/fields/query names and the result was what I had been beating my head against the desk for yesterday.

You solution is a lot easier than the expression I was trying to create.

I know you're very busy, but for my sake and those who will read this post, can you tell me your "thinking" behind this code? Is this known as a "select statement"?

I'm new to this but every example I can learn something from.

THANK YOU VERY MUCH FOR THE SOLUTION!!

chris