Board index MS SQL Server

help.. converting query from MS Access to SQL

help.. converting query from MS Access to SQL

Post by Stacey Levin » Fri, 15 Nov 2002 00:10:17



Please help. I have the following query that runs in access and I need to
make it run directly on SQL Server 2K. I did not build this access query and
I have never used a join quite like this. Any help would be appreciated.

UPDATE tblItm

INNER JOIN (ItmStr INNER JOIN StrReqRvw ON

(StrReqRvw.StNo = ItmStr.StrNo) AND

(ItmStr.UPC = StrReqRvw.UPC)) ON tblItm.UPC = StrReqRvw.UPC

SET ItmStr.StrReqQty = 0

WHERE (((StrReqRvw.ReqDt) Like '11/04/02')

AND ((tblItm.StckFlg)<>"SQ" And (tblItm.StckFlg)<>"PR"));

Thanks

Stacey

 
 
 
Top

help.. converting query from MS Access to SQL

Post by Anith Se » Fri, 15 Nov 2002 00:23:09


Try:

UPDATE ItmStr
   SET StrReqQty = 0
  FROM ItmStr
 INNER JOIN StrReqRvw
    ON StrReqRvw.StNo = ItmStr.StrNo
   AND StrReqRvw.UPC = ItmStr.UPC
 INNER JOIN tblItm
    ON tblItm.UPC = StrReqRvw.UPC
 WHERE StrReqRvw.ReqDt = '11/04/02'
   AND tblItm.StckFlg NOT IN ('SQ', 'PR')

--
- Anith

 
 
 
Top

help.. converting query from MS Access to SQL

Post by Stacey Levin » Fri, 15 Nov 2002 00:35:14


THanks.. that runs..

Stacey

Quote:> Try:

> UPDATE ItmStr
>    SET StrReqQty = 0
>   FROM ItmStr
>  INNER JOIN StrReqRvw
>     ON StrReqRvw.StNo = ItmStr.StrNo
>    AND StrReqRvw.UPC = ItmStr.UPC
>  INNER JOIN tblItm
>     ON tblItm.UPC = StrReqRvw.UPC
>  WHERE StrReqRvw.ReqDt = '11/04/02'
>    AND tblItm.StckFlg NOT IN ('SQ', 'PR')

> --
> - Anith

 
 
 
Top

1. Converting MS Access Query to MS SQL Query

Hi,
  My MS Access Query uses Forms. Please let me know what
is the equivalent MS SQL Query. The problem is in the
WHERE statement.

MS Access Query:
SELECT tblInvoice.InvoiceNo, tblCharge.CktCode,
tblCharge.ChargeType, tblCharge.Description,
tblCharge.UnitRate, tblCharge.Units, tblCharge.Time,
tlkpChargeType.ChargeType AS TblChgTypeChargeType
FROM (tlkpChargeClass INNER JOIN tlkpChargeType ON
tlkpChargeClass.ChargeClass = tlkpChargeType.ChargeClass)
INNER JOIN (((tblBAN INNER JOIN (tblASR INNER JOIN
tblCircuit ON tblASR.ASRID = tblCircuit.ASRID) ON
tblBAN.BAN = tblASR.BAN) INNER JOIN tblInvoice ON
tblBAN.BAN = tblInvoice.BAN) INNER JOIN tblCharge ON
tblCircuit.CktCode = tblCharge.CktCode) ON
tlkpChargeType.ChargeType = tblCharge.ChargeType;
WHERE (((tblCircuit.ActiveDt)<=[forms]!
[frmInvoiceProcessing].[ocxCal]) AND
((tblCircuit.DisconnectDt) Is Null Or
(tblCircuit.DisconnectDt)>DateAdd("m",-1,[forms]!
[frmInvoiceProcessing]![ocxCal])));

Thanks,
C.R.Amaran

2. Accessing SQL 7.0 Data with SQL 8.0 Enterprise Manager

3. Need help converting MS ACCESS query to SQL 6.5

4. Sybase memory limits under Solaris 2.6

5. Help Convert MS Access SQL to MS SQL Server 6.5

6. Statistics

7. Converting MS Access Query to SQL server query

8. Simple SQL Query ? How To ?

9. Converting SQL-query from Access to MS-SQL Stored procedure

10. How to convert ms.access queries to T-SQL to suite the sql

11. converting access query to ms-sql view or stored procedure

12. Problems With the Converting MS-Access Queries to SQL-Server Stored Procedures

13. Converting MS Access query to SQL


All times are UTC
Board index