Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grouping and Counting Looking for Differences

Status
Not open for further replies.

anniesolomon

Technical User
Oct 2, 2011
13
US
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.
 
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.
 
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.
 
Did you try my suggestion timestamped 7 Sep 10 20:38 ?

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

"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;
 
FYI, I've tested this:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top