Access 2007 Query is too complex (Error 3360)

Question

I am trying to produce a simple query, I have an existing very complex query "HsHld" which takes data from a table and produces a field called HsTyp. HsTyp has a wide range of text values and I want to produce a new field, which groups entries into broad categories. I am attempting to do this using the Switch command.

Here is the SQL

SELECT HsHld.ID, HsHld.aacode, HsHld.DVHsize, HsHld.HsTyp,

Switch(

[HsTyp]='Widowed (85+)','Alone 85+',

[HsTyp]='Divorced (85+)','Alone 85+',

[HsTyp]='Single (85+)','Alone 85+',

[HsTyp]='Widowed (75-84)','Alone 75-84',

[HsTyp]='Divorced (75-84)','Alone 75-84',

[HsTyp]='Single (75-84)','Alone 75-84',

True,[HsTyp])

AS Expr1

FROM HsHld;

If I only have a few values in the Switch command then the query runs, but once I have more than three I get the error message "Query is too complex". I have used much larger Switch functions before so I can't see why this one does not work.

Answer 1

Hi

The combined table works fine for small datasets . . where the data is easily visible on a screen . . much like looking at a spreadsheet.

If  , , HsType has 14,000  I would definitly look a 2 tables . . one for types and one for groups.

You will need some way of cross-referencing the  HsType values against a group.

With 14000 posible values . . this is a bigger problem than your original post inferred . .I cant see you listing 14000 values in a coded command. So a table structure is the only feasible answer.

cheers

PaulG

Answer 2

Thanks PaulG,

Unfortunatly I'm not sure that solution is very feasible as HsType has 14,000 different possible values.

Falcs

Answer 3

Hi

You have not mentioned where this data originates.

The values in your list are actual data - and it bothers me to see "data" values used this way.

Hans has mentioned a lookup table and that will fix your current dilema - but i would suggest going back a step and maybe changeing how the original data is structured.

expand on Hans' table idea.

Add a numeric Key to the lookup table

You could use something like this - Its actually 2 tables in one

tblCODES

TC_KEY . . number . . autonumber? . . key to the record - so is key for both Code and Group

TC_CODE . . your code data . . eg  Widowed (75-84)

TC_TCG_KEY . .  this is the tricky bit . . this key points back to TC_KEY ( as key to the group data)

TC_GROUP . . . you Group data . .eg  'Alone 75-84'

I suspect that the total Codes and Groups is quite small - so displaying the table on the screen would allow you to populate it with all the data fairly easily.

using the above example data you could end up having . . columns dont work very well here . .

TC_KEY . .TC_CODE .  . TC_TCG_KEY .  .TC_GROUP

 . . .

1 . . . . . . . Widowed (85+) . . .  .  1 . . . . . . . Alone 85+

2 . . . . . . . Divorced (85+) . . . . . .1   . . . . . . Alone 75-84

3 . . . . . . . Single (85+) . . . . . . . .1 . . .  . . . .Alone 65-74

4 . . . . . . . Widowed (75-84) . . . . 2  . . .    . .Alone 55-64

5 . . . . . . . Divorced (75-84) . . .. .2 . . . .. . .  Alone 45-54

6 . . . . . . . Single (75-84) . . . . . . 2   . . . . . . Alone 35-44

so if yo look at the last record.

Code . . Single (75-84) has a key of 6 . .and a grouk key of 2 which is an foreign key pointer to the group in row 2 . which is Alone 75-84.

the alternative is to have 2 tables . . one for codes and one for groups.

the table Hans proposed is actually like the one above - but without the number (key) columns and TC_CODE would be the key.

and would look like this . .

TC_CODE . . . .  . .TC_GROUP

. . .

Widowed (85+) . . . Alone 85+

Divorced (85+) . . . .Alone 85+ 

Single (85+) . . . . . . Alone 85+

Widowed (75-84) . .  Alone 75-84

Divorced (75-84) . . . Alone 75-84..

Single (75-84) . . . . . Alone 75-84.

Some food for thought . .

cheers

PaulG

Answer 4

If the HsHld query has to perform a lot of calculations to produce the HsTyp field, the additional computations needed for Switch may have been the straw that broke the camel's back.

Try creating a lookup table wirh the unique values of the HsHld field in the first column (as Primary Key) and the categories in the second column. You can then create a query based on HsHld and the lookup table, joined on HsTyp vs the 1st column, and return the 2nd column of the lookup table.