MS Query
Har et spørgsmål vedr. SQL, og håber nogen kan hjælpe.I Excel har jeg oprettet forrbindelse til database i XAL via MS Query, og det virker efter hensigten med denne SQL kode:
SELECT STOCKTABLE.ITEMGROUP, STOCKTABLE.ITEMNUMBER AS 'Varenr', STOCKTABLE.ITEMNAME AS 'Beskrivelse', STOCKTABLE.COSTPRICE AS 'Kostpris', Avg(STOCKSUM.ENTEREDQTY) AS 'Antal', STOCKTABLE.DATASET, STOCKSUM.DATASET, STOCKSUM.STOCKLOC AS 'Lager', U9_FILES.FILETEXT, STOCKTABLE.DELIVERYTIME AS 'Levering', STOCKTABLE.RE_MA_BOOKING AS 'Booking', U9_FILES.REFFILEID, U9_FILES.REFRECID, U9_FILES.PATHFILENAME AS 'Billedfil', STOCKTABLE.ROWNUMBER
FROM XALDRIFT.dbo.STOCKSUM STOCKSUM, XALDRIFT.dbo.STOCKTABLE STOCKTABLE, XALDRIFT.dbo.U9_FILES U9_FILES
WHERE STOCKSUM.ITEMNUMBER = STOCKTABLE.ITEMNUMBER AND STOCKTABLE.ROWNUMBER = U9_FILES.REFRECID
GROUP BY STOCKTABLE.ITEMGROUP, STOCKTABLE.ITEMNUMBER, STOCKTABLE.ITEMNAME, STOCKTABLE.COSTPRICE, STOCKTABLE.DATASET, STOCKSUM.DATASET, STOCKSUM.STOCKLOC, U9_FILES.FILETEXT, STOCKTABLE.DELIVERYTIME, STOCKTABLE.RE_MA_BOOKING, U9_FILES.REFFILEID, U9_FILES.REFRECID, U9_FILES.PATHFILENAME, STOCKTABLE.ROWNUMBER, STOCKSUM.ENTEREDQTY
HAVING (STOCKTABLE.DATASET='DAT') AND (STOCKSUM.ENTEREDQTY>0) AND (STOCKSUM.DATASET='dat') AND (STOCKTABLE.COSTPRICE>12600) AND (STOCKTABLE.RE_MA_BOOKING=1) AND (U9_FILES.REFFILEID=11)
ORDER BY STOCKTABLE.ITEMGROUP, STOCKTABLE.ITEMNUMBER
Men jeg ønsker at databasen U9_FILES kun skal vise data når U9_FILES.FILETEXT = 'Billedfil'.
Databaserne STOCKTABLE og STOCKSUM skal vise alle data der er inden for de eksisterende kriterier.
Nogen der kan knække den?