CODEWITHSUNDEEP
sqlms-access
neojakey
neojakey

Reputation: 1663

Terrible access database issue

I have a terrible database from a client and I need to count the number of results from a query, which is as follows:

SELECT
  Offices.OfficeID
, ContractsBooksCommodities.CommodityID
  FROM ((((Offices
INNER JOIN tbl_Sales
        ON Offices.CompanyID = tbl_Sales.CompanyID)
INNER JOIN ContractBooks
        ON tbl_Sales.CompanyID = ContractBooks.CompanyID)
INNER JOIN ContractsBooksAds
        ON ContractBooks.ContractNum = ContractsBooksAds.ContractNum)
INNER JOIN ContractsBooksBrands
        ON ContractsBooksAds.ContractNum = ContractsBooksBrands.ContractNum)
INNER JOIN ContractsBooksCommodities
        ON ContractsBooksBrands.ContractNum = ContractsBooksCommodities.ContractNum;

How can I make this count the number of records returned?

Upvotes: 0

Views: 59

Answers (2)

Yawar
Yawar

Reputation: 11607

In general,

select count(*)
from (
  your-select-query
)

will give you the number of records returned by your query.

Upvotes: 3

duffymo
duffymo

Reputation: 308733

COUNT and GROUP BY would be my guess:

SELECT Offices.OfficeID, ContractsBooksCommodities.CommodityID, COUNT(*) AS COUNT
FROM ((((Offices INNER JOIN tbl_Sales ON Offices.CompanyID = tbl_Sales.CompanyID) INNER JOIN ContractBooks ON tbl_Sales.CompanyID = ContractBooks.CompanyID) INNER JOIN ContractsBooksAds ON ContractBooks.ContractNum = ContractsBooksAds.ContractNum) INNER JOIN ContractsBooksBrands ON ContractsBooksAds.ContractNum = ContractsBooksBrands.ContractNum) INNER JOIN ContractsBooksCommodities ON ContractsBooksBrands.ContractNum = ContractsBooksCommodities.ContractNum
GROUP BY Offices.OfficeID, ContractsBooksCommodities.CommodityID
ORDER BY Offices.OfficeID, ContractsBooksCommodities.CommodityID

Upvotes: 0

Related Questions