Q:

Make this report in one SQL in C#

EDIT: the database is Access 2007
Hi, I'm new here and I need some help:
I have three tables:

technicians (Id, tech_name, is_active)
type_services (Id, serv_name, is_active)
services (Id, date_time, prod_name, quantity, serv_type, tech_name, is_active)

I have to make a report that contain:

Number of services per tech (SUM(quantity))
Number of type of services per tech
Total per each services
Total of services

Example:

TECH_NAME TYPE_SERV1 TYPE_SERV2 TYPE_SERV3 TYPE_SERV4 TYPE_SERV5 TYPE_SERV6 TOTAL
NAME1     2          0          3          7          15         52         79
NAME2     0          0          1          6          18         45         70
NAME3     0          0          2          3          13         38         56
NAME4     1          1          0          3          11         21         37
TOTAL     3          1          6          19         57         156        242

All using a date interval
NOTE: In the services table I use the string name of serv_type and tech_name directly, so I don't use the number id
This is the NEW sql, that it's works but if it's possible to make all this in a SQL sentence with no C# extra code, because not always there are one type services on services

SELECT
  COUNT(Srvs.serv_type) AS numReg,
  SUM(Srvs.quantity) AS tot,
  Techs.tech_name AS tchs,
  Srvs.serv_type AS srv 
  FROM 
  technicians AS Techs, 
  type_services AS TySrv,
  services AS Srvs 
  WHERE 
  (Techs.is_active = true AND
  TySrv.is_active = true AND
  Srvs.is_active = true) AND
  (Srvs.date_time BETWEEN #2010/06/01 00:00:00# AND #2010/08/30 23:59:59# 
  AND Srvs.tech_name = Techs.tech_name)
  AND Srvs.serv_type = TySrv.serv_name
  GROUP BY Srvs.serv_type, Techs.tech_name
  ORDER BY Techs.tech_name ASC

Before this SQL I had three SQL to make this report xD, so I need that be more simple with one SQL
Thanks and I hope you know what I'm trying to say

A:

In Access, I think you want something like this:
TRANSFORM Sum(services.quantity) AS SumOfquantity
SELECT services.tech_name, 
       Sum(services.quantity) AS [Total Of Quantity], 
       Count(services.serv_type) AS [Count of Services]
FROM (services 
INNER JOIN technicians ON services.tech_name = technicians.tech_name) 
INNER JOIN type_services ON services.serv_type = type_services.serv_name
WHERE services.is_active=True 
    AND technicians.is_active=True
    AND type_services.is_active=True 
    AND services.date_time Between #6/1/2010# And #8/30/2010 23:59:59#
GROUP BY services.tech_name
PIVOT services.serv_type

if you wish to vary the dates, you can use a parameter.

