[AccessD] Add criteria to query

[Kaup, Chester]

Good point. Better than what I tried in the past with using color.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, October 09, 2019 4:49 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Add criteria to query

[This email message was received from the Internet and came from outside of Kinder Morgan]


>  I then added criteria for Sym Code of  "infill". The issue is all records
of Sym Code "infill" are being returned. Your assistance appreciated.

If you split your SQL into lines and ident on brackets it makes it much easier to identify the 
problems caused by incorrect bracketing of complex conditions:

The "infill" condition is only being applied to the Last_Status condition inside the outer "OR"
If the first half of the condition is met, the "infill" condition is not tested.


HAVING 
(
	(
		([tbl Petra Current Status Copy].[Sym Code]) Not Like "PA*" 
		And 
		([tbl Petra Current Status Copy].[Sym Code])<>"P&A"
		And 
		([tbl Petra Current Status Copy].[Sym Code])<>"side"
	)
)

OR 

(
	(
		(dbo_DSS_LastStatus.Status)="TA" 
		Or
		(dbo_DSS_LastStatus.Status)="P&A"
	) 
		AND 
		(
			([tbl Petra Current Status Copy].[Sym Code])="infill"
		)
);





On 9 Oct 2019 at 14:05, Kaup, Chester wrote:

> SELECT dbo_DSS_LastStatus.PID, dbo_DSS_LastStatus.Completion_Name,
> dbo_DSS_LastStatus.Status, [tbl Petra Current Status Copy].[Sym Code]
> INTO [tbl Petra and DSS Status] FROM (dbo_DSS_LastStatus INNER JOIN
> dbo_DSS_CompletionMaster ON dbo_DSS_LastStatus.PID =
> dbo_DSS_CompletionMaster.PID) INNER JOIN [tbl Petra Current Status
> Copy] ON dbo_DSS_CompletionMaster.ParentPID = [tbl Petra Current
> Status Copy].[UWI (APINum)] GROUP BY dbo_DSS_LastStatus.PID,
> dbo_DSS_LastStatus.Completion_Name, dbo_DSS_LastStatus.Status, [tbl
> Petra Current Status Copy].[Sym Code] HAVING ((([tbl Petra Current
> Status Copy].[Sym Code]) Not Like "PA*" And ([tbl Petra Current Status
> Copy].[Sym Code])<>"P&A" And ([tbl Petra Current Status Copy].[Sym
> Code])<>"side")) OR (((dbo_DSS_LastStatus.Status)="TA" Or
> (dbo_DSS_LastStatus.Status)="P&A") AND (([tbl Petra Current Status
> Copy].[Sym Code])="infill"));


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com