Calculations Using Aggregate Data

[JAKnudson]

Hello,

I have a problem and from what I find on the internet I'm wondering if I want to do something that cant be done in Access.
I am pulling data into a table from our AS 400 (curently monthly). This table has fields for date, department, Part number, Decription, quantity, and a code telling me if the entry was scrap or production. So far so good. I can get daily totals for each by department by using the aggregate function 'sum'.
I need to take this a step further and do a "Parts Per Million" calculation or PPM. The PPM is simply the scrap divided by the production multiplied by 1 million. This is where the problem comes in for me. Every combination of query that i have tried results in the query timing out or I get my procution data and then the same scrap total repeated for the entire month. Is there a way to use the aggregate functions in my PPM calculation? I can't figgure out how to make the query work so that I can graph all three, production, scrap and PPM all on one graph.

 

[jzwp22]

I would typically build a second query that pulls the necessary information from the aggregate query and do the calculation in the second query.

 

[JAKnudson]

My problem comes from using the aggregate totals for each day. It summarizes the days for me but it would let me use the result i another calculation. Every time I do, it hangs up the query until it finally times out and fails.
I'm afraid that i will need to write my own aggregate function to total each day and then use that in the second calculation. I'm not capable of doing that (not smart enough).
I'm not SQL smart either but I think there may be some usefulness using the DISTINCT statement to summarize each day and then building from that point but again i'm not a programmer so it would take me quite some time to even see if that approach would ever work.

 

[jzwp22]

It summarizes the days for me but it would let me use the result i another calculation. Every time I do, it hangs up the query until it finally times out and fails.

It sounds like something else might be at fault. Is there any chance that you can post your database with any sensitive data removed or altered? Do you import the information from your AS400 system into an Access table or are you using a linked table?

 

[JAKnudson]

I have attached a sample.

 

[jzwp22]

Which query is the aggregate query you referred to in your earlier post?