It would be possible to do this with your current table structure, but it would require some code to build the value list by looping through the columns per row and examining the value of each for TRUE or FALSE.
The problem stems from the fact that your table is incorrectly designed. By having a separate Boolean (Yes/No) column for each value, data is being 'encoded as column headings'. A fundamental principle of the database relational model is the Information
Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.
This is a common error by inexperience relational database developers, but an error nevertheless. The correct structure would be to firstly have a table Achievements say:
Achievements
….AchievementID (PK)
….Achievement
The many-to-many relationship type between your current table, let's call it Members for this example, and the Achievements table should then be modelled by another table which resolves the relationship type into two one-to-many relationship types:
MemberAchievements
….MemberID (FK)
….AchievementID (FK)
The primary key of this table is a composite of the two foreign keys. If necessary the table can include further non-key columns representing attributes of the relationship type, e.g. a DateAchieved column.
The interface for data entry would be a members form, in single form view, and within it a member achievements subform, in continuous forms view, linked to the parent form on MemberID.
It is possible, with a little code, to automate the recasting of the existing data into a set of correctly normalized tables as described above. You'll find an example in UnencodeColumns.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
With the corrected model it would be a simple task to return the achievements per member by a simple query which joins the three tables. This could then be used as the basis for a report. However, the achievements per member would be listed as a
vertical list, and consequently rather wasteful of space. They can be concatenated into a single value list by using a concatenation function. You'll find an example in my same OneDrive folder in Concat.zip. However, my own preference in
case like this is a code-free solution which uses a parent report based on members, and within it a multi-column subreport with across-then-down column layout, based on a query which joins the MemberAchievements and Achievements. In the same OneDrive
folder you'll find an example in DatabaseBasics.zip. In this, the final form in the section on 'retrieving data from the database' illustrates how to build such a subreport.
Ken Sheridan, Stafford, England
