Har följande SELECT Sats fungerar i MSDE.
Men inte om jag kör den mot Access 2000
Nån som kan hjälpa mig
SELECT TOP 100 PERCENT AVG(DISTINCT r.Resultat / r.Serier) AS Snitt, s.FNamn, s.Id, SUM(r.Serier) AS Total_serier,
(SELECT AVG(resultat / serier)
FROM resultat
WHERE Spelare = s.ID AND H_B = 'H') AS Snitt_Hemma,
(SELECT AVG(resultat / serier)
FROM resultat
WHERE Spelare = s.ID AND H_B = 'B') AS Snitt_Borta
FROM dbo.Medlemmar s INNER JOIN
dbo.Resultat r ON s.Id = r.Spelare
GROUP BY s.FNamn, s.Id
ORDER BY Snitt DESC
ACCESS:
string sqlCmd = "SELECT AVG(DISTINCT r.Resultat / r.Serier) AS Snitt";
sqlCmd+="s.Namn,SUM(r.Serier) AS Total_serier, s.Id,";
sqlCmd+="(SELECT AVG(r.resultat / r.Serier) FROM resultat WHERE Spelare = s.ID AND H_B = 'H') AS Snitt_Hemma ,";
sqlCmd+="(SELECT AVG(r.resultat / r.Serier) FROM resultat WHERE Spelare = s.ID AND H_B = 'B') AS Snitt_Borta ,";
sqlCmd+="SUM(r.Poang)/SUM(r.Serier) AS Poang_Snitt , MAX(r.Resultat) AS Basta , MIN(r.Resultat) AS Samsta ,";
sqlCmd+="(SELECT SUM(Serier) FROM resultat WHERE H_B = 'H') AS serier_hemma ,";
sqlCmd+="(SELECT SUM(Serier) FROM resultat WHERE H_B = 'B') AS Serier_Borta";
sqlCmd+="FROM Medlemmar AS s INNER JOIN Resultat AS r ON s.Id = r.Spelare";
sqlCmd+="GROUP BY s.Namn , s.Id ORDER BY s.Namn DESC";