SQL Pivot Table to Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Pivot Table to Access

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2007-12-05 : 12:46:38

Below is a Statement that works like a dream in SQL, the million dollar question is does Access have the capability to build a Pivot Table within itself like SQL does?

GC

SELECT POLICYPERIOD as 'POLICY PERIOD',
SUM ([OPENCLAIMS]) as '# OPEN CLAIMS',
SUM ([REOPENCLAIMS]) as '# REOPEN CLAIMS',
SUM ([SETTLEDCLAIMS]) as '# SETTLED CLAIMS',
SUM ([CANCELLEDCLAIMS]) as '# CANCELLED CLAIMS',
SUM (([OPENCLAIMS]) + ([REOPENCLAIMS]) + ([SETTLEDCLAIMS]) + ([CANCELLEDCLAIMS])) as '# TOTAL CLAIMS',
SUM ([PAIDMEDICAL]) as 'MEDICAL PAID LOSS',
SUM (([INCURREDMEDICAL]) - ([PAIDMEDICAL])) as 'MEDICAL RESERVE',
SUM ([INCURREDMEDICAL]) as 'MEDICAL INCURRED',
SUM ([PAIDINDEMNITY]) as 'INDEMNITY PAID LOSS',
SUM (([INCURREDINDEMNITY]) - ([PAIDINDEMNITY])) as 'INDEMNITY RESERVE',
SUM ([INCURREDINDEMNITY]) as 'INDEMNITY INCURRED',
SUM ([PAIDEXPENSE]) as 'EXPENSE PAID LOSS',
SUM (([INCURREDEXPENSE]) - ([PAIDEXPENSE])) as 'EXPENSE RESERVE',
SUM ([INCURREDEXPENSE]) as 'EXPENSE INCURRED',
SUM ([PAIDTOTAL]) as 'TOTAL PAID LOSS',
SUM (([INCURREDTOTAL]) - ([PAIDTOTAL])) as 'TOTAL RESERVE',
SUM ([INCURREDTOTAL]) as 'TOTAL INCURRED'
FROM (
SELECT
CASE
WHEN AccidentDate <= '05/31/1993' THEN 'Before 06/1/1993'
WHEN AccidentDate >= '06/01/1993' AND AccidentDate <= '05/31/1994' THEN '06/01/1993 - 05/31/1994'
WHEN AccidentDate >= '06/01/1994' AND AccidentDate <= '05/31/1995' THEN '06/01/1994 - 05/31/1995'
WHEN AccidentDate >= '06/01/1995' AND AccidentDate <= '07/31/1996' THEN '06/01/1995 - 07/31/1996'
WHEN AccidentDate >= '08/01/1996' AND AccidentDate <= '07/31/1997' THEN '08/01/1996 - 07/31/1997'
WHEN AccidentDate >= '08/01/1997' AND AccidentDate <= '07/31/1998' THEN '08/01/1997 - 07/31/1998'
WHEN AccidentDate >= '08/01/1998' AND AccidentDate <= '07/31/1999' THEN '08/01/1998 - 07/31/1999'
WHEN AccidentDate >= '08/01/1999' AND AccidentDate <= '07/31/2000' THEN '08/01/1999 - 07/31/2000'
WHEN AccidentDate >= '08/01/2000' AND AccidentDate <= '07/31/2001' THEN '08/01/2000 - 07/31/2001'
WHEN AccidentDate >= '08/01/2001' AND AccidentDate <= '07/31/2002' THEN '08/01/2001 - 07/31/2002'
WHEN AccidentDate >= '08/01/2002' AND AccidentDate <= '07/31/2003' THEN '08/01/2002 - 07/31/2003'
WHEN AccidentDate >= '08/01/2003' AND AccidentDate <= '07/31/2004' THEN '08/01/2003 - 07/31/2004'
WHEN AccidentDate >= '08/01/2004' AND AccidentDate <= '07/31/2005' THEN '08/01/2004 - 07/31/2005'
WHEN AccidentDate >= '08/01/2005' AND AccidentDate <= '07/31/2006' THEN '08/01/2005 - 07/31/2006'
WHEN AccidentDate >= '08/01/2006' AND AccidentDate <= '07/31/2007' THEN '08/01/2006 - 07/31/2007'
WHEN AccidentDate >= '08/01/2007' AND AccidentDate <= '07/31/2008' THEN '08/01/2007 - 07/31/2008'
WHEN AccidentDate >= '08/01/2008' AND AccidentDate <= '07/31/2009' THEN '08/01/2008 - 07/31/2009'
ELSE 'Future Claims'
END as 'POLICYPERIOD',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN MedicalNetPaid ELSE 0 END as 'PAIDMEDICAL',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN IndemnityNetPaid ELSE 0 END as 'PAIDINDEMNITY',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN ExpenseNetPaid ELSE 0 END as 'PAIDEXPENSE',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN TotalNetPaid ELSE 0 END as 'PAIDTOTAL',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN MedicalIncurred ELSE 0 END as 'INCURREDMEDICAL',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN IndemnityIncurred ELSE 0 END as 'INCURREDINDEMNITY',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN ExpenseIncurred ELSE 0 END as 'INCURREDEXPENSE',
CASE WHEN ClaimStatus IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED') THEN TotalIncurred ELSE 0 END as 'INCURREDTOTAL',
CASE WHEN ClaimStatus = 'OPEN' THEN 1 ELSE 0 END as 'OPENCLAIMS',
CASE WHEN ClaimStatus = 'REOPEN' THEN 1 ELSE 0 END as 'REOPENCLAIMS',
CASE WHEN ClaimStatus = 'SETTLED' THEN 1 ELSE 0 END as 'SETTLEDCLAIMS',
CASE WHEN ClaimStatus = 'CANCELLED' THEN 1 ELSE 0 END as 'CANCELLEDCLAIMS'
FROM WCVALUATIONDATA
WHERE CompanyCode = 'A'
)X
GROUP BY POLICYPERIOD WITH CUBE
ORDER BY POLICYPERIOD DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:23:34

