cross tab report display style

First of all, I think your 1st Query can be ignored because the InspectionType.ityp_id already existed in your 2nd query, I don't see any reason why you want to have the 1st Query and have it join with the 2nd one.
For easy understanding your query, I have rewrited your 2nd query:

SELECT     InspectionType.ityp_id, SUM(Inspection.insp_critical_failures) AS SumOfinsp_critical_failures,
                SUM(Inspection.insp_merit_points) AS SumOfinsp_merit_points,
                COUNT(Inspection.insp_id) AS CountOfinsp_id
FROM       Sector
                INNER JOIN Building
                       ON Sector.sect_id = Building.sect_id
                INNER JOIN InspectionUnit
                       ON Building.buil_id = InspectionUnit.buil_id
                INNER JOIN Inspection
                       ON InspectionUnit.unit_id = Inspection.unit_id
                INNER JOIN (InspectionSchedule
                       ON Inspection.sinp_id = InspectionSchedule.sinp_id
                INNER JOIN InspectionType
                       ON InspectionUnit.ityp_id = InspectionType.ityp_id
                       AND InspectionSchedule.ityp_id = InspectionType.ityp_id
GROUP BY InspectionType.ityp_id

For your question about cross tab, can you give us an example on how would you want your data to be display?

dylan

hi  dylanlee

if i write your query, i wont get all the data.
but the query is correct

i want all the data, if there is no count, no sum, just display zero
so, thats why SELECT ityp_id fROM InspectionType.
 i want to get ityp_id which unqique in the inspectiontype

what i want is
 
xxxx   10  20 25
x2       5   10  35
x3      11    0  34
x4       0     0   0


where (xxx,x2,x3,x4) will get the from inspectiontype(SELECT ityp_id fROM InspectionType)
but what i want is
10 20 25 get from the join query(refer 2nd query in prv. question)
and also  0 0 0 if no data present

thanks

So... Is your report looks like this?

ityp_id           SumOfinsp_critical_failures              SumOfinsp_merit_points            CountOfinsp_id
xxxx              10                                                 20                                            25
x2                 5                                                   10                                           35
x3                 11                                                 0                                             34
x4                 0                                                   0                                             0

In such case, I think cross-tab is not required, just put all your required fields to the detail sections will do...

dylan

And... to obtain all ityp_id, you could do left outer join in your query instead of joining them in crystal report:

SELECT    A.ityp_id, ISNULL(B.SumOfinsp_critical_failures, 0) SumOfinsp_critical_failures,
               ISNULL(B.SumOfinsp_merit_points, 0) SumOfinsp_merit_points,
               ISNULL(B.CountOfinsp_id, 0) CountOfinsp_id
FROM       InspectionType A
               LEFT OUTER JOIN
               (
                        SELECT     InspectionType.ityp_id, SUM(Inspection.insp_critical_failures) AS SumOfinsp_critical_failures,
                                        SUM(Inspection.insp_merit_points) AS SumOfinsp_merit_points,
                                        COUNT(Inspection.insp_id) AS CountOfinsp_id
                        FROM       Sector
                                        INNER JOIN Building
                                               ON Sector.sect_id = Building.sect_id
                                        INNER JOIN InspectionUnit
                                               ON Building.buil_id = InspectionUnit.buil_id
                                        INNER JOIN Inspection
                                               ON InspectionUnit.unit_id = Inspection.unit_id
                                        INNER JOIN (InspectionSchedule
                                               ON Inspection.sinp_id = InspectionSchedule.sinp_id
                                        INNER JOIN InspectionType
                                               ON InspectionUnit.ityp_id = InspectionType.ityp_id
                                               AND InspectionSchedule.ityp_id = InspectionType.ityp_id
                        GROUP BY InspectionType.ityp_id
               ) B
                       ON A.ityp_id = B.ityp_id

dylan

if i execute this query it IN MS ACCESS
IT SAY

syntax error (missing operator) in query expression 'ISNULL(B.SumOfinsp_critical_failures, 0)  SumOfinsp_critical_failures'

Sorry bsarahim, didn't notice you are using Access.
In access, you can use the below syntax instead:

IIf(IsNull(B.SumOfinsp_critical_failures),0,B.SumOfinsp_critical_failures) SumOfinsp_critical_failures

dylan

hi dlanyee,
still error say From Clause iam using ms acess 2003.
how abt B, where it is realted in below query


SELECT    A.ityp_id, IIf(IsNull(B.SumOfinsp_critical_failures),0,B.SumOfinsp_critical_failures) AS SumOfinsp_critical_failures,
             
IIf(IsNull(B.SumOfinsp_merit_points),0,B.SumOfinsp_merit_points) as SumOfinsp_merit_points,
IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) as  CountOfinsp_id
             
FROM       InspectionType A
               LEFT outer  JOIN
               (
                        SELECT     InspectionType.ityp_id, SUM(Inspection.insp_critical_failures) AS SumOfinsp_critical_failures,
                                         SUM(Inspection.insp_merit_points) AS SumOfinsp_merit_points,
                                         COUNT(Inspection.insp_id) AS CountOfinsp_id
                        FROM       Sector
                                        INNER JOIN Building
                                               ON Sector.sect_id = Building.sect_id
                                        INNER JOIN InspectionUnit
                                               ON Building.buil_id = InspectionUnit.buil_id
                                        INNER JOIN Inspection
                                               ON InspectionUnit.unit_id = Inspection.unit_id
                                        INNER JOIN (InspectionSchedule
                                               ON Inspection.sinp_id = InspectionSchedule.sinp_id
                                        INNER JOIN InspectionType
                                               ON InspectionUnit.ityp_id = InspectionType.ityp_id
                                               AND InspectionSchedule.ityp_id = InspectionType.ityp_id
                                        GROUP BY InspectionType.ityp_id
               )    B
                       ON A.ityp_id = B.ityp_id

B is the alias for the sub query.
I am not quite sure about access, but could you try append an "AS" infront of the alias?
SELECT    A.ityp_id, IIf(IsNull(B.SumOfinsp_critical_failures),0,B.SumOfinsp_critical_failures) AS SumOfinsp_critical_failures,
IIf(IsNull(B.SumOfinsp_merit_points),0,B.SumOfinsp_merit_points) as SumOfinsp_merit_points,
IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) as  CountOfinsp_id
             
FROM       InspectionType AS A
               LEFT outer  JOIN
               (
                        SELECT     InspectionType.ityp_id, SUM(Inspection.insp_critical_failures) AS SumOfinsp_critical_failures,
                                         SUM(Inspection.insp_merit_points) AS SumOfinsp_merit_points,
                                         COUNT(Inspection.insp_id) AS CountOfinsp_id
                        FROM       Sector
                                        INNER JOIN Building
                                               ON Sector.sect_id = Building.sect_id
                                        INNER JOIN InspectionUnit
                                               ON Building.buil_id = InspectionUnit.buil_id
                                        INNER JOIN Inspection
                                               ON InspectionUnit.unit_id = Inspection.unit_id
                                        INNER JOIN (InspectionSchedule
                                               ON Inspection.sinp_id = InspectionSchedule.sinp_id
                                        INNER JOIN InspectionType
                                               ON InspectionUnit.ityp_id = InspectionType.ityp_id
                                               AND InspectionSchedule.ityp_id = InspectionType.ityp_id
                                        GROUP BY InspectionType.ityp_id
               ) AS B
                       ON A.ityp_id = B.ityp_id

dylan

still the same error again

hmm... could you post me the exact error message?

dylan

hi dylan the error is

 syntax error in from clause