Crosstab Query with Multiple Criteria

[phenley]

Hello,

I have some SQL code that doesn't seem to be working for a crosstab query. I have a join on two tables, and in the code below, the "Not Like "NANG"" statement works just fine, while the "Not Like "ALTCRG"" statement is not working at all. Any ideas where this might go wrong? All companies are listed in the Company groups table with their associated groups, and Company is a primary key in that table.

TRANSFORM Sum([Pivot Query].Amount) AS SumOfAmount
SELECT [Pivot Query].Company, [Pivot Query].Type
FROM [Pivot Query] INNER JOIN [Company Groups] ON [Pivot Query].Company = [Company Groups].Company
WHERE ((([Pivot Query].Type)<>"ALR") AND (([Company Groups].Group) Not Like "NANG")) OR ((([Pivot Query].Type)<>"ALR") AND (([Company Groups].Group) Not Like "ALTCRG"))
GROUP BY [Pivot Query].Company, [Pivot Query].Type
ORDER BY [Pivot Query].Company, [Pivot Query].[Statement Date]
PIVOT [Pivot Query].[Statement Date];

 

[isladogs]

Try using wildcards (*) for each:

TRANSFORM Sum([Pivot Query].Amount) AS SumOfAmount
SELECT [Pivot Query].Company, [Pivot Query].Type
FROM [Pivot Query] INNER JOIN [Company Groups] ON [Pivot Query].Company = [Company Groups].Company
WHERE ((([Pivot Query].Type)<>"ALR") AND (([Company Groups].Group) Not Like "*NANG*")) OR ((([Pivot Query].Type)<>"ALR") AND (([Company Groups].Group) Not Like "*ALTCRG*"))
GROUP BY [Pivot Query].Company, [Pivot Query].Type
ORDER BY [Pivot Query].Company, [Pivot Query].[Statement Date]
PIVOT [Pivot Query].[Statement Date];

 

[phenley]

Still doesn't seem to be working, the ALR part is working as with the NANG, just not the ALTCRG

 

[isladogs]

You haven't explained what you mean by 'not working'
Error message / no results / incorrect results / Access crashes / earthquake ?

Post a stripped down version of your db with the relevant tables & the query

See these guidelines on doing this: https://www.access-programmers.co.uk/forums/showthread.php?t=140587

EDIT: As you have less than 10 posts you will need to ZIP your db

 

[arnelgp]

you can simplify:

TRANSFORM Sum([Pivot Query].Amount) AS SumOfAmount
SELECT [Pivot Query].Company, [Pivot Query].Type
FROM [Pivot Query] INNER JOIN [Company Groups] ON [Pivot Query].Company = [Company Groups].Company
WHERE [Pivot Query].Type<>"ALR" AND [Company Groups].Group) <>"NANG" AND [Company Groups].Group) <>"ALTCRG"
GROUP BY [Pivot Query].Company, [Pivot Query].Type
ORDER BY [Pivot Query].Company, [Pivot Query].[Statement Date]
PIVOT [Pivot Query].[Statement Date];

 

[isladogs]

Arnelgp

Phenley wants 'not like' in his criteria which is why I posted my more complicated version

 

[phenley]

Arnelgp had it so it would properly work.

I was having the issue where it wouldn't filter out the criteria "ALTCRG" in the where statement. All I had to do was change the OR statement into an AND one, and it properly used the criteria.

Thanks for the help!