CODEWITHSUNDEEP
sqlsql-serverms-access
Newbie Developer
Newbie Developer

Reputation: 125

Convert Ms sqlserver query to Ms Access?

I have this query on sql server which i use in my reporting services now i need to convert it to local report(rdlc) and use ms access as backend. 

WITH SuperSelect AS 

(  SELECT d.CaseNumber AS 'CASE NO.', 
          'ODU' AS MATERIAL, d.ItemNumber AS 'BOM NO.', 
          h.ModelNumber AS 'P/N',
          h.Description AS 'DESCRIPTION', 
          d.Quantity AS 'QTY',
          'PCS' AS UOM, 
          CAST(d.Quantity AS int) * 5.9 AS 'GW(KG)', 
          CAST(d.Quantity AS int) * 5 AS 'NW(KG)',
          '0.35*0.35*0.22' AS 'MEASUREMENT(CBM)',         
          d.Batch AS 'PL', 
          d.ContractNumber AS 'Contract Number', 
          d.Consignee AS 'Consignee',
          d.Destination AS 'Destination', 
          d.SO_Number AS 'Invoice',
          d.PO_Number AS 'PO Number', 
          d.Shiplist_Qty AS 'Total Quantity'
,(

  SELECT COUNT (CaseNumber)as CaseNumber 
    FROM 
    (
    SELECT DISTINCT (CaseNumber) FROM TableA d
         INNER JOIN TableB h 
                 ON d.ItemNumber = h.ItemNumber
              WHERE (d.Batch =@BatchCode)) as countCase) AS CountCase
               FROM DropshipPackinglist 
         INNER JOIN h 
                 ON d.ItemNumber = h.ItemNumber
              WHERE (d.Batch =@BatchCode)
    )

    Select *,Sum([QTY]) OVER (partition BY ss.[CASE NO.])AS'TOTALVOLUME',CASE 
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 31 AND  sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 36 THEN '1090x730x1460' 
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 25 AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 30 THEN '1090x730x1230'
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 19 AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 24 THEN '1090x730x1000'
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 13 AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 18 THEN '1090x730x780' 
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 7 AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 12 THEN '1090x730x570' 
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 3 AND  sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 6 THEN '1090x730x350'
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 1 AND  sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 2 THEN '570x400x420'
           WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 0 AND  sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 1 THEN '350x350x220'
      ELSE 'Unkown' END AS 'TOTAL VOLUME (MM3)'


  FROM  SuperSelect ss

Thanks in Regards

Upvotes: 2

Views: 2403

Answers (1)

JustinStolle
JustinStolle

Reputation: 4410

Common table expressions (the WITH block) and the CAST functions do not exist within Access. You'll have to basically rewrite this as separate queries within Access to get the same results you would in SQL Server. Of course, the concept of partitioning is beyond what Access can do with any built-in function. You'll likely need a series of nested IIf functions and a lot of other tools to make this bad boy work in Access. Take a long look around your company to find anyone with experience writing Access queries.

However, it sounds like your data is not moving, just the query, is that right? A better suggestion would be to make this into a View in SQL Server and then add it as a Linked Table (Access can create a virtual table out of an external table or view), then you can write Queries in Access based on this result. (Example here)

Upvotes: 5

Related Questions