Results 1 to 8 of 8
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    Select only column with not null fields

    I have a table with about 15 columns.
    Now i want to create a query that will select only columns that have a value, and not show the ones that have null values.
    Datatypes of the columns are ShortText, except the ID column which is Autonumber.
    Can somenone help me out?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In a word, no. You can restrict which records (rows) are pulled, not which fields (columns). From the sound of it, your table isn't normalized. What do the fields hold?

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    the fields hold numeric values, but i decided to set their datatypes to shortext.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I meant a description of what they hold. They shouldn't be "January, February..." or "Product1, Product2...".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    For the work i am doing, i decided to use a denormalized table.
    Now i am wondering if it's possible to programmatically (using vba) Select only column with not null values from that table.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It may be possible, but not simple. What if one record has a value in a column and another doesn't? What do you expect to see when record 1 has values in columns a, c, and f, and record 2 has values in columns b, d, and e?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    you right,
    i did not think of it that way.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm always right...except when I'm wrong.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. 'Last' Non Null Record in Column
    By Mpike926 in forum Access
    Replies: 7
    Last Post: 10-14-2015, 10:20 AM
  2. ComboBox, select null?
    By offie in forum Queries
    Replies: 2
    Last Post: 07-19-2013, 06:27 AM
  3. Replies: 1
    Last Post: 03-14-2011, 10:04 AM
  4. Replies: 5
    Last Post: 03-20-2010, 08:30 AM
  5. Setting NOT NULL for an column
    By cwwaicw311 in forum Forms
    Replies: 1
    Last Post: 02-21-2010, 10:30 PM

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