Perfection as always. Thank you.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext.. 130
Fax (605)964-1070
"Valar Dohaeris"
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, January 15, 2018 10:45 PM
Subject: 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;
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
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;
FROM (tblCabinets INNER JOIN tblFolders ON tblCabinets.CabinetID = tblFolders.CabinetID) INNER JOIN tblDocuments ON tblFolders.FolderID = tblDocuments.FolderID
GROUP BY tblCabinets.CabinetID, tblCabinets.CabinetName;
| 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: Art Lorenzini <dbalorenzini@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar