Access Query: too complex

Question

I am trying to run a query as part of a report to determine if an event needs to happen. There are lots of combinations because a subsequent event depends on the observations in the first and second event, and these combinations vary by fish size and are grouped by species. Hopefully that provides some context. I have scoured the questions on here, but haven't found any solutions to this exactly. Here is a snippet of the code. The code is about 160 lines or so, mostly of it contained within the Switch function. Thank you for any thoughts!

SELECT headerid, species, ageclass, P1, P2, P3, P4, P5,
switch(ageclass in ('Subyearling', 'Yearling') and P3 is null and P1<4 and P2<1,    
'SHOCK',
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 4 and 5 and 
P2<2, 'SHOCK',
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 6 and 7 and   
P2<3, 'SHOCK',
ageclass in ('Subyearling', 'Yearling') and P3 is null and P1 between 8 and 9 and 
P2<4, 'SHOCK',

   ageclass like 'Subadult' and P3 is null and P1<5 and P2<1, 'SHOCK',
   ageclass like 'Subadult' and P3 is null and P1 between 5 and 8 and P2<2, 'SHOCK',
   ageclass like 'Subadult' and P3 is null and P1 between 9 and 11 and P2<3, 'SHOCK',
   ageclass like 'Subadult' and P3 is null and P1 between 12 and 13 and P2<4, 'SHOCK',
   ageclass like 'Subadult' and P3 is null and P1 between 14 and 16 and P2<5, 'SHOCK'

   as Event from data;

Once you start using Switch, it is generally time to consider a related look-up table. BTW the above is not viable sql. — Fionnuala
It seems like if the question is anything then the answer is 'SHOCK' — Dan Metheus

Lee Louviere Lee Louviere · Accepted Answer · 2013-03-12T21:49:15

For "and" you can do these queries in stages. Filter by the first property, and filter the results on the second property.

For "or" you can do these queries separately and JOIN the results.

Programming logic is the art of breaking down a problem into smaller problems. So you need to break down your queries into smaller steps and apply them in order. It's likely with high complexity, queries like these branch differently depending on preceding information, and you should branch in your algorithm logic, not in your select statement.

Access Query: too complex

1 Answers