I have a Select query that collects data from several tables.
Here's the simplified version of its sql :
SELECT tblReceiption.Rec_ID, tblReceiption.SetName, tblMaster.DrawingNo, tblUsedMaterials.ProgramNo,
tblUsedMaterials.LaNc, tblUsedMaterials.Tools FROM tblReceiption
INNER JOIN (tblUsedMaterials INNER JOIN tblMaster ON tblUsedMaterials.DrawingID_FK = tblMaster.DrawingID_PK)
ON tblReceiption.DrawingID_FK = tblMaster.DrawingID_PK
I'm trying to add a column to count the number of "DrawingNo". As the following image shows, 2,3 & 4 rows have the same DrawingNo. So I need a column that shows 3 for these rows and 1 for the others.
Here's what the query looks like:
I tried to add a sub query as a column, but I wasn't able to make it work.
Any kind of advice is appreciated.