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

 

[TT]

This can be done in two steps, first of all, you would need a query to
identify all the records that have INCLUDE_IN_SFDC_Y_N = "N" and then filter
them out from the original query.

Query 1:

SELECT PART_NO, INCLUDE_IN_SFDC_Y_N
FROM tblName
WHERE (((INCLUDE_IN_SFDC_Y_N)="N"));

Then you would build a second query:

SELECT tblName.PART_NO, tblName.INCLUDE_IN_SFDC_Y_N
FROM tblName LEFT JOIN Query1 ON tblName.PART_NO= Query1.PART_NO
WHERE (((Query1.PART_NO) Is Null))
GROUP BY tblName.PART_NO, tblName.INCLUDE_IN_SFDC_Y_N;

You would substitue your table name with tblName.

You can also do this with an embedded subquery, however, I don't have enough
experience with that to help you.

Hopefully this helps!!
TT

 

[Gary Walter]

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.

If I understand correctly...

A typical method is:
(replace "yurtable" w/ actual table/query name)

SELECT [PART_NO.]
FROM yurtable
GROUP BY
[PART_NO.]
HAVING
Max([INC_SFDC ]='Y') < 0

true = -1
false = 0

if any [INC_SFDC ] was not 'Y'
for a specific PART_NO. group,
Max would be 0

if all [INC_SFDC ] were 'Y'
for a specific PART_NO. group,
Max would be -1

For what its worth and if its not too late...

what good do you get from including a "."
in your field name?

as a general rule, its best to try not to use
any punctuation in field names

in fact, I never understood the need for
the "underlines" as well

PartNo
IncSFDC

seem perfectly descriptive to me.

in the real world, that may not be possible, i.e.,
I have worked with AS400 data where they
thought nothing of including "#" in their field
names....I had no control over it.

good luck,

gary