Drill down within a qry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Group

My qry produces, in part,

WOID Item# PNID PROCID
2813 Item1 1 9502
2813 Item1 2 9503
2813 Item2 3 9504

For Item1, I only want the highest PROCID number.. so I would only
get:
2813 Item1 2 9503
2813 Item2 3 9504

Here is my qry:
SELECT tblPNNested.Cert, tblWorkorder.WOID, [tblProcNested-Non].[Item#],
[tblProcNested-Non].Desc, [tblProcNested-Non].Thick,
[tblProcNested-Non].Area, [tblProcNested-Non].PNID, [tblProcNested-Non].PROCID
FROM tblPNNested INNER JOIN (tblWorkorder INNER JOIN [tblProcNested-Non] ON
tblWorkorder.WOID = [tblProcNested-Non].WOID) ON (tblWorkorder.WOID =
tblPNNested.WOID) AND (tblPNNested.[Item#] = [tblProcNested-Non].[Item#])
WHERE (((tblPNNested.Cert)=-1) AND ((tblWorkorder.WOID)=28319));


Thanks
Grant
 
For Item1, I only want the highest PROCID number.
Click to expand...

Use a Subquery:

SELECT tblPNNested.Cert, tblWorkorder.WOID,
[tblProcNested-Non].[Item#],
[tblProcNested-Non].Desc, [tblProcNested-Non].Thick,
[tblProcNested-Non].Area, [tblProcNested-Non].PNID,
[tblProcNested-Non].PROCID
FROM tblPNNested INNER JOIN (tblWorkorder INNER JOIN
[tblProcNested-Non] ON
tblWorkorder.WOID = [tblProcNested-Non].WOID) ON (tblWorkorder.WOID =
tblPNNested.WOID) AND (tblPNNested.[Item#] =
[tblProcNested-Non].[Item#])
WHERE (((tblPNNested.Cert)=-1) AND ((tblWorkorder.WOID)=28319))
AND [tblProcNested-Non].[PROCID] = (SELECT Max(X.ProcID) FROM
[tblProcNested-Non] AS X WHERE X.WOID = [tblProcNested-Non].[WOID]));

John W. Vinson[MVP]
 
Hi John,
When I run your qry I only get:

WOID Item# PNID PROCID
2813 Item2 3 9504

I need:

WOID Item# PNID PROCID
2813 Item1 2 9503
2813 Item2 3 9504

What change to the qry would be needed?
Thanks
Grant




John Vinson said:
For Item1, I only want the highest PROCID number.
Click to expand...

Use a Subquery:

SELECT tblPNNested.Cert, tblWorkorder.WOID,
[tblProcNested-Non].[Item#],
[tblProcNested-Non].Desc, [tblProcNested-Non].Thick,
[tblProcNested-Non].Area, [tblProcNested-Non].PNID,
[tblProcNested-Non].PROCID
FROM tblPNNested INNER JOIN (tblWorkorder INNER JOIN
[tblProcNested-Non] ON
tblWorkorder.WOID = [tblProcNested-Non].WOID) ON (tblWorkorder.WOID =
tblPNNested.WOID) AND (tblPNNested.[Item#] =
[tblProcNested-Non].[Item#])
WHERE (((tblPNNested.Cert)=-1) AND ((tblWorkorder.WOID)=28319))
AND [tblProcNested-Non].[PROCID] = (SELECT Max(X.ProcID) FROM
[tblProcNested-Non] AS X WHERE X.WOID = [tblProcNested-Non].[WOID]));

John W. Vinson[MVP]
Click to expand...
 
Hi John,
When I run your qry I only get:

WOID Item# PNID PROCID
2813 Item2 3 9504

I need:

WOID Item# PNID PROCID
2813 Item1 2 9503
2813 Item2 3 9504

What change to the qry would be needed?
Thanks
Grant
Click to expand...

Eh?

You said:
For Item1, I only want the highest PROCID number.
Click to expand...

My query gives you that.

Please explain the logic. Why would you get these particular two
records?


John W. Vinson[MVP]
 
PMFBI

I think John correlated WOID
but not Item# if I understand
correctly.

you might try this variation:

SELECT tblPNNested.Cert, tblWorkorder.WOID,
[tblProcNested-Non].[Item#],
[tblProcNested-Non].Desc, [tblProcNested-Non].Thick,
[tblProcNested-Non].Area, [tblProcNested-Non].PNID,
[tblProcNested-Non].PROCID
FROM tblPNNested INNER JOIN (tblWorkorder INNER JOIN
[tblProcNested-Non] ON
tblWorkorder.WOID = [tblProcNested-Non].WOID) ON (tblWorkorder.WOID =
tblPNNested.WOID) AND (tblPNNested.[Item#] =
[tblProcNested-Non].[Item#])
WHERE
(((tblPNNested.Cert)=-1)
AND
((tblWorkorder.WOID)=28319))
AND
[tblProcNested-Non].[PROCID] =
(SELECT Max(X.ProcID)
FROM
[tblProcNested-Non] AS X
WHERE
X.WOID = [tblProcNested-Non].[WOID]
AND
X.[Item#] = [tblProcNested-Non].[Item#]));

note the last line where you would now be finding the max
in a WOID and * an Item# * "grouping"
 
Back
Top