Count or sum help on query

  • Thread starter Thread starter jannie
  • Start date Start date
J

jannie

This is my SQL code for a query I'm running that is working great for my
report. What I need now is that I need the total of Y's and N's for each
column. So I need to know how to make my sql put at the bottom on report
Total count for how many Y's for conductor casing,and how many N's, how many
Y's for surface casing, How many N's, How many Y's for Prod casing, how many
N's, How many CMPS Y's and how many N's and How many Wells on Prod Y's and
N's count. Then my report will be complete. This is my code so far and my
query is running exactly how I need it to. I just need totals to complete it.
Thanks.

SELECT MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME, MANUAL_INPUT_DRILL_DATES.STATUS,
MANUAL_INPUT_DRILL_DATES.CONDUCTOR_CSG, MANUAL_INPUT_DRILL_DATES.SURF_CSG,
MANUAL_INPUT_DRILL_DATES.FIRST_GAS_TOW, MANUAL_INPUT_DRILL_DATES.RELEASE_RIG,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![CONDUCTOR_CSG])=True,"N","T") AS
Conductor_Csg__Set,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![SURF_CSG])=True,"N","T") AS
Surface_Csg_Set,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![PROD_CSG_SET])=True,"N","T") AS
Prod_Csg_Set,
IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![FIRST_GAS_TOW])=True,"N","T") AS
First_Gas, IIf(IsNull([MANUAL_INPUT_DRILL_DATES]![RELEASE_RIG])=True,"N","T")
AS Rig_Release
FROM MANUAL_INPUT_DRILL_DATES
ORDER BY MANUAL_INPUT_DRILL_DATES.PLATFORM_NAME,
MANUAL_INPUT_DRILL_DATES.WELL_NAME;

Jannie
 
Since this is for a report, it is easiest to add totals/aggregates in the
report footer section. To count values, you only need to create a true/false
calculation. For instance:
[Surface Casing]="Y"
This will return -1 for true or 0 for false. Change the value to positive
using Abs() and then Sum it.
=Sum( Abs( [Surface Casing]="Y" ) )
and
=Sum( Abs( [Surface Casing]="N" ) )
This assumes you have a text field named [Surface Casing] with values of "Y"
and "N".
 
Back
Top