Convert Count(Case()) SQL Server Query to Access

M

meyvn77

Hello I have a Query in a function that summarizes a pretty big table
in to summary tables in SQL Server.
With my current knowledge of Access I can't pull this off. Im not sure
I can use a case statement Ex.COUNT(CASE DIRFMINT WHEN 'N' THEN 1 ELSE
NULL END). I'm really frusterated at the limitations in access but its
useability is nice.

Here is the Code and Query





Public Function MAKE_SUB_DATE(summary_table As String, node_type As
String, link_table As String, link_field As String, STREET1 As String,
STREET2 As String, From_Date As String, To_Date As String)

Dim sql_1 As String
Dim sql_2 As String
Dim sql_3 As String


DoCmd.RunSQL "DELETE FROM " & summary_table & ""

sql_1 = " INSERT INTO " & summary_table & "" & _
" (NODE, NORTH, EAST, SOUTH, WEST, Left_Turn,
Right_Turn, Angle, Rear_End, Head_on, AT_INT, INFLUBY, NOT_AT_INT,
BRIDGE, DRIVEWAY, DAY," & _
" DARK_LIGHT, DARK_NOLIGHT, DUSK, DAWN, FATAL,
INCAP, NON_INCAP, POSSIBLE, NONE, NON_TRAFF, FATSUM, INJCNT, VEHCNT," &
_
" TOTAL_CRASHES, Yr_1989, Yr_1990, Yr_1991,
Yr_1992, Yr_1993, Yr_1994, Yr_1995, Yr_1996, Yr_1997, Yr_1998, Yr_1999,
Yr_2000, Yr_2001," & _
" Yr_2002, Yr_2003, Yr_2004, Yr_2005, Yr_2006,
Yr_2007, Yr_2008, Yr_2009, Yr_2010, Yr_2011, Yr_2012, Yr_2013, Yr_2014,
Yr_2015, Yr_2016," & _
" Yr_2017, AVG_CRASH, FLAG_BIKE, FLAG_TRUCK,
FLAG_PED, FLAG_SPEED, FLAG_DUI, FLAG_RED, FLAG_AGR, FLAG_NIGHT,
FLAG_BEACH," & _
" STREET1, STREET2) " & _
" SELECT GIS_EVENTS." & node_type & ", COUNT(CASE DIRFMINT WHEN 'N'
THEN 1 ELSE NULL END) AS NORTH," & _
" COUNT(CASE DIRFMINT WHEN 'E' THEN 1 ELSE NULL
END) AS EAST, COUNT(CASE DIRFMINT WHEN 'S' THEN 1 ELSE NULL END) AS
SOUTH," & _
" COUNT(CASE DIRFMINT WHEN 'W' THEN 1 ELSE NULL
END) AS WEST, COUNT(CASE FSTHARM1 WHEN 'LEFT-TURN' THEN 1 ELSE NULL
END)" & _
" AS LEFT_TURN, COUNT(CASE FSTHARM1 WHEN
'RIGHT-TURN' THEN 1 ELSE NULL END) AS RIGHT_TURN," & _
" COUNT(CASE FSTHARM1 WHEN 'ANGLE' THEN 1 ELSE
NULL END) AS ANGLE, COUNT(CASE FSTHARM1 WHEN 'REAR-END' THEN 1 ELSE
NULL END)" & _
" AS REAR_END, COUNT(CASE FSTHARM1 WHEN 'HEAD-ON'
THEN 1 ELSE NULL END) AS HEAD_ON," & _
" COUNT(CASE SITELOC WHEN 'AT INTERSECTION' THEN
1 ELSE NULL END) AS AT_INT," & _
" COUNT(CASE SITELOC WHEN 'INFLUENCED BY
INTERSECTION' THEN 1 ELSE NULL END) AS INFLUBY," & _
" COUNT(CASE SITELOC WHEN 'NOT AT
INTERSECTION/RRXING/BRIDGE' THEN 1 ELSE NULL END) AS NOT_AT_INT," & _
" COUNT(CASE SITELOC WHEN 'BRIDGE' THEN 1 ELSE
NULL END) AS BRIDGE, COUNT(CASE SITELOC WHEN 'DRIVEWAY' THEN 1 ELSE
NULL END)" & _
" AS DRIVEWAY, COUNT(CASE LIGHTING WHEN
'DAYLIGHT' THEN 1 ELSE NULL END) AS DAY," & _
" COUNT(CASE LIGHTING WHEN 'DARK (STREET LIGHT)'
THEN 1 ELSE NULL END) AS DARK_LIGHT," & _
" COUNT(CASE LIGHTING WHEN 'DARK (NO STREET
LIGHT)' THEN 1 ELSE NULL END) AS DARK_NOLIGHT," & _
" COUNT(CASE LIGHTING WHEN 'DUSK' THEN 1 ELSE
NULL END) AS DUSK, COUNT(CASE LIGHTING WHEN 'DAWN' THEN 1 ELSE NULL
END)" & _
" AS DAWN, COUNT(CASE ACC_SEV WHEN 'FATAL' THEN 1
ELSE NULL END) AS FATAL," & _
" COUNT(CASE ACC_SEV WHEN 'INCAPACITATING INJ'
THEN 1 ELSE NULL END) AS INCAP,"

