Results 1 to 6 of 6
  1. #1
    bxdobs is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2012
    Posts
    35

    Can an SQL Query fill in Colums based on a parameter linked to a Control/option Table?

    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 changes have to be applied to N reports instead of one ... plus ... adding any filters to these reports 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 Control 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

    Perhaps a Control table isn't even required ... could x1 - 4 be added to the query using "if" constructs controlled by a query param ... is concat available in msaccess could something like concat(if(option 1,,if(option 2,,if(option 3,,))) as Xn be used?
    Last edited by bxdobs; 04-13-2021 at 04:23 PM. Reason: add tags

  2. #2
    bxdobs is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2012
    Posts
    35
    SIMPLE SOLUTION! ... seems I am having more and more grey moments ... might be time to throw in the proverbial towel ... completely forgotten that iif(,,) is valid in msaccess queries ... didn't help that iif is NOT listed in w3schools under MSACCESS SQL functions

    anyway

    the following would appear to be all I would need to do ... perhaps there is a simpler method? ... I have made global user functions but found these tend to make queries slower plus make it harder to decipher in the future ... prefer black and white solutions

    Select
    *,
    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>

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looks like an un-normalized design, which is likely the root of the problem. Yes, if 2 or more reports are basically the same, it is a common error to make n reports instead of one with varying record sources or filters. Sometimes Choose or Switch is more elegant that nested IIFs. Likely you didn't find IIF when searching under SQL because it's not - it's a VBA function, one of many that can be used in Access sql.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am totally confused......

    Maybe you would post the SQL of the record source (queries) for 2 or 3 of the reports?

  5. #5
    bxdobs is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2012
    Posts
    35
    Yes Micron! The Choose Function is the much CLEANER choice (nested iifs can become monsters too). Thanks, appreciate your pointing that out.

    Sorry ssanfu, these reports contain confidential information ... all reports are printed on preprinted paper also making the output cryptic without the background details

    SQL has always been a thorn in my shoe especially after writing with 4GL for 20+ years. Even with 40+ years of various DB programming experience including; dbase, foxpro and 4GL, I still struggle to coin a question relating to SQL in the appropriate terminology (plus, now most notably, my realization, sadly, especially with this project, that my experience is no longer something I can depend on ... I am grateful for the existence of communities like this with people who are still willing to share knowledge)

    Suffice it to say, that all N reports use the same data (table) with various fields mixed around between the report variations ... so ... the solution being pursued was to identify ALL the VARIANT fields from ALL the report variations and assign them; X1 - XN values ... this should allow tossing the N reports, building ONE report with the new X1-XN fields then calling it from ONE query called from ONE all-encompassing Feature/Option form ... going forward, anyone needing to make further changes to this Report will undoubtedly be thankful this was corrected

    Yes, conceptionally the entire DB is a poorly unnormalized design ... owner claims this was created by an ACCESS Trainer ... looks to me as it was completely built using ACCESS built-in features with minimal coding ... breaks all fundamental rules with bound fields, no audit tracing for changes and more.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hmmmm.. I understand that the DATA can be/is confidential/sensitive information, but the SQL of the report queries??
    I wouldn't think the field names would be confidential information.....

    OK, well, good luck with your project. I'll back out now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-26-2016, 08:53 AM
  2. Replies: 5
    Last Post: 10-09-2015, 09:27 AM
  3. Replies: 1
    Last Post: 11-23-2014, 02:20 PM
  4. run parameter query based on option box selection
    By rivereridanus in forum Queries
    Replies: 1
    Last Post: 07-01-2011, 01:07 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums