Converting MS Access SQL statements to MS SQL statements

[scsusan]

Anyone know how to convert this SQL statement from Access to MS SQL? I have searched the web for answers but is still unable to come up with a solution.

SELECT SUM([WI].[Weight]) AS TotalWeight, SUM([WI].[WI]) AS TotalWI, Round(TotalWI/TotalWeight,1) AS [Index]
FROM WI
WHERE [WI].[L1]=1 And [WI].[L2]=1 And [WI].[L3]=1;

I intend to create a view in MS SQL with the MS Access SELECT statement like this.

CREATE VIEW KC AS
SELECT SUM([WI].[Weight]) AS TotalWeight, SUM([WI].[WI]) AS TotalWI, Round(TotalWI/TotalWeight,1) AS [Index]
FROM WI
WHERE [WI].[L1]=1 And [WI].[L2]=1 And [WI].[L3]=1

It gives me the Invalid Column Error.

Thanks. Any help will be greatly appreciated.

Regards,
Susan

 

[BluJag]

Susuan, your SUM([WI].[WI]) looks strange to me. Looks like you're trying to multiply a table by itself. Also, since WI is the only table referenced, you can skip referring to it.

SELECT SUM(Weight) AS TotalWeight, SUM(WI) AS TotalWI, Round(TotalWI/TotalWeight,1) AS Index
FROM WI
WHERE (L1=1 And L2=1 And L3=1)

BUT - I'm not sure how SUM(WI) is supposed to work - it doesn't look right to me.