Removing Specific Records from Queries

D

DRTurner0076

Hi,

I am struggling with a query that I am trying to produce. I have a list of
stock transactions (of Part Numbers) from which I need to identify only the
issues that have occurred since the QPA was reached (Quantity Per Assembly).

Part No. QPA
123456-01 10
325523-02 5
111111-01 2

From the example above, I need to show the transactions (in date order)
after the total issues has reached/exceeded the QPA.

Any takers?

David
 
D

Duane Hookom

How does anyone know how you define "total issues has reached/exceeded the
QPA"?
 
A

Adam Turner via AccessMonster.com

DRTurner0076 said:
Hi,

I am struggling with a query that I am trying to produce. I have a list of
stock transactions (of Part Numbers) from which I need to identify only the
issues that have occurred since the QPA was reached (Quantity Per Assembly).

Part No. QPA
123456-01 10
325523-02 5
111111-01 2

From the example above, I need to show the transactions (in date order)
after the total issues has reached/exceeded the QPA.

Any takers?

David
Click to expand...

Part No. QPA
123456-01 10
325523-02 5
111111-01 2

SELECT PartNum, Count(QPA)
FROM MyTable
GROUP BY PartNum
HAVING COUNT(QPA) >= MaxQPA
 
M

[MVP] S.Clark

To restrict data, you use a WHERE clause.

e.g.
Select PartNo, QPA from table WHERE Somefield > #12/12/2005#

So, I would do this in two queries, the first would be like the example
above, and the second would use the first, and perform the QPA Sum(or
Count??)

Select PartNo, Sum(QPA) as QPASum from QueryAbove GROUP BY PartNo.

If you want to get fancy, you could do it with one using a subquery.
 
Top