Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Red face Unanswered: Limit query results to certain fields of a record

    Hi all,

    I am trying to create a query that only pulls certain fields into the result set. I have 11 fields in a record where some will be populated and some will not be.
    For example:

    [Furniture Comments] "Install desk"
    [Furniture Estimate] "$300.00"
    [Movers Comments] Null
    [Movers Estimate] Null
    [Electrical Comments] "Pull wire for outlet"
    [Electrical Estimate] "$500.00"
    [HVAC Comments] "Move vent to other side of wall"
    [HVAC Estimate] "$1,000"
    [Install Comments] Null
    [Install Estimate] Null

    How do I get the Furniture, Electrical, and HVAC to pull into the query but not the Movers and Install?

    The intent is to populate a report with only the fields that have values and leave the other categories off the report.

    Thanks

    Chuck

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't get it.

    If you don't want certain fields, don't include them!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thsi has the stench of dodgy design
    waht happens if you suddenly decide to add another trade.. say plumbers

    your current solution demands a redesign of the application
    pushing the comments to a sub table means that you users can have as many different trades as they wish

    however in the situation you are in now what does it matter, just ignore the columns you dont' want or write your query explicitly to include only the columns you ned
    eg
    "select [Furniture Comments], [Furniture Estimate], [Electrical Comments] , [Electrical Estimate] , [HVAC Comments] ,[HVAC Estimate] from mytable"
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2009
    Posts
    3

    Clarification

    So let me clarify more....

    The categories we have are static. There are 11 possible places to put estimates.

    In one record we might have estimates for 4 out of the 11 and in the second record we might have 6 different categories filled in. The problem lies in how do I exclude the categories that have $0.00 estimates but ensure I get only the ones that have dollar amounts in them.

    Each record will have different categories based on the end-user input.

    Thanks for the help.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by emmas_pa
    The categories we have are static. There are 11 possible places to put estimates.
    They are today. Chances are you can't guarantee with 100% certainty that this will remain the case indefinitely. That's the crux of what Healdem is trying to convey to you.

    In one record we might have estimates for 4 out of the 11 and in the second record we might have 6 different categories filled in. The problem lies in how do I exclude the categories that have $0.00 estimates but ensure I get only the ones that have dollar amounts in them.

    Each record will have different categories based on the end-user input.

    Thanks for the help.
    You don't. Not with this design. Please trust me when I say you're setting yourself up for a reporting nightmare by pursuing a denormalized design.

    Have a read through here and see what you think:

    http://r937.com/relational.html
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2009
    Posts
    3

    Redesign

    I can drop the comments to a sub table and that is fine.

    I think I see a solution now. Instead of making the fields static on the form, I can use dropdowns from the tbl_categories to allow the user to select the type.

    Thanks for the comments!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's EXACTLY it! Good luck!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •