My Query is too complex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please can you help my query keeps saying it is too complex and i am not sure
how to get around the problem (not wanting to do more queries to supply me
with the daya i need)
Below I have showm my query

Expr1: Switch([DrCode] Like "DCOE*","Care of The Elderly",[DrCode] Like
"DENT*" And "DER*" And "DHE*" And "DEC*","E.N.T",[DrCode] Like
"DM*","Medical",[DrCode] Like "DGYN*" And "DGR*" And "DGH*" And
"DGC*","Gynaecology",[DrCode] Like "DOPH*","Ophthalmology",[DrCode] Like
"DORS*" And "DOS*","Oral Surgery",[DrCode] Like "DOR*" And "DOC" And
"DOH","Orthopaedic",[DrCode] Like "DPAE*" And "DPC" And "DPH" And
"DPR","Paediatrics",[DrCode] Like "DPSY*","Psychiatry",[DrCode] Like
"DSUR*","Surgical") it returns results saying too complex its so annoying if
i do an if statement it does not like it please help.
thankyou
kate
 
kate said:
Please can you help my query keeps saying it is too complex and i am not sure
how to get around the problem (not wanting to do more queries to supply me
with the daya i need)
Below I have showm my query > Expr1: Switch([DrCode] Like "DCOE*","Care of The Elderly",[DrCode] Like "DENT*" ,"E.N.T",[DrCode] Like "DER*","Medical",[DrCode] Like "DHE*" ,"E.N.T",[DrCode] Like"DEC*","E.N.T",[DrCode] Like "DM*","Medical",[DrCode] Like "DGYN*" ,"Gynaecology",[DrCode] Like "DGR*" ,"Gynaecology" ,[DrCode] Like "DGH*" ,"Gynaecology", [DrCode] Like "DGC*" ,"Gynaecology",[DrCode] Like "DOPH*","Ophthalmology",[DrCode] Like "DORS*" ,"Oral Surgery",[DrCode] Like "DOS*","Oral Surgery",[DrCode] Like "DOR*","Orthopaedic",[DrCode] Like "DOC" ,"Orthopaedic",[DrCode] Like "DOH","Orthopaedic",[DrCode] Like "DPAE*" ,"Paediatrics",[DrCode] Like "DPC" ,"Paediatrics",[DrCode] Like "DPH" ,"Paediatrics",[DrCode] Like "DPR","Paediatrics",[DrCode] Like "DPSY*","Psychiatry",[DrCode] Like "DSUR*","Surgical",[DrCode] Like "DCAE*","Care of The Elderly")
Click to expand...
 
Well, this is complex enough that I would be tempted to write a function to
handle it or better yet make a table with two columns and use that in a
query.

Try the following for your switch statement. You need to specifically
reference the DrCode field in EACH comparison. AND you need to use OR
(meaning this or that) NOT And (meaning must be both at the same time).

Switch([DrCode] Like "DCOE*","Care of The Elderly",
[DrCode] Like "DENT*" Or DrCode Like "DER*" or DrCode Like "DHE*" Or DrCode
Like "DEC*","E.N.T",
[DrCode] Like "DM*","Medical",
[DrCode] Like "DGYN*" OR DrCode Like "DGR*" OR DrCode Like "DGH*" OR DrCode
Like "DGC*","Gynaecology",
[DrCode] Like "DOPH*","Ophthalmology",
[DrCode] Like "DORS*" OR DrCode Like "DOS*","Oral Surgery",
[DrCode] Like "DOR*" OR DRCode Like "DOC" OR DRCode Like
"DOH","Orthopaedic",
[DrCode] Like "DPAE*" OR DRCode Like "DPC" OR DRCode Like "DPH" OR DRCode
Like "DPR","Paediatrics",
[DrCode] Like "DPSY*","Psychiatry",
[DrCode] Like "DSUR*","Surgical")

Advantage of using a table is that all you need to do is add one record to
it if another DrType is added to your data.

IF you use the table, you have two options. Option one would be to enter
every DRType and the corresponding expansion (Title).
Table: DRTypes
DrType (DCOE, DENTIST, etc.)
Expansion(Care of Elderly, E.N.T)

Then you could just add this table to your query and link between the DrType
fields.
Alternate design of table
Table DrTypes
DrTypeAbbrev (DCOD, DENT)
Expansion (Care of Elderly, E.N.T.)

Again you would add the table to your query and you would set up a join
between DrType and DrTypeAbbrevation. HOWEVER, you would need to open the
query in SQL view and modify the join to read something like
TheMainTable INNER JOIN DrTypes On TheMainTable.DrCode LIKE
DrTypes.DrTypeAbbrev & "*"
 
Back
Top