Newbie Developer Newbie Developer - 3 months ago 10
SQL Question

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

Answer

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)