Basic question as to how Access does criteria

[Johnny C]

I've got a query that generates a big table of data for use by multiple queries.

There are criteria against a number of the fields. But when I run the query, it just ignores the criteria and returns everything.

What I want is to restrict data from a Region fields to "North","South","Midlands" and "Scotland". I want to restrict a field to AgeGroup "16-18","19-23","24+". Theres another field with 3 criteria too. So what I want is all data that satisfies those criteria independently.

I initially had the criteria against each field on separate lines, but it seemed to be combining the criteria and matching criteria on the same line in query design and only returning a subset of the data.

E.g. first row in criteria had "North" under Region field and "16-18" on the same row in the AgeGroup field. Second row had "Midlands" under region and "19-23" under AgeGroup. It was returning only stuff from the North in Agegroup 16-18 and from the Midlands in AgeGroup 19-23.

So I made the criteria into 'or's. So, Region has the criteria
"North" or "South" or "Midlands" or "Scotland"
AgeGroup has the criteria
"16-18" or "19-23" or "24+"
Scheme field has the criteria
"ILA" or "ALA" or "TLA"
Outcome field has the criteria
"S" or "U" or "X"

and I put these on separate rows in the criteria section of query design. But it's bringing back all regions, all agegroups, all schemes.

There are additional criteria, 2 yes/no fields from small linked lookup tables linked via a primary key in those tables.
Again it's ignoring the criteria.

The only criteria that does work is a date filter, >#31/12/2009# on the first row of criteria.

Looking at the SQL it seems to do AND's against criteria on the same criteria row in the query design grid.

Sooo... how do you use multiple criteria against multiple fields on a query to just bring back records that fit all criteria? Is my understanding that you need to use combine multiple criteria using OR for each field and match them all on the same row to get an AND?

Sorry it's a dumb noob question, I haven't used criteria that much but I don't remember having problems with multiples in the past.

Here's the criteria part of the SQL where they are on separate rows in the design grid, which seems to me to do ANDs against stuff on the same row but also bring back things on different rows which ignore criteria on othe rows

WHERE (
((StaticData.SCHEME)="ILA" Or (StaticData.SCHEME)="ALA" Or (StaticData.SCHEME)="TLA") AND 
((StaticData.StartCohort)>20094) AND (([T-AssessorLookup].[Ignore?])=False) AND 
((VisitType.IncludeInAnalysis)=True)) OR (((StaticData.AGEGROUP)="16-18" Or (StaticData.AGEGROUP)="19-23" Or (StaticData.AGEGROUP)="24+")) OR (((StaticData.REGION)="Scotland" Or (StaticData.REGION)="South" Or (StaticData.REGION)="North" Or (StaticData.REGION)="Midlands")) OR (((VISITS.OUTCOME)="S" Or (VISITS.OUTCOME)="U" Or (VISITS.OUTCOME)="X")
);

Here's the SQL where I put them all on the same row and (I HOPE!!!) will bring back just what I want. This query takes half an hour or more to run so I've written this post whilst it runs with them all on the same row to see if that works, which from the SQL looks like it will

WHERE (
((StaticData.SCHEME)="ILA" Or (StaticData.SCHEME)="ALA" Or (StaticData.SCHEME)="TLA") AND 
((StaticData.AGEGROUP)="16-18" Or (StaticData.AGEGROUP)="19-23" Or (StaticData.AGEGROUP)="24+") AND 
((StaticData.REGION)="Scotland" Or (StaticData.REGION)="South" Or (StaticData.REGION)="North" Or (StaticData.REGION)="Midlands") AND 
((StaticData.StartCohort)>20094) AND 
((Maytas3_VISITS.OUTCOME)="S" Or (VISITS.OUTCOME)="U" Or (VISITS.OUTCOME)="X") AND 
(([T-AssessorLookup].[Ignore?])=False) AND 
((VisitType.IncludeInAnalysis)=True));

 

[plog]

I can't really discern what you want your criteria to be from your words, so it would be best to provide some sample starting data, then what results should be returned based on that sample data Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 1/2/2013, 56
Steve, 4/30, 1998, 419

 

[namliam]

These type of questions tend to answer themselves IF you make your code readable...

WHERE (
          (   (StaticData.SCHEME)="ILA" 
           Or (StaticData.SCHEME)="ALA" 
           Or (StaticData.SCHEME)="TLA"
          ) 
       AND
          (   (StaticData.StartCohort)>20094
          ) 
       AND 
          (
           ([T-AssessorLookup].[Ignore?])=False
          ) 
       AND 
          ( 
           (VisitType.IncludeInAnalysis)=True 
          )
      ) 
   [B][COLOR="Red"]OR[/COLOR][/B] (
       (
             (StaticData.AGEGROUP)="16-18"  
          Or (StaticData.AGEGROUP)="19-23"  
          Or (StaticData.AGEGROUP)="24+"
       )
      ) 
   [B][COLOR="red"]OR[/COLOR][/B] (
       (
             (StaticData.REGION)="Scotland"  
          Or (StaticData.REGION)="South"  
          Or (StaticData.REGION)="North"  
          Or (StaticData.REGION)="Midlands") 
        ) 
   [B][COLOR="Red"]OR[/COLOR][/B] ( 
       ( 
             (VISITS.OUTCOME)="S"  
          Or (VISITS.OUTCOME)="U"  
          Or (VISITS.OUTCOME)="X"
       )
      );

So tell me what is wrong with this where statement vs your requirements?

 

[Johnny C]

Hi
Lol Namlian, that's what I did to figure out that my initial misunderstanding was wrong.

I'm more used to Excel, I wrote a VBA function to parse long formulae and break it down. In Access I have to resort to notepad alas... you'd think they'd fix the SQL window to do that automatically, it's not exactly rocket science to fomat according to nested parenthesis.

But I understand now! Which was the point of my question.

Cheers