Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2019, 03:57 PM   #1
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 233
Thanks: 379
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Adding Count to a select query

I have a Select query that collects data from several tables.

Here's the simplified version of its sql :
Code:
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.
Attached Images
File Type: jpg 2019-09-12_8-38-48.jpg (67.3 KB, 64 views)


Last edited by Tera; 09-11-2019 at 04:26 PM.
Tera is offline   Reply With Quote
Old 09-11-2019, 06:10 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Adding Count to a select query

Hi. Have you tried using DCount()? However, it might run a bit slow.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (09-11-2019)
Old 09-11-2019, 06:24 PM   #3
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 233
Thanks: 379
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: Adding Count to a select query

Quote:
Originally Posted by theDBguy View Post
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.

Tera is offline   Reply With Quote
Old 09-11-2019, 07:31 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Adding Count to a select query

Quote:
Originally Posted by Tera View Post
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:
Code:
SELECT T1.FieldName, (SELECT Count(*) FROM TableName WHERE SomeField=T1.FieldName) AS RowNum
FROM TableName T1
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (09-12-2019)
Old 09-11-2019, 09:57 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,026
Thanks: 65
Thanked 2,557 Times in 2,454 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Adding Count to a select query

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Tera (09-12-2019)
Old 09-12-2019, 12:52 AM   #6
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 233
Thanks: 379
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: Adding Count to a select query

Quote:
Originally Posted by theDBguy View Post
Right. A subquery solution might look something like:
Code:
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.
Tera is offline   Reply With Quote
Old 09-12-2019, 12:59 AM   #7
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 233
Thanks: 379
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: Adding Count to a select query

Quote:
Originally Posted by arnelgp View Post
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.

Tera is offline   Reply With Quote
Old 09-12-2019, 04:31 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Adding Count to a select query

Quote:
Originally Posted by Tera View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (09-12-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] adding parameters to count query Weresmytriple Queries 3 09-16-2013 06:46 AM
Adding a Count to a Query Big_Si Queries 5 05-11-2009 03:28 AM
Anyway of adding like a count field to a query please? ignite Queries 1 02-03-2006 10:22 PM
Anyway of adding like a count field to a query please? ignite Queries 1 02-03-2006 05:32 PM
Update Query - Adding a count field flying linus Queries 2 10-13-2004 06:29 AM




All times are GMT -8. The time now is 08:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World