Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can a Query have Dynamically filled columns?

Can a Query have Dynamically filled columns?

Can a Query have Dynamically filled columns?


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

RE: Can a Query have Dynamically filled columns?

Arg ... Never mind ... completely forgot about using iif in a query ... plus ... didn't help that w3schools doesn't list iif as a function under MSACCESS SQL Functions

The Solution is Simply

iif(option="1",[a],iif(option="2",[q] ,[f])) as X1,
iif(option="1",[b],iif(option="2","" ,"")) as X2,
iif(option="1",[c],iif(option="2","blah","")) as X3,
iif(option="1",[d],iif(option="2",[k] ,"")) as X4
from x
where <some filter>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close