Thanks. That's alot better.

I hate the way Access formats SQL.



>>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.