>I have this Access query and I'm trying to convert it to SQL. The Access query is using tables linked to the same SQL tables. In SQL I get 3303 rows. In Access I get 5814 rows. Anyone wanna take a stab at it
>
>

>SELECT	Date() AS [Snapshot Date], 
>		PK_OL.OL_PART_NO AS [Item #], 
>		Trim(PK_OL!OL_PHKEY) AS [PO #], 
>		PK_OL.OL_REF_NO AS [PO Line], 
>		PK_OL.OL_PRICE AS [PO Price], 
>		PK_PH.PH_VNKEY AS [PO Vendor], 
>		Trim(PK_OL!OL_VEND_DESCR) AS [PO Line Mfr], 
>		Trim(PK_OL!OL_VEND_PART_NO) AS [PO Line MPN], 
>		PK_MOS.MOS_WANTDATE AS [PO Del Date], 
>		PK_MOS.MOS_QTY AS [PO Del Qty], 
>		IIf(PK_MOS!MOS_QTY>PK_MOS!MOS_QTY_REC,PK_MOS!MOS_QTY-PK_MOS!MOS_QTY_REC,0) AS [PO Del Qty Unreceived], 
>		IIf(PK_MOS!MOS_QTY>PK_MOS!MOS_QTY_REC,PK_MOS!MOS_QTY-PK_MOS!MOS_QTY_REC,0) AS [PO Del Qty Open], 
>		0 AS [PO Del Qty In Rcvg Insp], 
>		PK_MOS.MOS_SEQNUM AS [PO Del SeqNum], 
>		IIf(PK_MOS!MOS_STATUS<3,"OPEN",IIf(PK_MOS!MOS_STATUS=3,"CLOSED","CANCELLED")) AS [PO Del Status], 
>		IIf(PK_OL!OL_STATUS<3,"OPEN",IIf(PK_OL!OL_STATUS=3,"CLOSED","CANCELLED")) AS [PO Line Status], 
>		IIf(PK_MOS!MOS_WANTDATE=#12/31/2029#,True,False) AS [Placeholder?], 
>		1 AS [Fake Field] 
>	INTO [DW Purchase Orders]
>	FROM (PK_PH INNER JOIN PK_OL ON PK_PH.PH_KEY = PK_OL.OL_PHKEY) INNER JOIN PK_MOS ON (PK_OL.OL_REF_NO = PK_MOS.MOS_OLREFNO) AND (PK_OL.OL_PHKEY = PK_MOS.MOS_OLPHKEY)
>	WHERE (((IIf([PK_MOS]![MOS_STATUS]<3,"OPEN",IIf([PK_MOS]![MOS_STATUS]=3,"CLOSED","CANCELLED")))="OPEN") AND 
>		  ((IIf([PK_OL]![OL_STATUS]<3,"OPEN",IIf([PK_OL]![OL_STATUS]=3,"CLOSED","CANCELLED")))="OPEN")) OR 
>			(((PK_MOS.MOS_WANTDATE)>Date()-365) AND ((IIf([PK_MOS]![MOS_STATUS]<3,"OPEN",IIf([PK_MOS]![MOS_STATUS]=3,"CLOSED","CANCELLED")))="CLOSED")) OR 
>			(((PK_MOS.MOS_WANTDATE)>Date()-365) AND ((IIf([PK_OL]![OL_STATUS]<3,"OPEN",IIf([PK_OL]![OL_STATUS]=3,"CLOSED","CANCELLED")))="CLOSED"))
>	ORDER BY PK_OL.OL_PART_NO, Trim(PK_OL!OL_PHKEY), PK_OL.OL_REF_NO, PK_MOS.MOS_WANTDATE;

>
>The columns are fine but the row count is off. I can't see to get the WHERE clause right. Here's what I came up with:
>
>

>SELECT	GETDATE() AS Snapshot_Date, 
>		OL.OL_PART_NO,										-- AS Item_No, 
>		LTRIM(RTRIM(OL.OL_PHKEY)) AS OL_PHKEY,				-- AS PO_No, 
>		OL.OL_REF_NO,										-- AS PO_Line, 
>		OL.OL_PRICE,										-- AS PO_Price, 
>		PH.PH_VNKEY,										-- AS PO_Vendor, 
>		RTRIM(OL.OL_VEND_DESCR) AS OL_VEND_DESCR,			-- AS PO_Line_Mfr, 
>		RTRIM(OL.OL_VEND_PART_NO) AS OL_VEND_PART_NO,		-- AS PO_Line_MPN, 
>		MOS.MOS_WANTDATE,									-- AS PO_Del Date], 
>		MOS.MOS_QTY,										-- AS [PO Del Qty], 
>
>		CASE 
>			WHEN MOS.MOS_QTY > MOS.MOS_QTY_REC 
>			THEN MOS.MOS_QTY - MOS.MOS_QTY_REC 
>			ELSE 0 
>		END AS PO_Del_Qty_Unreceived,
>
>		CASE
>			WHEN MOS.MOS_QTY > MOS.MOS_QTY_REC
>			THEN MOS.MOS_QTY - MOS.MOS_QTY_REC
>			ELSE 0
>		END AS PO_Del_Qty_Open,
>		
>		0 AS PO_Del_Qty_In_Rcvg_Insp, 
>		MOS.MOS_SEQNUM,										-- AS [PO Del SeqNum], 
>
>		CASE 
>			WHEN MOS.MOS_STATUS < 3 THEN 'OPEN' 
>			WHEN MOS.MOS_STATUS = 3 THEN 'CLOSED' 
>			WHEN MOS.MOS_STATUS > 3 THEN 'CANCELLED'
>		END AS PO_Del_Status,
>
>		CASE 
>			WHEN OL.OL_STATUS < 3 THEN 'OPEN'
>			WHEN OL.OL_STATUS = 3 THEN 'CLOSED' 
>			WHEN OL.OL_STATUS > 3 THEN 'CANCELLED'
>		END AS PO_Line_Status,
>
>		CASE WHEN MOS.MOS_WANTDATE = '12/31/2029' THEN 1 ELSE 0 END AS IsPlaceholder
>
>	FROM PK5.dbo.PH 
>	INNER JOIN PK5.dbo.OL ON PH.PH_KEY = OL.OL_PHKEY
>	INNER JOIN PK5.dbo.MOS ON OL.OL_REF_NO = MOS.MOS_OLREFNO AND 
>							  OL.OL_PHKEY = MOS.MOS_OLPHKEY
>	WHERE (MOS.MOS_STATUS < 3 AND OL.OL_STATUS < 3) OR
>		  (MOS.MOS_WANTDATE > dateadd(day,-365,GETDATE()) AND MOS.MOS_STATUS = 3) OR
>		  (MOS.MOS_WANTDATE > dateadd(day,-365,GETDATE()) AND OL.OL_STATUS = 3)
>	ORDER BY OL.OL_PART_NO, RTRIM(OL.OL_PHKEY), OL.OL_REF_NO, MOS.MOS_WANTDATE
>

See my slight changes inside.