Query too long how can I shorten this?

[yepwingtim]

Test: IIf([SortOrder]=0,"VisitTime","")+IIf([SortOrder]=1,"Location","")+IIf([SortOrder]=2,"Purpose/Subj","")+IIf([SortOrder]=3,"Assessment","")+IIf([SortOrder]=4,"E2","")+IIf([SortOrder]=5,"LH","")+IIf([SortOrder]=6,"FSH","")+IIf([SortOrder]=7,"PROG","")+IIf([SortOrder]=8,"PROL","")+IIf([SortOrder]=9,"HCG","")+IIf([SortOrder]=10,"Endo","")+IIf([SortOrder]=11,"Rt Fol 1","")+IIf([SortOrder]=12,"Rt Fol 2","")+IIf([SortOrder]=13,"Rt Fol 3","")+IIf([SortOrder]=14,"Rt Fol 4","")IIf([SortOrder]=16,"Lt Fol 1","")+IIf([SortOrder]=17,"Lt Fol 2","")+IIf([SortOrder]=18,"Lt Fol 3","")+IIf([SortOrder]=19,"Lt Fol 4","")+IIf([SortOrder]=21,"Lupron .05","")+IIf([SortOrder]=22,"Bravelle .75","")+IIf([SortOrder]=23,"Menopur 75","")+IIf([SortOrder]=24,"Repronex 75","")+IIf([SortOrder]=25,"Gonal-f 75","")+IIf([SortOrder]=26,"Cetrotide .25","")+IIf([SortOrder]=27,"Prog 50 IM","")+IIf([SortOrder]=28,"Prog 200 vag","")+IIf([SortOrder]=29,"Estradiol 2mg","")+IIf([SortOrder]=30,"HCG 10000")+IIf([SortOrder]=31,"ASP/IUI/Trans","")+IIf([SortOrder]=32,"HSG","")+IIf([SortOrder]=33,"Sono/SHG","")+IIf([SortOrder]=34,"MRI","")

Thanks
________
EASY VAPE

 

[pbaldy]

What is it you're trying to accomplish? By the looks of it, I'd have a table with the SortOrder values and the associated values and just join that table into the query.

 

[yepwingtim]

hi pblady, i don't want that many tables/relationships - join queries confuses me =(
I just need to shorten this by a bit =)
________
Honda Stepwgn specifications

 

[pbaldy]

You'd rather try to maintain that monstrosity when something changes? It's a big mistake IMO, but look at the Switch function.

 

[MSAccessRookie]

hi pblady, i don't want that many tables/relationships - join queries confuses me =(
I just need to shorten this by a bit =)

• A Cascading IIf() Statement would be disastrous in appearance, but it would save a few dozen characters.
• A Switch Case statement might save a few more characters.

All in all, pbaldy is right. Since you are actually only using one of the values at a time, and the others will always be "", a SortOrder Key is by far the best way for you to go. It will probably be faster as well.

No need to be afraid of Joins. A join is a very basic part of Database Programming, and every user winds up needing to use them over and over again. If you have questions, please feel free to ask.

FOOTNOTE: I see that pbaldy agrees that the Switch Case Method would shorten the code a little, but you may find that it is VERY little.

 

[yepwingtim]

ahh okay, I'll try to do it

So Ill make a table with

ID
SortOrderID
Test
________
CHRYSLER VALIANT CHARGER HISTORY

 

[MSAccessRookie]

ahh okay, I'll try to do it

So Ill make a table with

ID
SortOrderID
Test

Looks like that would work. You would access the table by Joining Table1 (your original table) to Table2 (the new table) by their SortOrderIDs in a manner similar to below:

Select TestName from Table1 INNER Join Table2 
ON Table1.SortOrderID=Table2.SortOrderID;

Note that I changed the last column from Test to TestName to create a little less confusion as to its meaning (to me). You should call it whatever is most meaningful to you.

 

[yepwingtim]

Thank you guys and happy holidays =)
________
Toyota Fj40 Specifications

 

[Pat Hartman]

ID is not necessary since SortOrderID is unique.