Query help: Access 2000 to SQL7

[timmah13]

I'm in the process of converting my VB app from Access 2000 to MSDE 1.0 (SQL Server 7). I've been modifying the queries as necessary, but have one question for which I can't seem to find an answer. Here's a query example:

SELECT E1.EmpNo, E2.EmpNo
FROM (Trxn_Mast AS TM
LEFT JOIN Employee AS E1 ON TM.EmpID1 = E1.ID)
LEFT JOIN Employee AS E2 ON TM.EmpID2 = E2.ID
WHERE TM.ID = 1323

In Access, the result set returns field names [E1.EmpNo] and [E2.EmpNo], but SQL returns [EmpNo] and [Column0]. Is there any way for SQL to "auto-alias" using the format [TABLE_NAME.FIELD_NAME]? This would greatly reduce the coding changes on many queries. Thanks in advance.

 

[lespaul]

You can declare the field Names yourself in the SQL:

SELECT E1.EmpNo As E1.EmpNo, E2.EmpNo As E2.EmpNo


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[timmah13]

Thanks lespaul, but I'm trying to avoid having to explicitly alias every field name. That's why I was curious whether it's possible in SQL to change the way field names are "auto-aliased" as in [TABLE_NAME.FIELD_NAME].

 

[srim]

Hi timmah13,

I am also in the same process of converting queries from Access 2000 to SQL Server 2000. Have you gone through
converting update statements with right join from access
to sql server. If so please let me know how to convert the
following:


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;

Thanks,
Srim