Adding Count to a select query (1 Viewer)

[deletedT]

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
WHERE (((tblReceiption.SetName)="NF17979BK101"));

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.

 

[theDBguy]

Hi. Have you tried using DCount()? However, it might run a bit slow.

 

[deletedT]

Hi. Have you tried using DCount()? However, it might run a bit slow.

Sorry for not mentioning it.
Yes, I've already tried DCount. But as you explained it's too slow. If the query returns some 500 records, it takes about 10 sec to show the result, and as I scroll the result I feel a freeze time every now and then somewhat like Access is trying to refresh the result.

I was thinking of a sub-query if it's possible.

 

[theDBguy]

Sorry for not mentioning it.
Yes, I've already tried DCount. But as you explained it's too slow. If the query returns some 500 records, it takes about 10 sec to show the result, and as I scroll the result I feel a freeze time every now and then somewhat like Access is trying to refresh the result.

I was thinking of a sub-query if it's possible.

Right. A subquery solution might look something like:

SELECT T1.FieldName, (SELECT Count(*) FROM TableName WHERE SomeField=T1.FieldName) AS RowNum
FROM TableName T1

 

[arnelgp]

you can also make a Total Query first (qryNumDrawings):

select DrawingID_FK, Count("1") As NumOfDrawing
from tblUsedMaterials
group by DrawinID_FK;

add the query (field, NumOfDrawing) to your posted query and join by DrawingID_FK.

 

[deletedT]

Right. A subquery solution might look something like:

SELECT T1.FieldName, (SELECT Count(*) FROM TableName WHERE SomeField=T1.FieldName) AS RowNum
FROM TableName T1

I followed you. But unfortunately I couldn't feel any speed progress comparing to DCount.
I'll try to add some more criteria to bring down the number of result.

Million thanks for your help.

 

[deletedT]

you can also make a Total Query first (qryNumDrawings):

select DrawingID_FK, Count("1") As NumOfDrawing
from tblUsedMaterials
group by DrawinID_FK;

add the query (field, NumOfDrawing) to your posted query and join by DrawingID_FK.

I appreciate your help. It seems faster than DCount.

 

[theDBguy]

I followed you. But unfortunately I couldn't feel any speed progress comparing to DCount.
I'll try to add some more criteria to bring down the number of result.

Million thanks for your help.

Hi. Glad to hear Arnel’s suggestion worked better. Good luck with your project.