convert access queries to sql server(T-SQL)

[srim]

Hi,

I'm trying to convert all the queries in Access database and the queries in the VB program to T-SQL(sql server) format. This is because we are moving the database from access to sql server. I need to know how to change the following queries to t-sql:

1.UPDATE table1 AS a RIGHT JOIN table2 AS b ON (a.inv=b.inv) AND (a.pro=b.pro) SET a.pro = b.pro, a.inv = b.inv, a.chp = b.chp;

2.UPDATE(table1 AS a INNER JOIN table2 AS b ON a.inv=b.inv)
INNER JOIN table3 AS c ON a.pro=c.pro SET a.up=b.up*c.prc
WHERE a.inv> #" & date1 & "#;"
3.UPDATE table1 AS a INNER JOIN table2 AS b ON
Format(a.in,"mm/yy&quot=Format(b.in,"mm/yy&quot SET a.pro =b.pro;


Any help would be great.
Thanks,
Srim

 

[ClaireHCM]

1.
update table1 set table1.pro = table2.pro,table1.inv = table2.inv ,table1.chp = table2.chp from table1 right join table2 on
table1.inv = table2.inv and table1.pro = table2.pro


2.
UPDATE table1 set table1.up = table2.up*table3.prc
from table1
INNER JOIN table2 ON table1.inv = table2.inv
INNER JOIN table3 ON table1.pro = table3.pro
WHERE table1.inv> #" + date1 + "#"

3.
UPDATE table1 set table1.pro = table2.pro
from table1INNER JOIN table2 ON Format(table1.in,"mm/yy&quot=Format(table2.in,"mm/yy&quot

The first one should work.But for the 2nd and 3rd ones ,could you explain what do you want to express by [WHERE table1.inv> #" + date1 + "#"] and format?
what is the datatype of "in" column?

 

[srim]

Datatype is date/time in access and in where condition it is looking for the date > date1.

Do we have to split UPDATE statement with RIGHT JOIN into
2 statements or does it work has you mentioned.

Thanks,
Srim

 

[ClaireHCM]

Try this,I assumen table1.inv , table1.in and table2.in are in datetime field

Is date1 is a datetime value?
2.
UPDATE table1 set table1.up = table2.up*table3.prc
from table1
INNER JOIN table2 ON table1.inv = table2.inv
INNER JOIN table3 ON table1.pro = table3.pro
WHERE table1.inv> date1

3.
UPDATE table1 set table1.pro = table2.pro from table1INNER JOIN table2 ON datepart(mm,table1.in)+datepart(yy,table1.in)=datepart(mm,table2.in)+datepart(yy,table2.in)