Code:
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 1] AS Carrier,CCur([BusinessActivity].[Proj Amt 1]) AS Amount, [BusinessActivity].[Type 1] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp1" FROM [BusinessActivity] WHERE ([Proj amt 1] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 2] AS Carrier,CCur([BusinessActivity].[Proj Amt 2]) AS Amount, [BusinessActivity].[Type 2] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp2" FROM [BusinessActivity] WHERE ([Proj amt 2] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 3] AS Carrier, CCur([BusinessActivity].[Proj Amt 3]) AS Amount, [BusinessActivity].[Type 3] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp3" FROM [BusinessActivity] WHERE ([Proj amt 3] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 4] AS Carrier, CCur([BusinessActivity].[Proj Amt 4]) AS Amount, [BusinessActivity].[Type 4] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp4" FROM [BusinessActivity] WHERE ([Proj amt 4] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 5] AS Carrier, CCur([BusinessActivity].[Proj Amt 5]) AS Amount, [BusinessActivity].[Type 5] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp5" FROM [BusinessActivity] WHERE ([Proj amt 5] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 6] AS Carrier, CCur([BusinessActivity].[Proj Amt 6]) AS Amount, [BusinessActivity].[Type 6] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp6" FROM [BusinessActivity] WHERE ([Proj amt 6] <> Null)
UNION ALL
SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 7] AS Carrier, CCur([BusinessActivity].[Proj Amt 7]) AS Amount, [BusinessActivity].[Type 7] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp7" FROM [BusinessActivity] WHERE ([Proj amt 7] <> Null)
UNION ALL SELECT [BusinessActivity].[Pro #] AS PRO, [BusinessActivity].[Proj Exp 8] AS Carrier, CCur([BusinessActivity].[Proj Amt 8]) AS Amount, [BusinessActivity].[Type 8] As Type, CDate([BusinessActivity].[Ship Date]) AS [Ship Date], CDate([BusinessActivity].[Invoice Date]) AS [Invoice Date], CDate([BusinessActivity].[Posted Info]) AS [Post Date], "Exp8" FROM [BusinessActivity] WHERE ([Proj amt 8] <> Null);