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