Trying to eliminate a number of "like" reports that differ only on how N fields are populated from a primary table ... the original designer created N report variations ... now ... any minor changes have to be applied to N reports instead of one ... plus ... adding any filters to this report also multiplies efforts ... is it possible to create a query that would use a Table that defines the variations so only one report is required. My SQL skills are very basic, so hoping someone here might be able to answer this.
Example: Let's say a Primary Table X, has 26 character Fields; "a" thru "z" and a Variation Table Y has 5 character fields; "Option", "X1", "X2", "X3", "X4" and may have records like:
Table Y
Rec#: Option, X1, X2, X3, X4
----------------------------
1: 1, a, b, c, d
2: 2, q,"","Blah",k
3: 3, f,"","",""
Is it possible for a query to select 1 row with * from table X and also fill 4 additional character fields; X1, X2, X3, and X4 based on options listed in the Variation table?
If this query is run with a parameter of option = 2 then the expected output should be
qry.a = X.a
.
.
.
qry.z = X.z
qry.x1 = X.q
qry.x2 = blank or null
qry.x3 = "Blah"
qry.x4 = X.k
The ultimate goal is ONE report that handles all of the variations
Expecting the related X1-4 Report Fields could be coded to do this selection instead of using a query ... however ... thinking a query would make the report simpler