sql_2 = " COUNT(CASE ACC_SEV WHEN 'NON_INCAP INJ' THEN 1 ELSE NULL END)
AS NON_INCAP, " & _
" COUNT(CASE ACC_SEV WHEN 'POSSIBLE INJ' THEN 1
ELSE NULL END) AS POSSIBLE, COUNT(CASE ACC_SEV WHEN 'NONE' THEN 1 ELSE
NULL " & _
" END) AS NONE, COUNT(CASE ACC_SEV WHEN
'NON-TRAFFIC FATALITY' THEN 1 ELSE NULL END) AS NON_TRAFF,
SUM(GIS_EVENTS.FATCNT) " & _
" AS FATSUM, SUM(GIS_EVENTS.INJCNT) AS INJCNT,
SUM(GIS_EVENTS.VEHCNT) AS VEHCNT, COUNT(DISTINCT GIS_EVENTS.CASEID) " &
_
" AS TOTAL_CRASHES, COUNT(CASE YEAR(DATE_) WHEN
1989 THEN 1 ELSE NULL END) AS YR_1989, COUNT(CASE YEAR(DATE_) " & _
" WHEN 1990 THEN 1 ELSE NULL END) AS YR_1990,
COUNT(CASE YEAR(DATE_) WHEN 1991 THEN 1 ELSE NULL END) AS YR_1991, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 1992 THEN 1 ELSE
NULL END) AS YR_1992, COUNT(CASE YEAR(DATE_) WHEN 1993 THEN 1 ELSE NULL
END) " & _
" AS YR_1993, COUNT(CASE YEAR(DATE_) WHEN 1994
THEN 1 ELSE NULL END) AS YR_1994, COUNT(CASE YEAR(DATE_) WHEN 1995 THEN
1 ELSE NULL " & _
" END) AS YR_1995, COUNT(CASE YEAR(DATE_) WHEN
1996 THEN 1 ELSE NULL END) AS YR_1996, COUNT(CASE YEAR(DATE_) " & _
" WHEN 1997 THEN 1 ELSE NULL END) AS YR_1997,
COUNT(CASE YEAR(DATE_) WHEN 1998 THEN 1 ELSE NULL END) AS YR_1998, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 1999 THEN 1 ELSE
NULL END) AS YR_1999, COUNT(CASE YEAR(DATE_) WHEN 2000 THEN 1 ELSE NULL
END) " & _
" AS YR_2000, COUNT(CASE YEAR(DATE_) WHEN 2001
THEN 1 ELSE NULL END) AS YR_2001, COUNT(CASE YEAR(DATE_) WHEN 2002 THEN
1 ELSE NULL " & _
" END) AS YR_2002, COUNT(CASE YEAR(DATE_) WHEN
2003 THEN 1 ELSE NULL END) AS YR_2003, COUNT(CASE YEAR(DATE_) " & _
" WHEN 2004 THEN 1 ELSE NULL END) AS YR_2004,
COUNT(CASE YEAR(DATE_) WHEN 2005 THEN 1 ELSE NULL END) AS YR_2005, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 2006 THEN 1 ELSE
NULL END) AS YR_2006, COUNT(CASE YEAR(DATE_) WHEN 2007 THEN 1 ELSE NULL
END) " & _
" AS YR_2007, COUNT(CASE YEAR(DATE_) WHEN 2008
THEN 1 ELSE NULL END) AS YR_2008, COUNT(CASE YEAR(DATE_) WHEN 2009 THEN
1 ELSE NULL " & _
" END) AS YR_2009, COUNT(CASE YEAR(DATE_) WHEN
2010 THEN 1 ELSE NULL END) AS YR_2010, COUNT(CASE YEAR(DATE_) " & _
" WHEN 2011 THEN 1 ELSE NULL END) AS YR_2011,
COUNT(CASE YEAR(DATE_) WHEN 2012 THEN 1 ELSE NULL END) AS YR_2012, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 2013 THEN 1 ELSE
NULL END) AS YR_2013, COUNT(CASE YEAR(DATE_) WHEN 2014 THEN 1 ELSE NULL
END) " & _
" AS YR_2014, COUNT(CASE YEAR(DATE_) WHEN 2015
THEN 1 ELSE NULL END) AS YR_2015, COUNT(CASE YEAR(DATE_) WHEN 2016 THEN
1 ELSE NULL " & _
" END) AS YR_2016, COUNT(CASE YEAR(DATE_) WHEN
2017 THEN 1 ELSE NULL END) AS YR_2017, "


