SWITCH FUNCTION TOO COMPLEX

[aaronlglover]

Hey Guys,

I have a bunch of data stored in an MDB. I am using microsoft query to query it and pull it into excel. From what I understand Access does not support Case statements so I decided to use SWITCH instead. The problem is when I get past 15 conditions in my SWITCH statement I get a "Expression too complex in query expression" error. Does anyone have any workarounds to this or suggestions?

Thanks

 

[dhookom]

It seems that you are attempting to create overly complex expressions when creating and using a related table might work better. Since you didn't provide any details regarding your expression or why you need more than 15 conditions, I can't tell for sure.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Can't you use a mapping table ?
Not seeing your huge Switch expression it's hard to give any decent advice ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aaronlglover]

Sorry,

Here is the SQL I am using

SELECT Schedules.AGENT_ID, Schedules.AGENT_NAME AS 'Agent', Schedules.AGENT_DATA_VALUE AS 'Supervisor', Schedules.MU_ID AS 'MU', Schedules.HEADER_DATE AS 'Schedule Date', Schedules.DETAIL_EXCEPTION AS 'Detail Exception', SWITCH(schedules.DETAIL_EXCEPTION = '1 on 1 ','Meeting',schedules.DETAIL_EXCEPTION = '40 hour rule ','Absenteeism',schedules.DETAIL_EXCEPTION = 'All Cen Power of One','Leave',schedules.DETAIL_EXCEPTION = 'Apprentice ','Misc',schedules.DETAIL_EXCEPTION = 'Appt Time ','Exclude',schedules.DETAIL_EXCEPTION = 'Bereavement ','Training',schedules.DETAIL_EXCEPTION = 'Break ','VTO',schedules.DETAIL_EXCEPTION = 'Early Out ','Meeting',schedules.DETAIL_EXCEPTION = 'Extended Time ','Absenteeism',schedules.DETAIL_EXCEPTION = 'FMLA ','Leave',schedules.DETAIL_EXCEPTION = 'Focus Group ','Exclude',schedules.DETAIL_EXCEPTION = 'Job Shadowing ','Open',schedules.DETAIL_EXCEPTION = 'Jury Duty ','Vacation',schedules.DETAIL_EXCEPTION = 'Late ','Meeting',schedules.DETAIL_EXCEPTION = 'Leads Open Time ','VTO',schedules.DETAIL_EXCEPTION = 'LOA ','Absenteeism',schedules.DETAIL_EXCEPTION = 'Lunch ','Misc',schedules.DETAIL_EXCEPTION = 'Makeup Time ','Exclude',schedules.DETAIL_EXCEPTION = 'Manager Override ','Training',schedules.DETAIL_EXCEPTION = 'Meeting ','Absenteeism',schedules.DETAIL_EXCEPTION = 'MTO ','Meeting',schedules.DETAIL_EXCEPTION = 'MWEST OT ','Misc',schedules.DETAIL_EXCEPTION = 'No Call No Show ','Meeting',schedules.DETAIL_EXCEPTION = 'Project ','Absenteeism',schedules.DETAIL_EXCEPTION = 'Protected Shift ','Exclude',schedules.DETAIL_EXCEPTION = 'Reading Time ','Vacation',schedules.DETAIL_EXCEPTION = 'Roamer ','Training',schedules.DETAIL_EXCEPTION = 'Sick ','Training') AS Category, Header_date+(DETAIL_START_MINUTE/1440) AS 'Detail Start', Header_Date+((Detail_START_MINUTE+DETAIL_LENGTH)/1440) AS 'Detail Stop', Schedules.DETAIL_LENGTH AS 'Detail Duration', Schedules.SCHED_ID AS 'Schedule ID' FROM `\\Wfmid-1350\database$\IEX`.Schedules Schedules WHERE (Schedules.HEADER_DATE>= #2009-05-31# And Schedules.HEADER_DATE< #2009-06-23#) AND (Schedules.MU_ID In (1000))

 

[dhookom]

It seems PH and I guessed correctly that you should consider creating a table of unique DETAIL_EXCEPTION values and their related value. You can then add this table into your query and join the DETAIL_EXCEPTION fields and display the related values.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

So, the answer is clearly to have a DetailStart mapping table you'll join in your query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mikeraut]

Hi All,
I have exactly the same problem. I have 25 individual sites that I need to report on. It too works upto 15 then bombs out with the same "too complex error"

Here is the MS query
______________________________

XLODBC
1
DSN=MS Access Database;DBQ=C:\Program Files\CBSv3\cbs.mdb;DefaultDir=C:\Program Files\CBSv3;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT Switch([tblExtensions.Switch]=0,'Head Office', [tblExtensions.Switch]=1, 'Keetmans DEES', [tblExtensions.Switch]=2, 'Keetmans RWS', [tblExtensions.Switch]=3, 'Mariental Abbatoir', [tblExtensions.Switch]=4, 'Otjiwarongo Forestry', [tblExtensions.Switch]=5, 'Hardap ', [tblExtensions.Switch]=6, 'Keetmans Abattoir', [tblExtensions.Switch]=7, 'Mariental DEES', [tblExtensions.Switch]=8, 'Gobabis DEES', [tblExtensions.Switch]=9, 'Mariental Vet', [tblExtensions.Switch]=10, 'Rundu DEES', [tblExtensions.Switch]=11, 'Eenana DEES', [tblExtensions.Switch]=12, 'Eenana RWS', [tblExtensions.Switch]=13, 'Eenana Forestry', [tblExtensions.Switch]=14, 'Eenana VET', [tblExtensions.Switch]=15, 'Outapi DEES', [tblExtensions.Switch]=16, 'Outapi Forestry', [tblExtensions.Switch]=17, 'Ondangwa Vet', [tblExtensions.Switch]=18, 'Otjiwarongo DEES', [tblExtensions.Switch]=19, 'Ongwediva Forestry', [tblExtensions.Switch]=20, 'Ongwediva DEES', [tblExtensions.Switch]=21, 'Gobabis AUX', [tblExtensions.Switch]=22, 'Karasburg VET', [tblExtensions.Switch]=23, 'Keetmans Vet', [tblExtensions.Switch]=24, 'Uitkoms Research', [tblExtensions.Switch]=25, 'Mariental RWS') AS [Site], tblExtensions.Device AS 'PIN', tblExtensions.Budget AS 'LIMIT', tblExtensions.Total AS 'USED', tblExtensions.Barred, ROUND((tblExtensions.Budget-tblExtensions.Total),2) AS 'REMAINING' FROM `C:\Program Files\CBSv3\cbs`.tblExtensions tblExtensions ORDER BY tblExtensions.Switch, tblExtensions.Device


Switch PIN LIMIT USED Barred REMAINING

__________________________


I am extracting data from Access into Excel

Any help would be appreciated - If any suggestions are to be made regarding adding another table and joining , I would like to see a sample if possible, tks Mike

 

[dhookom]

Start by creating a small table like
[tt][blue]
tblSites
SiteID Site
0 'Head Office'
1 'Keetmans DEES'
2 'Keetmans RWS'
3 'Mariental Abbatoir'
4 'Otjiwarongo Forestry'
5 'Hardap '
6 'Keetmans Abattoir'
7 'Mariental DEES'
-- ------------------
25 'Mariental RWS'
[/blue][/tt]

You can then add this table to your query and join the SiteID field to the [Switch] field. Then add the Site field to the query grid.

Duane
Hook'D on Access
MS Access MVP