tags:

views:

85

answers:

1

EDIT: the database is Access 2007

Hi, I'm new here and I need some help:

I have three tables:

  1. technicians (Id, tech_name, is_active)
  2. type_services (Id, serv_name, is_active)
  3. services (Id, date_time, prod_name, quantity, serv_type, tech_name, is_active)

I have to make a report that contain:

  1. Number of services per tech (SUM(quantity))
  2. Number of type of services per tech
  3. Total per each services
  4. 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.

Remou
Yeah this works, but somes values are NULL so I change this: > TRANSFORM Sum(services.quantity) AS SumOfquantityfor this: > TRANSFORM IIF(Sum(services.quantity) IS NULL, 0,Sum(services.quantity)) AS SumOfquantity and it's works, thanks so much
tttony
You can use Nz: Nz(Sum([Quantity]),0) - you can use anything instead of 0, "N/A" for example, which may be more appropriate.
Remou
I use Nz() function but show me an error message: "The NZ function is not defined in the expression" I google it and I think that function is for access 2003, now I use: IIF(ISNULL(Sum(services.quantity)), 0,Sum(services.quantity)) and it works fine, thanks again for you time
tttony
Hi again Remou, last question xD how do I: ORDER BY [Total Of Quantity] DESC, I tested but seems to be complicated for me
tttony