Removing Specific Records from Queries

[Guest]

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

 

[Duane Hookom]

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

 

[Adam Turner via AccessMonster.com]

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

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

 

[[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.