Trying to show only Non Null Values on a report

[adamtate94]

I have a table which has an ID and 45 other columns each referring to a module. I do not have the room to binid 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

 

[plog]

I have a table which has an ID and 45 other columns each referring to a module

You had me until the last part about them each referring to a module. Perhaps you can demonstrate your issue with data. Provide a sample of what you have and what you want.

 

[pbaldy]

I'll give you the same answer I did elsewhere:

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:

http://www.r937.com/Relational.html

Plus I'll add this:

http://www.excelguru.ca/node/7

 

[adamtate94]

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

 

[Minty]

Read Paul's response - we understand your concept , but your concept isn't correct, because you aren't storing the data sensibly/correctly.
Access is not an Excel spreadsheet.

 

[adamtate94]

My modules per certificate are bit types, which are bound to check boxes where people can select which modules somebody has passed, do you have any other suggestions of a way of storing this? If not, how can I display on the the non null values in text boxes in an Access Report?

Thanks
Adam

 

[Minty]

Uncle Gizmo (Moderator on here) has a number of videos that show how to handle "check list" style data-sets. The problems with your current set up are that if you add another course you would have to redesign your forms and reports manually every time it happens, and as you have found out building a report to cater for your data layout is very troublesome.
Uncle Gizmo's site - https://sites.google.com/site/msaccess457966vmfjg/

Essentially you should create three tables, one with certificate header information, one with all the available courses , and then another (junction) table that you store your CertificateID and Any CourseID that is taken for that certificate (if I have understood your requirements).