Re: [MS_AccessPros] Multiple Counts in a query

 

I would try get the number of folders from a subquery that doesn't include tblDocuments.

SELECT tblCabinets.CabinetID, tblCabinets.CabinetName, 

(SELECT Count(tblFolders.FolderID)

 FROM tblFolders

 WHERE tblFolders.CabinetID = tblCabinets.CabinetID) AS CountOfFolderID, Count(tblDocuments.DocumentID) AS CountOfDocumentID
FROM (tblCabinets INNER JOIN tblFolders ON tblCabinets.CabinetID = tblFolders.CabinetID) INNER JOIN tblDocuments ON tblFolders.FolderID = tblDocuments.FolderID
GROUP BY tblCabinets.CabinetID, tblCabinets.CabinetName;

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, January 15, 2018 5:45 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Multiple Counts in a query

 

I am trying to do multiple  counts in a single query.

I am after the number of folders in a cabinet and the number of documents in a folder.

So far this is what I got.

SELECT tblCabinets.CabinetID, tblCabinets.CabinetName, Count(tblFolders.FolderID) AS CountOfFolderID, Count(tblDocuments.DocumentID) AS CountOfDocumentID
FROM (tblCabinets INNER JOIN tblFolders ON tblCabinets.CabinetID = tblFolders.CabinetID) INNER JOIN tblDocuments ON tblFolders.FolderID = tblDocuments.FolderID
GROUP BY tblCabinets.CabinetID, tblCabinets.CabinetName;

Query1

CabinetID	CabinetName	CountOfFolderID	CountOfDocumentID
1	SRST TERO Compliance Plans	4	4
2	SRST TERO Applicants	3582	3582
3	SRST TERO VENDORS	176	176
4	SRST TERO Equipment	75	75
5	SRST TERO PROJECTS	4	4
6	TERO-Documents	21	21
7	Job Seek Sign In Sheets	7	7
8	Old Crusher	3	3
9	Tero Specification Manual Project	2	2
10	TERO BOARD MEETINGS	6	6
11	Migration Issue	11	11

 THis is basically returning the number of documents in the cabinets and leaving out a subquery somewhere.

Pertinent table information

tblCabinet

  CabinetID

  CabinetName

tblFolder

  FoldersID

  CabinetID

  FolderName

tblDocuments

  DocumentID

  FolderID

  DocumentName

a Cabinet can have multiple Folders

and a Folder can have multiple documents

I am basically trying to return the number of folders in a cabinet and the number of documents in the folders in the cabinet.

Thank you

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___