printing field names conditionally on mailing lables

[Guest]

I have a table containing names and mailing addresses. It also has fields indicating which group, called NEF, REF, IC the person in that record belongs to. So each of these fields has a Yes or No according to whether the person is a member of that group, like this:
Name NEF REF IC
Jones YES NO NO
Smith NO YES NO

I want to print mailing labels which have as a top line the name of the group the person belongs to - like this
NEF
Jones

REF
Smith
This involves printing the field name when field = Yes (ie, if NEF = Yes, then print field name NEF) (Obviously, I don't want it to print Yes because that will not indicate on the label which group the person belongs to!)

Can anyone help me with ideas on how to do this?

 

[Jeff Boyce]

David

You describe a typical spreadsheet construction, where the way you add a
person to another group is to create another column for that group. ?Add 6
new groups, ... add 6 new columns! You do that in spreadsheets because
there's no alternative.

Committing spreadsheet in Access means you don't get the advantages of the
power of the relational database, and simple tasks (like the one you posted)
get very very hard. For example, are you planning to rewrite your queries,
reports (labels), and code, and revise your table structure every time a
group is added or dropped?

Read up on normalization and consider redesigning your table structure along
the lines of:

tblPerson
PersonID
FirstName
...
DeliveryAddress
City
...

tblGroup
GroupID
GroupName
...

trelGroupMember
GroupID
MemberID
?BeginDate (when the person first joined)
?EndDate (when the person left the group)

With this design, a person can "belong" to zero or 100 groups, with one row
per group membership in the trelGroupMember table. ?Add 6 new groups -- add
6 new ROWS to the tblGroup table! Then show members in the trelGroupMember
table, one row per group member.

Need to know what Groups a member is in? Query the trelGroupMember table
for that members MemberID? Need to know the GroupName and PersonName, etc.?
Join the three tables in your query and select the needed fields.

Good luck!

Jeff Boyce
<Access MVP>