I have an sql query which I am trying to change over to access. I keep on getting stuck. Below is the sql query which works fine and then I also show as far as I got to in the access conversion.
SQL
SELECT V.NAME, SUM(CASE WHEN I.TYPE2 = 'FPR' THEN QTY ELSE 0 END) AS FPR,
SUM(CASE WHEN I.TYPE2 = 'LOW' THEN QTY ELSE 0 END) AS LOW,
SUM(CASE WHEN I.TYPE2 = 'DEA' THEN QTY ELSE 0 END) AS DEALS,
ISNULL(SUM(A.QTY),0)AS TOTAL ,
ROUND(ISNULL(100 * SUM(A.QTY)/(SUM(SUM(A.QTY)) OVER()),0),2) AS [PERCENT OF TOTAL],
SUM(SUM(A.QTY))OVER() AS [GRAND TOTAL]
FROM ARD A JOIN INV I ON A.ITEM = I.ITEM AND A.DATE BETWEEN '6-30-15' AND '7-1-15'
RIGHT JOIN VENDORS V ON V.VENDORNUM =
CASE WHEN I.VENDOR = '26100' THEN
CASE WHEN I.DESCRIPTION LIKE 'AB%' THEN '12300'
WHEN I.DESCRIPTION LIKE 'CD%' THEN '12300'
WHEN I.DESCRIPTION LIKE 'EF%' THEN '12300'
WHEN I.DESCRIPTION LIKE 'GH%' THEN '123'
END
WHEN I.VENDOR IN('90500', '90600') THEN '90600'
WHEN I.VENDOR IN( '12300','27200','01301')
THEN CASE
WHEN I.DESCRIPTION LIKE 'IJ%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'KL%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'MN%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'OP%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'QR%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'ST%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'UV%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'WX%' THEN I.VENDOR
WHEN I.DESCRIPTION LIKE 'YZ%' THEN '123'
END
ELSE I.VENDOR
END
WHERE NAME = '123' OR NAME LIKE '456%' OR NAME LIKE '789%'OR NAME LIKE '10%'OR NAME LIKE
GROUP BY NAME
ORDER BY NAME
ACCESS
SELECT VENDORS.NAME, SUM(IFF (INV.TYPE2 = 'FPR', QTY, 0))AS FPR, SUM(IFF (INV.TYPE2 = 'LOW', QTY, 0))AS LOW, SUM(IFF (INV.TYPE2 = 'DEA', QTY, 0))AS DEALS,ISNULL(SUM(ARD.QTY),0)AS TOTAL
FROM( ARD
INNER JOIN INV
ON (ARD.ITEM = INV.ITEM))
RIGHT JOIN VENDORS ON VENDORS.VENDORNUM =
SWITCH(
INV.VENDOR = '26100',SWITCH(
INV.DESCRIPTION LIKE 'AB*', '12300',
INV.DESCRIPTION LIKE 'CD*' , '12300',
INV.DESCRIPTION LIKE 'EF*' , '12300',
INV.DESCRIPTION LIKE 'GH*' , '123'
),
INV.VENDOR IN('90500', '90600') , '90600' ,
INV.VENDOR IN( '12300','27200','01301') ,SWITCH(
INV.DESCRIPTION LIKE 'IJ*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'KL*', INV.VENDOR,
INV.DESCRIPTION LIKE 'MN*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'OP*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'QR*' , INV.VENDOR ,
INV.DESCRIPTION LIKE 'ST*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'UV*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'WX*' , INV.VENDOR,
INV.DESCRIPTION LIKE 'YZ*' , '123'
),
INV.VENDOR
)
WHERE NAME = '123' OR NAME LIKE '456*' OR NAME LIKE '789*'OR NAME LIKE '10*'
GROUP BY NAME
ORDER BY NAME;
Other recent topics
