Order by no. of occurrances 1

[johnv20]

Hi, I need to count the no of times an entry appears in a field in 1 table and order a seperate table by this count where the 2 tables are linked by a different field. Does anyone have an idea how this can be done ?

 

[Niv3k]

Here is one that sorts by the number of line items associated with a purchase order, and grabs some stuff from the purchase order, too. Modify and have fun:

SELECT dbo.tblPOLineItem.PONumber, Count(dbo.tblPOLineItem.PONumber) AS Total,
 	tblPurchaseOrders.RequestDate, tblPurchaseOrders.CostCenter
	FROM dbo.tblPOLineItem INNER Join dbo.tblPurchaseOrders 
		ON tblPOLineItem.PONumber = tblPurchaseOrders.PONumber
	GROUP BY dbo.tblPOLineItem.PONumber, tblPurchaseOrders.CostCenter, tblPurchaseOrders.RequestDate
	ORDER BY Total DESC

Kevin

 

[johnv20]

Perfect - thanks