sql_3 = " ROUND(CAST(12 * COUNT(MONTH(GIS_EVENTS.date_)) AS [FLOAT]) /
CAST " & _
" ((SELECT COUNT(*) AS TOTAL_MONTHS " & _
" FROM (SELECT TOP 100
PERCENT YEAR(DATE_) AS YEAR, MONTH(DATE_) AS MONTH, COUNT(MONTH(DATE_))
AS _COUNT " & _
" FROM GIS_EVENTS
" & _
" GROUP BY
YEAR(DATE_), MONTH(DATE_) " & _
" HAVING
(Count(Month(DATE_)) > 10) " & _
" ORDER BY
YEAR(DATE_), MONTH(DATE_)) DERIVEDTBL) AS [FLOAT](5)), 2) AS AVG_CRASH,
SUM(GIS_EVENTS.F_BIKE) " & _
" AS FLAG_BIKE, SUM(GIS_EVENTS.F_H_TRK) AS
FLAG_TRUCK, SUM(GIS_EVENTS.F_PED) AS FLAG_PED, SUM(GIS_EVENTS.F_SPEED)
" & _
" AS FLAG_SPEED, SUM(GIS_EVENTS.F_DUI) AS
FLAG_DUI, SUM(GIS_EVENTS.F_RED_SP) AS FLAG_RED, SUM(GIS_EVENTS.F_AGR) "
& _
" AS FLAG_AGR, SUM(GIS_EVENTS.F_NIGHT) AS
FLAG_NIGHT, SUM(GIS_EVENTS.F_BEACH) AS FLAG_BEACH, " & _
" MIN(" & link_table & "." & STREET1 & ") AS
STREET1, Max(" & link_table & "." & STREET2 & ") AS STREET2 " & _
"FROM GIS_EVENTS INNER JOIN " & _
" " & link_table & " ON GIS_EVENTS." & node_type & " = " &
link_table & "." & link_field & "" & _
" WHERE GIS_EVENTS.date_ >= '" & From_Date & "' And
GIS_EVENTS.date_ <= '" & To_Date & "'" & _
" GROUP BY GIS_EVENTS." & node_type & ""


DoCmd.RunSQL sql_1 & sql_2 & sql_3

End Function
 
T

Tom Ellison

Dear Mevyn:

Access doesn't have the CASE syntax. Use:

SUM(IIf(Dirfmint = "N", 1, 0))

Does that look like it would produce the same sum?

By the way, Access provides a version of SQL Server called MSDE which would
run your original queries. The Jet engine which does not is simply another
alternative.

Tom Ellison


