Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Omitting all null values in a report (Access 2003/SP 2)

    I couldn't quite get this with the Report Wizard, so I'm sure I need to design the report from scratch (or modify heavily). I'm not too experienced in that, so I feel a bit overwhelmed. If someone is able to give me a nudge in the right direction, I'd really appreciate it.

    I have a query where I extract comments from a survey about training. For simplicity's sake, we'll say the fields are: Trainer, Comment1, Comment2, Comment3. Not all comments have data. For example, Bob may have text in Comment1 and Comment3, while Mary has no comments at all.

    I wish to create a report per trainer, and I want only to include comments that are not null. Is there an easy way of doing that? My best approach is the Outline report that reads like:
    Bob
    Comment1: blah, blah, blah
    Comment2: <<blank>>
    Comment3: Blah, blah, blah
    Mary
    Comment1: <<blank>>
    Comment2: <<blank>>
    Comment3: <<blank>>

    Naturally, I want the final report to be:
    Comment1: blah, blah, blah
    Comment3: Blah, blah, blah

    I'll even welcome kicks to the backside.

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    If you can still change the data design, you should create a separate comments table, with 2 fields: Trainer and Comment, linked to the main table (sans the Comment fields) by Trainer. Each comment by each trainer would be a separate record, so if there are 2 comments by Bob, there would be two records for Bob, etc. That would make the report very easy to create.

    If that isn't possible, omit the labels for the comments, and set the Can Shrink property of the Comment text boxes to Yes, as well as the Can Shrink property of the Detail section. Make sure there is no vertical overlap between the text boxes.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    <hr>If you can still change the data design, you should create a separate comments table, with 2 fields: Trainer and Comment, linked to the main table (sans the Comment fields) by Trainer. Each comment by each trainer would be a separate record, so if there are 2 comments by Bob, there would be two records for Bob, etc. That would make the report very easy to create.<hr>

    That does sound nice. Alas, I have the necessary design, inefficient though it may be.

    Is there a way to create a query that duplicates this design?

    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    You could create a union query. You have to do this in SQL view:

    SELECT Trainer, Comment1 FROM [NameOfTable] WHERE Comment1 Is Not Null
    UNION
    SELECT Trainer, Comment2 FROM [NameOfTable] WHERE Comment2 Is Not Null
    UNION
    SELECT Trainer, Comment3 FROM [NameOfTable] WHERE Comment3 Is Not Null

    You'll now have a query that returns all non-blank comments in one column.

  5. #5
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    I am thrilled and humbled. I'm still learning about the power of Access (and espeically SQL).

    The query works wonderfully, but I left out some information. Sorry.

    I'd like to identify each comment by its field. So, the final output would be something like:

    <table border=1><td>Trainer</td><td>Comment Type</td><td>Comment Text</td><td>Bob</td><td>Relevance Comment</td><td>The class was very useful to me</td><td>Bob</td><td>Environment Comment</td><td>The classroom was too warm</td><td>Carol</td><td>Relevance Comment</td><td>I'll never use this material in my job</td></table>

    Is that possible to do with SQL?

    I've learned so much about Access lately. I've also learned that a self-taught course's idea of "Advanced" training is not necessarily so advanced. I didn't learn anything about detailed report building, forms, data pages, and complex queries. So, imagine my joy at learning much more beyond a simple CD.

    Kevin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    Is Comment1 always Relevance, Comment2 Environment etc.? If so, you can use

    SELECT Trainer, "Relevance Comment" AS CommentType, Comment1 AS CommentText
    FROM [NameOfTable]
    WHERE Comment1 Is Not Null
    UNION
    SELECT Trainer, "Environment Comment", Comment2
    FROM [NameOfTable]
    WHERE Comment2 Is Not Null
    UNION
    SELECT Trainer, "Other Comment", Comment3
    FROM [NameOfTable]
    WHERE Comment3 Is Not Null

    Note that the column headings have to be defined only in the first query listed in the union query

  7. #7
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    Wow.

    I'm becoming more and more impressed with the robustness of SQL. Thanks a bunch, Hans. That is exactly what I need. I'm adding this thread to my favorites. I think there is enough here to launch any future SQL statements I decide to concoct.

    Kevin

  8. #8
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    I crafted my beautiful queries, which helps out immensely with my report.

    But, I discovered that the CommentText field truncates (I haven't counted it, but I assume at 255 characters). I tried a search, but I didn't see anything that would help me in figure this out (or I'm just too dense).

    I had hoped to view the query in design mode so I could change the data type. That is not even an option. Can you define the type of data within a query?

    I am almost at the point where I can stop being such a bother.

    Kevin

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    You cannot view a union query in design view because it consists of several queries.
    The standard UNION will return unique values only, and Access truncates a memo field to 255 characters in any query that aggregates or formats the field.
    Since your query will return unique values anyway, you can replace both occurrences of UNION with UNION ALL. You should then get the complete contents of the memo field.

  10. #10
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Omitting all null values in a report (Access 2003/SP 2)

    I'm sure that'll strike me as a "duh" moment when I sit down and fully learn SQL.

    In the meantime, you've helped me out a lot. My brain officially hurts now, and it's time to go home and have a few shots of Ouzo. Thanks very much.

    Kevin

Posting Permissions

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