none
MS Access summary query for multiple yes/no fields RRS feed

  • Question

  • Could really use some help with this one.  Using MS Access 2016.  I have multiple fields that have a yes/no response.  i.e Amaze Journey, Amuse Journey, Get Moving Journey, badges, ect.  I want to return a report that will return a field that has the items that have been checked in a row.  Example.  A girl has completed two of the three journeys, (They have a yes in the appropriate field)  the report will return her name and the journeys that she completed separated with a comma  ( Susan - Amaze,Get Moving   or Sam - Amaze,Amuse.  Any help would be appreciated.

    Friday, June 14, 2019 5:10 PM

All replies

  • Eileen -

    what are the tables from which you are drawing these data?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, June 14, 2019 7:13 PM
  • 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

    Saturday, June 15, 2019 4:33 PM