Hello I have a Query in a function that summarizes a pretty big table
in to summary tables in SQL Server.
With my current knowledge of Access I can't pull this off. Im not sure
I can use a case statement Ex.COUNT(CASE DIRFMINT WHEN 'N' THEN 1 ELSE
NULL END). I'm really frusterated at the limitations in access but its
useability is nice.

Here is the Code and Query





Public Function MAKE_SUB_DATE(summary_table As String, node_type As
String, link_table As String, link_field As String, STREET1 As String,
STREET2 As String, From_Date As String, To_Date As String)

Dim sql_1 As String
Dim sql_2 As String
Dim sql_3 As String


DoCmd.RunSQL "DELETE FROM " & summary_table & ""

sql_1 = " INSERT INTO " & summary_table & "" & _
" (NODE, NORTH, EAST, SOUTH, WEST, Left_Turn,
Right_Turn, Angle, Rear_End, Head_on, AT_INT, INFLUBY, NOT_AT_INT,
BRIDGE, DRIVEWAY, DAY," & _
" DARK_LIGHT, DARK_NOLIGHT, DUSK, DAWN, FATAL,
INCAP, NON_INCAP, POSSIBLE, NONE, NON_TRAFF, FATSUM, INJCNT, VEHCNT," &
_
" TOTAL_CRASHES, Yr_1989, Yr_1990, Yr_1991,
Yr_1992, Yr_1993, Yr_1994, Yr_1995, Yr_1996, Yr_1997, Yr_1998, Yr_1999,
Yr_2000, Yr_2001," & _
" Yr_2002, Yr_2003, Yr_2004, Yr_2005, Yr_2006,
Yr_2007, Yr_2008, Yr_2009, Yr_2010, Yr_2011, Yr_2012, Yr_2013, Yr_2014,
Yr_2015, Yr_2016," & _
" Yr_2017, AVG_CRASH, FLAG_BIKE, FLAG_TRUCK,
FLAG_PED, FLAG_SPEED, FLAG_DUI, FLAG_RED, FLAG_AGR, FLAG_NIGHT,
FLAG_BEACH," & _
" STREET1, STREET2) " & _
" SELECT GIS_EVENTS." & node_type & ", COUNT(CASE DIRFMINT WHEN 'N'
THEN 1 ELSE NULL END) AS NORTH," & _
" COUNT(CASE DIRFMINT WHEN 'E' THEN 1 ELSE NULL
END) AS EAST, COUNT(CASE DIRFMINT WHEN 'S' THEN 1 ELSE NULL END) AS
SOUTH," & _
" COUNT(CASE DIRFMINT WHEN 'W' THEN 1 ELSE NULL
END) AS WEST, COUNT(CASE FSTHARM1 WHEN 'LEFT-TURN' THEN 1 ELSE NULL
END)" & _
" AS LEFT_TURN, COUNT(CASE FSTHARM1 WHEN
'RIGHT-TURN' THEN 1 ELSE NULL END) AS RIGHT_TURN," & _
" COUNT(CASE FSTHARM1 WHEN 'ANGLE' THEN 1 ELSE
NULL END) AS ANGLE, COUNT(CASE FSTHARM1 WHEN 'REAR-END' THEN 1 ELSE
NULL END)" & _
" AS REAR_END, COUNT(CASE FSTHARM1 WHEN 'HEAD-ON'
THEN 1 ELSE NULL END) AS HEAD_ON," & _
" COUNT(CASE SITELOC WHEN 'AT INTERSECTION' THEN
1 ELSE NULL END) AS AT_INT," & _
" COUNT(CASE SITELOC WHEN 'INFLUENCED BY
INTERSECTION' THEN 1 ELSE NULL END) AS INFLUBY," & _
" COUNT(CASE SITELOC WHEN 'NOT AT
INTERSECTION/RRXING/BRIDGE' THEN 1 ELSE NULL END) AS NOT_AT_INT," & _
" COUNT(CASE SITELOC WHEN 'BRIDGE' THEN 1 ELSE
NULL END) AS BRIDGE, COUNT(CASE SITELOC WHEN 'DRIVEWAY' THEN 1 ELSE
NULL END)" & _
" AS DRIVEWAY, COUNT(CASE LIGHTING WHEN
'DAYLIGHT' THEN 1 ELSE NULL END) AS DAY," & _
" COUNT(CASE LIGHTING WHEN 'DARK (STREET LIGHT)'
THEN 1 ELSE NULL END) AS DARK_LIGHT," & _
" COUNT(CASE LIGHTING WHEN 'DARK (NO STREET
LIGHT)' THEN 1 ELSE NULL END) AS DARK_NOLIGHT," & _
" COUNT(CASE LIGHTING WHEN 'DUSK' THEN 1 ELSE
NULL END) AS DUSK, COUNT(CASE LIGHTING WHEN 'DAWN' THEN 1 ELSE NULL
END)" & _
" AS DAWN, COUNT(CASE ACC_SEV WHEN 'FATAL' THEN 1
ELSE NULL END) AS FATAL," & _
" COUNT(CASE ACC_SEV WHEN 'INCAPACITATING INJ'
THEN 1 ELSE NULL END) AS INCAP,"

