Complex WHERE clause

[Edgar Thoemmes]

Hi

I have the following SQL which I need to amend to do the following

1. Exclude all records where [GL Codes].[GL Account] = 4301005 and [SAP ESS
Line Items by Exp Cat].LocCurrAmt is less than 75

2. Exclude all records where [GL Codes].[GL Account] = 4301001 and [SAP ESS
Line Items by Exp Cat].LocCurrAmt is less than 75

Can anyone help with the following? I can't seem to get the syntax correct.

SELECT [SAP ESS Line Items by Exp Cat].Trip, [SAP ESS Line Items by Exp
Cat].Persno, [SAP ESS Line Items by Exp Cat].[Employee/appname], [SAP ESS
Line Items by Cost Centre].[Cost ctr], [Cost Centre].[Cost Centre Name],
[Cost Centre].[Chargeout Business Unit], [Cost Centre].Division, [SAP ESS
Line Items by Cost Centre].Order, [SAP ESS Line Items by Exp Cat].ExpTy, [SAP
ESS Line Items by Exp Cat].[Name of expense type], [GL Codes].[GL Account],
[GL Codes].[GL Account Name], [SAP ESS Line Items by Exp Cat].LocCurrAmt,
[SAP ESS Line Items by Exp Cat].Curr, [SAP ESS Line Items by Exp
Cat].DateFROM (([Expenses Types] INNER JOIN [GL Codes] ON [Expenses
Types].[GL Code] = [GL Codes].[GL Account]) INNER JOIN ([SAP ESS Line Items
by Cost Centre] INNER JOIN [SAP ESS Line Items by Exp Cat] ON [SAP ESS Line
Items by Cost Centre].[Trip number] = [SAP ESS Line Items by Exp Cat].Trip)
ON [Expenses Types].Type = [SAP ESS Line Items by Exp Cat].ExpTy) INNER JOIN
[Cost Centre] ON [SAP ESS Line Items by Cost Centre].[Cost ctr] = [Cost
Centre].[Cost Centre]WHERE ((([Cost Centre].Division)="ukfs") AND (([SAP ESS
Line Items by Exp Cat].Date) Between [Month Start] And [Month End]));

 

[Marshall Barton]

I have the following SQL which I need to amend to do the following

1. Exclude all records where [GL Codes].[GL Account] = 4301005 and [SAP ESS
Line Items by Exp Cat].LocCurrAmt is less than 75

2. Exclude all records where [GL Codes].[GL Account] = 4301001 and [SAP ESS
Line Items by Exp Cat].LocCurrAmt is less than 75

Can anyone help with the following? I can't seem to get the syntax correct.

SELECT [SAP ESS Line Items by Exp Cat].Trip, [SAP ESS Line Items by Exp
Cat].Persno, [SAP ESS Line Items by Exp Cat].[Employee/appname], [SAP ESS
Line Items by Cost Centre].[Cost ctr], [Cost Centre].[Cost Centre Name],
[Cost Centre].[Chargeout Business Unit], [Cost Centre].Division, [SAP ESS
Line Items by Cost Centre].Order, [SAP ESS Line Items by Exp Cat].ExpTy, [SAP
ESS Line Items by Exp Cat].[Name of expense type], [GL Codes].[GL Account],
[GL Codes].[GL Account Name], [SAP ESS Line Items by Exp Cat].LocCurrAmt,
[SAP ESS Line Items by Exp Cat].Curr, [SAP ESS Line Items by Exp
Cat].DateFROM (([Expenses Types] INNER JOIN [GL Codes] ON [Expenses
Types].[GL Code] = [GL Codes].[GL Account]) INNER JOIN ([SAP ESS Line Items
by Cost Centre] INNER JOIN [SAP ESS Line Items by Exp Cat] ON [SAP ESS Line
Items by Cost Centre].[Trip number] = [SAP ESS Line Items by Exp Cat].Trip)
ON [Expenses Types].Type = [SAP ESS Line Items by Exp Cat].ExpTy) INNER JOIN
[Cost Centre] ON [SAP ESS Line Items by Cost Centre].[Cost ctr] = [Cost
Centre].[Cost Centre]

WHERE ([Cost Centre].Division="ukfs") AND ([SAP ESS
Line Items by Exp Cat].Date Between [Month Start] And [Month
End]) AND NOT ([GL Codes].[GL Account] IN(4301005,4301001)
AND [Exp Cat].LocCurrAmt < 75)