I decided to merge a large number of queries into 6 because each set did the same basic thing to a different field in the table.
The way I did this was to create a global variable and a function that would return that variable. I had the front-end set that variable (say to "PCP" or "BF"). I then created a query that would say IIF(Global="PCP", [Primary Care Provider, IIF(Global="BF", [Breast Feeding])).
The problem is that these statements keep getting longer and longer as I add more fields to the reports. They are complex to look at and hard to understand (for the testers). I also believe that they might be making the whole process slower. Is there a way to return or set the name of a field? I can't generate the SQL on my own because the data is complex enough that I need 6 seperate queries (4 of which would need to use the 'return field name' function) to accomplish my task.
The way I did this was to create a global variable and a function that would return that variable. I had the front-end set that variable (say to "PCP" or "BF"). I then created a query that would say IIF(Global="PCP", [Primary Care Provider, IIF(Global="BF", [Breast Feeding])).
The problem is that these statements keep getting longer and longer as I add more fields to the reports. They are complex to look at and hard to understand (for the testers). I also believe that they might be making the whole process slower. Is there a way to return or set the name of a field? I can't generate the SQL on my own because the data is complex enough that I need 6 seperate queries (4 of which would need to use the 'return field name' function) to accomplish my task.