Use TRANSFORM keyword.
There are at least four other people asking same question this week.

E 12°55'05.25"
N 56°04'39.16"

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2007-12-05 : 13:38:26

I've been playing with the code and have had success with the below code. Access isn't giving me any errors. I just need help with the DATE CASE statement.

GC

SELECT
SUM ([OPENCLAIMS]) AS 'OPEN CLAIMS',
SUM ([REOPENCLAIMS]) AS 'REOPEN CLAIMS',
SUM ([SETTLEDCLAIMS]) AS 'SETTLED CLAIMS',
SUM ([CANCELLEDCLAIMS]) AS 'CANCELLED CLAIMS',
SUM (([OPENCLAIMS]) + ([REOPENCLAIMS]) + ([SETTLEDCLAIMS]) + ([CANCELLEDCLAIMS])) AS 'TOTAL CLAIMS',
SUM ([PAIDMEDICAL]) AS 'MEDICAL PAID LOSS',
SUM (([INCURREDMEDICAL]) - ([PAIDMEDICAL])) as 'MEDICAL RESERVE',
SUM ([INCURREDMEDICAL]) as 'MEDICAL INCURRED',
SUM ([PAIDINDEMNITY]) as 'INDEMNITY PAID LOSS',
SUM (([INCURREDINDEMNITY]) - ([PAIDINDEMNITY])) as 'INDEMNITY RESERVE',
SUM ([INCURREDINDEMNITY]) as 'INDEMNITY INCURRED',
SUM ([PAIDEXPENSE]) as 'EXPENSE PAID LOSS',
SUM (([INCURREDEXPENSE]) - ([PAIDEXPENSE])) as 'EXPENSE RESERVE',
SUM ([INCURREDEXPENSE]) as 'EXPENSE INCURRED',
SUM ([PAIDTOTAL]) as 'TOTAL PAID LOSS',
SUM (([INCURREDTOTAL]) - ([PAIDTOTAL])) as 'TOTAL RESERVE',
SUM ([INCURREDTOTAL]) as 'TOTAL INCURRED'

FROM (
SELECT
IIf([ClaimStatus] In ('OPEN','REOPEN','SETTLED','CANCELLED'),[MedicalNetPaid],0) AS PAIDMEDICAL,
IIf([ClaimStatus] In ('OPEN','REOPEN','SETTLED','CANCELLED'),[IndemnityNetPaid],0) AS PAIDINDEMNITY,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[ExpenseNetPaid],0) AS PAIDEXPENSE,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[TotalNetPaid],0) AS PAIDTOTAL,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[MedicalIncurred],0) AS INCURREDMEDICAL,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[IndemnityIncurred],0) AS INCURREDINDEMNITY,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[ExpenseIncurred],0) AS INCURREDEXPENSE,
IIF([ClaimStatus] IN ('OPEN', 'REOPEN', 'SETTLED', 'CANCELLED'),[TotalIncurred],0) AS INCURREDTOTAL,
IIF([ClaimStatus] = 'OPEN',1,0) AS OPENCLAIMS,
IIF([ClaimStatus] = 'REOPEN',1,0) AS REOPENCLAIMS,
IIF([ClaimStatus] = 'SETTLED',1,0) AS SETTLEDCLAIMS,
IIF([ClaimStatus] = 'CANCELLED',1,0) AS CANCELLEDCLAIMS
FROM losttime_WCVALUATIONDATA
WHERE (((losttime_WCVALUATIONDATA.CompanyCode)='AA'))
)X

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-08 : 09:03:40

JET doesn't support CASE statements. Look into Iif() statements instead :)

Or alternatively just use TRANSFORM

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources