Converting to Access from SQL

[mattm31]

i have this code that i've compiled in SQL and
it works fine.

But i have now been told the webpage its been
written for is using an Access db and not SQL.

Can someone give me some pointers on
what code i need to change to get it
working with Access & Frontpage.

Thanks

Select a.Team, sum(a.HP) as TP, sum(a.HW5) as W5,sum(a.HW4) as W4,sum(a.HT3) as T3,sum(a.HA2) as A2, sum(a.HD1) as D1, sum(a.HL0) as L0,  sum(a.POINTS) as POINTS
from
(
SELECT Team_list.Team AS Team, Count(LCL.home_team)AS HP,
Sum(Case LCL.home_points When '5' Then 1 Else 0 END)AS HW5,
Sum(Case LCL.home_points When '4' Then 1 Else 0 END)AS HW4,
Sum(Case LCL.home_points When '3' Then 1 Else 0 END)AS HT3,
Sum(Case LCL.home_points When '2' Then 1 Else 0 END)AS HA2,
Sum(Case LCL.home_points When '1' Then 1 Else 0 END)AS HD1,
Sum(Case LCL.home_points When '0' Then 1 Else 0 END)AS HL0,
Sum(LCL.home_points)AS Points
FROM LCL INNER JOIN Team_list ON LCL.Home_Team = Team_List.team
Where LCL.division = '1' and LCL.season = '2006' and LCL.comp = 'league' and LCL.Home_Score IS NOT NULL
GROUP BY Team_List.Team

UNION
SELECT Team_list.Team AS Team, Count(LCL.away_team)AS AP,
Sum(Case LCL.away_points When '5' Then 1 Else 0 END)AS AW5,
Sum(Case LCL.away_points When '4' Then 1 Else 0 END)AS AW4,
Sum(Case LCL.away_points When '3' Then 1 Else 0 END)AS AT3,
Sum(Case LCL.away_points When '2' Then 1 Else 0 END)AS AA2,
Sum(Case LCL.away_points When '1' Then 1 Else 0 END)AS AD1,
Sum(Case LCL.away_points When '0' Then 1 Else 0 END)AS AL0,
Sum(LCL.away_points)AS Points
FROM LCL INNER JOIN Team_list ON LCL.Away_Team = Team_List.team
Where LCL.division = '1' and LCL.season = '2006' and LCL.comp = 'league'  and LCL.Home_Score IS NOT NULL
GROUP BY Team_List.Team
) a
GROUP BY a.Team
Order By a.Points DESC, a.W5 DESC, a.W4, a.T3, a.A2, a.D1, a.L0

 

[gmmastros]

Access does not have a CASE statement, but it does have IIF which is almost as good. So, to replace the case statements, you'll want to use something like this...

Case LCL.away_points When '5' Then 1 Else 0 END

to

IIF(LCL.away_points = '5', 1, 0)


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mattm31]

OK i've changed those

Select a.Team, sum(a.HP) as TP, sum(a.HW5) as W5,sum(a.HW4) as W4,sum(a.HT3) as T3,sum(a.HA2) as A2, sum(a.HD1) as D1, sum(a.HL0) as L0,  sum(a.POINTS) as POINTS
from
(
SELECT Team_list.Team AS Team, Count(fixtures.home_team)AS HP,
Sum(IIF(fixtures.Home_points = '5', 1, 0)AS HW5,
Sum(IIF(fixtures.home_points = '4', 1 ,0)AS HW4,
Sum(IIF(fixtures.home_points = '3', 1 ,0)AS HT3,
Sum(IIF(fixtures.home_points = '2', 1 ,0)AS HA2,
Sum(IIF(fixtures.home_points = '1', 1 ,0)AS HD1,
Sum(IIF(fixtures.home_points = '0', 1 ,0)AS HL0,
Sum(fixtures.home_points)AS Points
FROM fixtures INNER JOIN Team_list ON fixtures.Home_Team = Team_List.team
Where fixtures.division = '1' and fixtures.season = '2006' and fixtures.comp = 'league' and fixtures.Home_Score IS NOT NULL
GROUP BY Team_List.Team

UNION
SELECT Team_list.Team AS Team, Count(fixtures.away_team)AS AP,
Sum(IIF(fixtures.away_points = '5' ,1 ,0)AS AW5,
Sum(IIF(fixtures.away_points = '4' ,1 ,0)AS AW4,
Sum(IIF(fixtures.away_points = '3' ,1 ,0)AS AT3,
Sum(IIF(fixtures.away_points = '2' ,1 ,0)AS AA2,
Sum(IIF(fixtures.away_points = '1' ,1 ,0)AS AD1,
Sum(IIF(fixtures.away_points = '0' ,1 ,0)AS AL0,
Sum(fixtures.away_points)AS Points
FROM fixtures INNER JOIN Team_list ON fixtures.Away_Team = Team_List.team
Where fixtures.division = '1' and fixtures.season = '2006' and fixtures.comp = 'league'  and fixtures.Home_Score IS NOT NULL
GROUP BY Team_List.Team
) a
GROUP BY a.Team
Order By a.Points DESC, a.W5 DESC, a.W4, a.T3, a.A2, a.D1, a.L0

And i get this error.

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)