Help with a database query

[joakimhan]

I got the following two tables

TblCompany
ID, Company, Org

and

TblOrders
OrderID, CompanyID, CheckOrder

The way i want to display the record is like this

Company, Org, (Sum of orders), (Sum of CheckOrder)

My query
strSQL = "SELECT ID, Company, Org FROM tblCompany C "
strSQL=strSQL & "INNER JOIN TblOrders O "
strSQL=strSQL & "ON C.ID=O.CompanyID "
strSQL=strSQL & "WHERE C.Company LIKE '%" & Request.Form("foretag&quot & "%'"
strSQL=strSQL & " ORDER BY C.ID ASC "

The problem is that it will return a new line for every order that one of the companies have done.

Can I make the query so that each company will only be displayed once and how many orders they made.

Hope for some help from you gurus

best Regards
Joakim

 

[rac2]

Well it can be done but it could be rather complex.

Let me ask you this, do some companies have more than one org within the company? If so, do you want the totals for each org within each company? Or is org just another piece of information about the company that you want to show along with the company and the totals for the company? It makes a difference in how you must write the query.

I will make a suggestion for the easy case and I will just write the query instead of building a string. I will use a constant for Request.Form("foretag&quot.

SELECT C.ID, C.Company, C.Org, COUNT(*), COUNT(O.CheckOrder)
FROM tblCompany C
INNER JOIN TblOrders O ON C.ID=O.CompanyID
WHERE C.Company LIKE '%LLP%'
GROUP BY C.ID, C.Company, C.Org
ORDER BY C.ID ASC

If there is one Org for one Company, and one ID for one Company and CheckOrder is NULL for orders which have not been checked then this will give you the total number of orders and the total number checked. If there are multiple Orgs for one company this will give totals for each Org.