Results 1 to 8 of 8
  1. #1
    adamtate94 is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2016
    Posts
    7

    Trying to show only Non Null Values on a report

    I have a table which has an ID and 45 other columns each referring to a module. I do not have the room to bind 45 objects onto the report and also there will only be around 15 NON NULL values which need to be printed on the report. How do i populate 15 text boxes with only NON NULL values and ignore the rest, rather then putting 45 text boxes?


    Thanks for the help in advance

  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
    It sounds like you've created a spreadsheet rather than a relational database. The 45 fields should likely be 15 records in a related table. The concept is called normalization:

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

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So on each record out of the 45 fields, only 15 will have a value and it could be different fields with values from record to record?

  4. #4
    adamtate94 is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2016
    Posts
    7
    Hi,
    The concept is: I have a table that has a certificateID, the certificate had a training course which somebody has completed, a person can complete different modules out of 45 on the course, the maximum modules that someone is likely to complete is around 15, so I don't want to be putting 45 text boxes into the report, I need to make it so that the report only displays the modules the person has passed and ignored the rest, which are NULL in the table.
    Thanks

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    One way might be to create a report table with UserID, CourseID and 15 generic fields (Module1, Module2, ... Module15) Then when you run the report, first loop through your records with VBA code and move the UserID, CourseID and any completed Modules to those fields in the table. Then base your report on that report table.

    Or you could also have 1 long text field for completed Modules and as you loop through the records, append all the modules they passed into 1 field so would be like:
    UserID, CourseID, ModulesPassed
    001, 4356, Module1, Module3, Module6, Module15
    001, 6423, Module4, Module5, Module9
    002, 4356, Module3, Module6
    ...

  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
    I'll get out of the way so Bulzie can help you create an un-normalized mess.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You normalize until it does not make since to normalize. Not sure how he wants it to look so giving options that might work. If you know of a better way by all means share, I don't claim any titles...

  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'll reword the requirement:

    We sell 45 different products, but people usually only buy 15. I've got 45 different fields, 1 for every product we sell. How do I only display the 15 that each person bought instead of all 45?

    You still thing the design is okay? I don't, and posted a link that describes the correct concepts. I apply the rule of thumb that if they add a 46th module, will it require design changes to tables, forms, etc? If so, the design isn't correct. In a normalized design, the developer isn't required to be involved when a new module/product is added.
    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. Replies: 5
    Last Post: 03-08-2016, 07:25 AM
  2. Replies: 7
    Last Post: 12-04-2013, 01:55 PM
  3. Show null values in records
    By Nola-Edu in forum Queries
    Replies: 1
    Last Post: 04-10-2013, 12:36 PM
  4. show companies with null values
    By jamo in forum Programming
    Replies: 11
    Last Post: 11-06-2012, 08:11 AM
  5. Replies: 2
    Last Post: 08-01-2011, 09:30 AM

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