Grouping and Counting Looking for Differences

[anniesolomon]

I often have several requisitions on backorder against the same vendor contract and want to group them together so I only have to type my follow up notes one time. In other words, instead of having several TrackingNumber records, I just want one. I want to design a report that alerts me when a StockNumber has more than one (different) TrackingNumber so I can decide (based on the status of the order) whether or not to combine with an already established TrackingNumber. I tried grouping on the StockNumber and using Count([TrackingNumber]) > 1 but that doesn't work. I need to know when the TrackingNumber fields are different for a given StockNumber.

 

[anniesolomon]

Please ignore this post. I just remembered that I posted months ago (thread701-1619106). Just now getting the time to get back to thi problem.

 

[anniesolomon]

I shouldn't have asked everyone to ignore this post. I just spent hours trying to work with the earlier thread I mentioned but couldn't figure it out. It seems like Greg didn't explain how he developed qUniqueNSNXref. So, I'm still without a solution. I'm hopeful that someone will come to my rescue.

 

[PHV]

Did you try my suggestion timestamped 7 Sep 10 20:38 ?

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

 

[BigRed1212]

"It seems like Greg didn't explain how he developed qUniqueNSNXref"

That is the second query:
SELECT tblNSN.NSN, tblNSN.XrefIssueNum
FROM tblNSN
GROUP BY tblNSN.NSN, tblNSN.XrefIssueNum;

 

[PHV]

FYI, I've tested this:

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

with the following result:
[tt]NSN XrefIssueNum Nb
5330-01-043-5469 1205 1
5330-01-043-5469 1230 1[/tt]

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