Where Count > 1 only when values are different

[annie52]

I'm trying to build a report that identifies items that can potentially be worked together. I've played with Unique Values, Count, Group but nothing seems to work for me. I want to identify NSNs that have more than one unique XrefIssueNum.

In this example, NSN 5330-01-094-1406 should not be on the report since there is only one unique XrefIssueNum. On the other hand, NSN 5330-01-03-5469 should be reviewed since it has two unique XrefIssueNum values.

NSN XrefIssueNum
5330-01-043-5469 1205
5330-01-043-5469 1230
5330-01-094-1406 491
5330-01-094-1406 491

 

[SkipVought]

Select NSN

From [Your Table]

Group By NSN

Having Count(*)>1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[annie52]

That doesn't work for what I want to do. For example, if an NSN is listed 6 times but is related to the same XrefIssueNum, I don't want it on the report because they have already been grouped together.

Say an NSN is listed 5 times with the same XrefIssueNum and once more with a different XrefIssueNum. I do want this on the report so the different XrefIssueNum can be reviewed to see if it makes sense to group it with the other XrefIssueNum that's already related to the NSN 5 times.

 

[SkipVought]

Select NSN

From 
(
Select DISTINCT NSN, XrefIssueNum
From [Your Table]
)
Group By NSN

Having Count(*)>1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[annie52]

That shows me the NSN and XrefIssueNum for the 5 related issues but does not show the NSN and XrefIssueNum for the other 1 issue that I want to review. I need a way to determine if an NSN has DIFFERENT XrefIssueNum values.

 

[SkipVought]

Please post an example of the data in your table and the results you expect to see, that illustrates this issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[annie52]

I ended up using a make-table query to store unique records (NSN and XrefIssueNum) in a temporary table. I used that table in another make-table query to store NSNs where count >1 in a second temporary table. Then, by relating the NSNs in the second table to the NSNs in the first, I'm able to get the information I need. Sure seems the long way around but, at least, it works the way I need it to. I appreciate you hanging in there with me Skip.

 

[traingamer]

I used 3 queries to get the results I think you're seeking:
[tt]
(Data)
NSN XrefIssueNum
5330-01-094-1406 491
5330-01-094-1406 491
5330-01-094-1123 702
5330-01-094-1133 703
5330-01-043-5469 1205
5330-01-043-5469 1205
5330-01-043-5469 1205
5330-01-043-5469 1230
5330-01-043-5469 1230[/tt]

query QUniqueNSNXref

SELECT tblNSN.NSN, tblNSN.XrefIssueNum
FROM tblNSN
GROUP BY tblNSN.NSN, tblNSN.XrefIssueNum;

which returns
[tt]NSN XrefIssueNum
5330-01-043-5469 1205
5330-01-043-5469 1230
5330-01-094-1123 702
5330-01-094-1133 703
5330-01-094-1406 491[/tt]

query qryCountGT1 (returns all NSN with more than one distinct xref)

SELECT qUniqueNSNXref.NSN, Count(qUniqueNSNXref.XrefIssueNum) AS CountOfXrefIssueNum
FROM qUniqueNSNXref
GROUP BY qUniqueNSNXref.NSN
HAVING (((Count(qUniqueNSNXref.XrefIssueNum))>1));

which returns
[tt]NSN CountOfXrefIssueNum
5330-01-043-5469 2[/tt]

final query:

SELECT distinct tblNSN.NSN, tblNSN.XrefIssueNum
FROM tblNSN INNER JOIN qryCountGT1 ON tblNSN.NSN = qryCountGT1.NSN;

which returns [tt]NSN XrefIssueNum
5330-01-043-5469 1205
5330-01-043-5469 1230[/tt]

You could probably nest these or tweak them a little, but this is one way.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[PHV]

Typed, untested

SELECT A.NSN, A.XrefIssueNum, Count(*) AS Nb
FROM (SELECT D.NSN
  FROM (SELECT DISTINCT NSN, XrefIssueNum FROM yourTable) D
  GROUP BY D.NSN HAVING Count(*)>1
) S INNER JOIN yourTable A ON S.NSN = A.NSN
GROUP BY A.NSN, A.XrefIssueNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886