sql_2 = " COUNT(CASE ACC_SEV WHEN 'NON_INCAP INJ' THEN 1 ELSE NULL END)
AS NON_INCAP, " & _
" COUNT(CASE ACC_SEV WHEN 'POSSIBLE INJ' THEN 1
ELSE NULL END) AS POSSIBLE, COUNT(CASE ACC_SEV WHEN 'NONE' THEN 1 ELSE
NULL " & _
" END) AS NONE, COUNT(CASE ACC_SEV WHEN
'NON-TRAFFIC FATALITY' THEN 1 ELSE NULL END) AS NON_TRAFF,
SUM(GIS_EVENTS.FATCNT) " & _
" AS FATSUM, SUM(GIS_EVENTS.INJCNT) AS INJCNT,
SUM(GIS_EVENTS.VEHCNT) AS VEHCNT, COUNT(DISTINCT GIS_EVENTS.CASEID) " &
_
" AS TOTAL_CRASHES, COUNT(CASE YEAR(DATE_) WHEN
1989 THEN 1 ELSE NULL END) AS YR_1989, COUNT(CASE YEAR(DATE_) " & _
" WHEN 1990 THEN 1 ELSE NULL END) AS YR_1990,
COUNT(CASE YEAR(DATE_) WHEN 1991 THEN 1 ELSE NULL END) AS YR_1991, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 1992 THEN 1 ELSE
NULL END) AS YR_1992, COUNT(CASE YEAR(DATE_) WHEN 1993 THEN 1 ELSE NULL
END) " & _
" AS YR_1993, COUNT(CASE YEAR(DATE_) WHEN 1994
THEN 1 ELSE NULL END) AS YR_1994, COUNT(CASE YEAR(DATE_) WHEN 1995 THEN
1 ELSE NULL " & _
" END) AS YR_1995, COUNT(CASE YEAR(DATE_) WHEN
1996 THEN 1 ELSE NULL END) AS YR_1996, COUNT(CASE YEAR(DATE_) " & _
" WHEN 1997 THEN 1 ELSE NULL END) AS YR_1997,
COUNT(CASE YEAR(DATE_) WHEN 1998 THEN 1 ELSE NULL END) AS YR_1998, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 1999 THEN 1 ELSE
NULL END) AS YR_1999, COUNT(CASE YEAR(DATE_) WHEN 2000 THEN 1 ELSE NULL
END) " & _
" AS YR_2000, COUNT(CASE YEAR(DATE_) WHEN 2001
THEN 1 ELSE NULL END) AS YR_2001, COUNT(CASE YEAR(DATE_) WHEN 2002 THEN
1 ELSE NULL " & _
" END) AS YR_2002, COUNT(CASE YEAR(DATE_) WHEN
2003 THEN 1 ELSE NULL END) AS YR_2003, COUNT(CASE YEAR(DATE_) " & _
" WHEN 2004 THEN 1 ELSE NULL END) AS YR_2004,
COUNT(CASE YEAR(DATE_) WHEN 2005 THEN 1 ELSE NULL END) AS YR_2005, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 2006 THEN 1 ELSE
NULL END) AS YR_2006, COUNT(CASE YEAR(DATE_) WHEN 2007 THEN 1 ELSE NULL
END) " & _
" AS YR_2007, COUNT(CASE YEAR(DATE_) WHEN 2008
THEN 1 ELSE NULL END) AS YR_2008, COUNT(CASE YEAR(DATE_) WHEN 2009 THEN
1 ELSE NULL " & _
" END) AS YR_2009, COUNT(CASE YEAR(DATE_) WHEN
2010 THEN 1 ELSE NULL END) AS YR_2010, COUNT(CASE YEAR(DATE_) " & _
" WHEN 2011 THEN 1 ELSE NULL END) AS YR_2011,
COUNT(CASE YEAR(DATE_) WHEN 2012 THEN 1 ELSE NULL END) AS YR_2012, " &
_
" COUNT(CASE YEAR(DATE_) WHEN 2013 THEN 1 ELSE
NULL END) AS YR_2013, COUNT(CASE YEAR(DATE_) WHEN 2014 THEN 1 ELSE NULL
END) " & _
" AS YR_2014, COUNT(CASE YEAR(DATE_) WHEN 2015
THEN 1 ELSE NULL END) AS YR_2015, COUNT(CASE YEAR(DATE_) WHEN 2016 THEN
1 ELSE NULL " & _
" END) AS YR_2016, COUNT(CASE YEAR(DATE_) WHEN
2017 THEN 1 ELSE NULL END) AS YR_2017, "


