Distinct Records

[TheRook]

I currently have a query built but am now wanting to only show specific
distinct results.

Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'

What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y

For example:
PART_NO. INC_SFDC
123 Y
123 Y
123 N
123 Y
123 Y
666 Y
666 Y
666 Y

the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
Obiously there ar other colums involved but are only for results not to
query against.

How can this be done?

Thanks in advance

 

[Jerry Whittle]

SELECT TblRook.PART_NO
FROM TblRook
GROUP BY TblRook.PART_NO
HAVING Min(TblRook.INC_SFDC)=DMax("[INC_SFDC]","TblRook");

I haven't tested for Nulls.

 

[Dirk Goldgar]

I currently have a query built but am now wanting to only show
specific distinct results.

Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'

What i am wanting to show is only the PART NO.'s were ALL
INCLUDE_IN_SFDC = Y

For example:
PART_NO. INC_SFDC
123 Y
123 Y
123 N
123 Y
123 Y
666 Y
666 Y
666 Y

the result would only show 666, as the 3rd instance of 123 as
INC_SFDC as N. Obiously there ar other colums involved but are only
for results not to query against.

How can this be done?

Thanks in advance

It sounds like you want a query that specifies

SELECT <some fields> FROM YourTable
WHERE [PART_NO] Not In
(SELECT [PART_NO] FROM YourTable
WHERE [INCLUDE_IN_SFDC_Y_N] = 'N')

It's not clear to me from your description whether that query should
also have a DISTINCT or GROUP BY clause. Also, I'm not sure what the
real field names are. But maybe you can take it from here.

Note: the Jet database engine doesn't handle the "Not In" construct
very efficiently. If you find this version of the query to run
unacceptably slowly, there are other ways to frame it that optimize
better. But this is the cleanest way to express it.

 

[Jerry Whittle]

Forget about my first effort. It's lacking a couple of things. Try this:

SELECT TblRook.PART_NO
FROM TblRook
GROUP BY TblRook.PART_NO
HAVING (((Min(TblRook.INC_SFDC))
=DMax("[INC_SFDC]","TblRook","PART_NO = " & [PART_NO])
AND (Min(TblRook.INC_SFDC))='Y'));

 

[TheRook]

As you can probably tell I am new to Access. I have not told you that all
the data is from 4 different tables that are linked. PART_No is in
DMCS_PLANNING_OP_SEQ and INCLUDE_IN_SFDC is in DMCS_PLANNINGS_STAGE.

Please find below my current query, which results ALL records, as being new
to it can not understand where I put your segestions:

SELECT DMCS_PLANNINGS_OP_SEQ.PART_NUMBER,
DMCS_PLANNINGS_OP_SEQ.PLANNINGS_vc, DMCS_PLANNINGS_OP_SEQ.CENTRE,
DMCS_PLANNINGS_OP_SEQ.OP_SEQ, DMCS_PLANNINGS_STAGE.STAGE,
DMCS_CENTRES.INCLUDE_IN_SFDC
FROM ((DMCS_PLANNINGS_OP_SEQ INNER JOIN DMCS_PLANNINGS_STAGE ON
(DMCS_PLANNINGS_OP_SEQ.PLANNINGS_vc = DMCS_PLANNINGS_STAGE.PLANNINGS_vc) AND
(DMCS_PLANNINGS_OP_SEQ.PART_NUMBER = DMCS_PLANNINGS_STAGE.PART_NUMBER)) INNER
JOIN DMCS_PLAN001_OP_SEQ ON (DMCS_PLANNINGS_OP_SEQ.PART_NUMBER =
DMCS_PLAN001_OP_SEQ.PART_NUMBER) AND (DMCS_PLANNINGS_OP_SEQ.OP_SEQ =
DMCS_PLAN001_OP_SEQ.OP_SEQ)) INNER JOIN DMCS_CENTRES ON
DMCS_PLAN001_OP_SEQ.CENTRE = DMCS_CENTRES.CENTRES
WHERE (((DMCS_CENTRES.INCLUDE_IN_SFDC)="Y"))
ORDER BY DMCS_PLANNINGS_OP_SEQ.PART_NUMBER, DMCS_PLANNINGS_OP_SEQ.OP_SEQ;