Your subquery's SQL statement is incomplete. Here is what I posted in my
suggestion (I've excerpted the part for the comparison for PROID):
A.[PROID] = (SELECT Max(T.[PROID])
FROM YourTableName AS T
WHERE T.[WOID] = A.[WOID]);
Here is what you have:
(([tblProcNested-Non].PROCID)=(SELECT
Max([tblProcNested-non].PROCID)));
Notice what is missing after the "Max" part of the statement --you do not
specify the table from which the data are to be drawn, nor do you use the
WOID value to filter the data set in the subquery.
Also, you must use an alias for the tablename in the subquery's SQL
statement so that the query knows you're not using the exact same data set
in the subquery as in the main query. Look again at the posted example I
provided. I am using A as the alias for the table in the main query, and T
as the alias for the table in the subquery.
Therefore, a revised query statement would be something like this:
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)=28289) AND
(([tblProcNested-Non].PROCID)=(SELECT Max(T.PROCID
FROM [tblProcNested-non] AS T WHERE
T.[WOID] = [tblWorkorder].[WOID])));
--
Ken Snell
<MS ACCESS MVP>
Grant said:
Hi Ken,
Here's my exact statement which I get an syntax error on:
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)=28289) AND
(([tblProcNested-Non].PROCID)=(SELECT Max([tblProcNested-non].PROCID)));
What am I doing wrong?
Thanks
Grant
:
SELECT A.[WOID], A.[PROID]
FROM YourTableName AS A
WHERE A.[WOID] = 28286 AND
A.[PROID] = (SELECT Max(T.[PROID])
FROM YourTableName AS T
WHERE T.[WOID] = A.[WOID]);
--
Ken Snell
<MS ACCESS MVP>
Hello Group,
I have a qry using [WOID] as the criteria to pull up a number of
records.
The criteria is set at 28286. This produces 8 records. Another field
in
the
qry is [PROID]. Of the 8 records, 4 have a [PROID] of 91234 and 4
have
94321.
What I'd like to have happen is the qry will only produce the records
with
the greater (highest) number in the [PROID] field.. 4 records would
be
produced with [PROID] being 94321.
Is this possible and how would I go about do this?
Thank You
Grant
