Adding Field with an Expression

[AuditorCMM]

I currently have a database similar to the following (but with many more
committees):

Name Asset Committee
Mike Smith x
John Doe
Mary Jones x

*(x's represent checkboxes that are checked)

I would like to create another table/query based on this information that
would add a column and display certain text if the checkbox is checked. For
example, based on the above data, I would like to see:

Name Asset Committee Committee
Mike Smith x Asset
John Doe
Mary Jones x Asset

Is there an expression that I could put in the new column to display that
text based on whether or not the checkbox is checked for each individual?

Thanks!

 

[OfficeDev18 via AccessMonster.com]

By definition, a checkbox denotes a Boolean field. Ergo,

"... IIf([Asset Committee]=True, "Asset, ",", ") & IIf([Some Other Committee]
=True, "Some Other, ",", ") As Committee..."

This way, if Asset is true, the legend "Asset, " will appear, otherwise, the
legend ", " will appear in order to display the 'place marker' for the
missing committee. If Some Other Committee is false, then, and Asset is true,
the field will display "Asset, , ". If both are true, it will display "Asset,
Some Other,". Etc.

(I wouldn't sweat the final comma; place markers are very helpful.)

HTH

 

[Jerry Whittle]

Hi,

By Database do you actually mean Table with something like:

Name Asset Committee Finance Committee Christmas Committee and so on?

If so, step away from the keyboard! You are about to commit spreadsheet.

What you need is a minimum of three tables. Names, Committees, and Members.
In Names you put information about the people. In Committees you put
information about the Committiees.

Now for the fun part. As one person can be on more than one committee and a
committee can have more than one person, you have a Many-to-Many
relationship. To fix this you need a table known as bridging, linking, or
joining table depending on whose book that you read. In this Members table
you list one person and one committee per record.

If this doesn't make sense, I implore you to get some training or invest in
a copy of a good book on the subject like Database Design for Mere Mortals by
Hernandez.

 

[Jerry Whittle]

One other idea. There's a membership database template available from MS that
just might fit the bill or give you some good ideas.

http://office.microsoft.com/en-us/templates/TC010185841033.aspx