sql_3 = " ROUND(CAST(12 * COUNT(MONTH(GIS_EVENTS.date_)) AS [FLOAT]) /
CAST " & _
" ((SELECT COUNT(*) AS TOTAL_MONTHS " & _
" FROM (SELECT TOP 100
PERCENT YEAR(DATE_) AS YEAR, MONTH(DATE_) AS MONTH, COUNT(MONTH(DATE_))
AS _COUNT " & _
" FROM GIS_EVENTS
" & _
" GROUP BY
YEAR(DATE_), MONTH(DATE_) " & _
" HAVING
(Count(Month(DATE_)) > 10) " & _
" ORDER BY
YEAR(DATE_), MONTH(DATE_)) DERIVEDTBL) AS [FLOAT](5)), 2) AS AVG_CRASH,
SUM(GIS_EVENTS.F_BIKE) " & _
" AS FLAG_BIKE, SUM(GIS_EVENTS.F_H_TRK) AS
FLAG_TRUCK, SUM(GIS_EVENTS.F_PED) AS FLAG_PED, SUM(GIS_EVENTS.F_SPEED)
" & _
" AS FLAG_SPEED, SUM(GIS_EVENTS.F_DUI) AS
FLAG_DUI, SUM(GIS_EVENTS.F_RED_SP) AS FLAG_RED, SUM(GIS_EVENTS.F_AGR) "
& _
" AS FLAG_AGR, SUM(GIS_EVENTS.F_NIGHT) AS
FLAG_NIGHT, SUM(GIS_EVENTS.F_BEACH) AS FLAG_BEACH, " & _
" MIN(" & link_table & "." & STREET1 & ") AS
STREET1, Max(" & link_table & "." & STREET2 & ") AS STREET2 " & _
"FROM GIS_EVENTS INNER JOIN " & _
" " & link_table & " ON GIS_EVENTS." & node_type & " = " &
link_table & "." & link_field & "" & _
" WHERE GIS_EVENTS.date_ >= '" & From_Date & "' And
GIS_EVENTS.date_ <= '" & To_Date & "'" & _
" GROUP BY GIS_EVENTS." & node_type & ""


DoCmd.RunSQL sql_1 & sql_2 & sql_3

End Function
Click to expand...
 
M

meyvn77

Thanks.. Ill give that a shoot. I know about MSDE..but I have to use
Access due the ESRI's geodatabase format.
 
T

Tom Ellison

Dear Mevyn:

MSDE can connect to most of the same ODBC sources that Jet can.

Tom Ellison
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Numaric to Inword in excel by VB 1
converting some T-sql queries to MS ACCESS 1
Conversion from Number to Word!! Aki 1
Transform Access SQL to SQL server 2005 or SQLExpress 8
Display Numbers as Words 3
Notice few bugs 0
Macro VBA coding, Copying content from one workbook to a new one 0
Timeout issue 1

Top