Count on different criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to count two fields within a single query for different
findings?

What I want is:
A count of cylinders within a fixed system and
A count of fixed systems(identifiable because location is duplicated)

I can group on location and get counts per fixed system or count location
and get the same count as cylinder count. My sad attempt follows example:
example:
Bow Thruster Compartment appears (in Location) 12 times because there are 12
identifiable cylinders within the Bow Thruster Compartment fixed system. MSD
Compartment appears 4 times because there are 4 identifiable cylinders within
the MSD Compartment fixed system.

I want results as follows:
CylinderCount = 16
FixedSystem(OrLocationCount)=2


SELECT tblFireExtInspection.VesselID, tblCylDCLog.Fixed,
Count(tblCylDCLog.Fixed) AS CountOfFixed, tblCylDCLog.Type,
Count(tblCylDCLog.Location) AS CountOfLocation
FROM tblFireExtInspection INNER JOIN tblCylDCLog ON
tblFireExtInspection.FireInspectionRecord = tblCylDCLog.FireInspectionNumber
GROUP BY tblFireExtInspection.VesselID, tblCylDCLog.Fixed, tblCylDCLog.Type
HAVING
(((tblFireExtInspection.VesselID)=[Forms]![frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)=Yes)) OR
(((tblFireExtInspection.VesselID)=[Forms]![frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)<>No)) OR
(((tblFireExtInspection.VesselID)=[Forms]![frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)=Yes));

I know it needs to incorporate "where distinct" or "Unique" but just keep
falling short.

Any help is much appreciated.

Mary
 
-----Original Message-----
Is it possible to count two fields within a single query for different
findings?

What I want is:
A count of cylinders within a fixed system and
A count of fixed systems(identifiable because location is duplicated)

I can group on location and get counts per fixed system or count location
and get the same count as cylinder count. My sad attempt follows example:
example:
Bow Thruster Compartment appears (in Location) 12 times because there are 12
identifiable cylinders within the Bow Thruster Compartment fixed system. MSD
Compartment appears 4 times because there are 4 identifiable cylinders within
the MSD Compartment fixed system.

I want results as follows:
CylinderCount = 16
FixedSystem(OrLocationCount)=2


SELECT tblFireExtInspection.VesselID, tblCylDCLog.Fixed,
Count(tblCylDCLog.Fixed) AS CountOfFixed, tblCylDCLog.Type,
Count(tblCylDCLog.Location) AS CountOfLocation
FROM tblFireExtInspection INNER JOIN tblCylDCLog ON
tblFireExtInspection.FireInspectionRecord = tblCylDCLog.FireInspectionNumber
GROUP BY tblFireExtInspection.VesselID,
Click to expand...
tblCylDCLog.Fixed, tblCylDCLog.Type
HAVING
(((tblFireExtInspection.VesselID)=[Forms]! [frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)=Yes)) OR
(((tblFireExtInspection.VesselID)=[Forms]! [frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)<>No)) OR
(((tblFireExtInspection.VesselID)=[Forms]! [frmFireExtInspection]![VesselID])
AND ((tblCylDCLog.Fixed)=Yes));

I know it needs to incorporate "where distinct" or "Unique" but just keep
falling short.

Any help is much appreciated.

Mary
Click to expand...
Why not run create a pivot table based on your quey, this
should enable you to drill down on the subtotals you
require
 
Back
Top