Help!

[Kady]

Im trying to filter a single entry out of a multiple entry field. In the
properties section we have abreviations for the different investment
properties we manage.
Ex: Six, Stone, Mnt, TPL, Cyp, Wood, Vin, Vic, Ect. Im trying to run a
filter that would give me each contact that had "Stone" in the properties
section, but when I run the filter it only gives me the select few contacts
that have only "Stone" in the properties section, not the others who have
many entries including Stone. Can any one tell me the correct way to filter
this.

 

[John W. Vinson]

Im trying to filter a single entry out of a multiple entry field. In the
properties section we have abreviations for the different investment
properties we manage.
Ex: Six, Stone, Mnt, TPL, Cyp, Wood, Vin, Vic, Ect. Im trying to run a
filter that would give me each contact that had "Stone" in the properties
section, but when I run the filter it only gives me the select few contacts
that have only "Stone" in the properties section, not the others who have
many entries including Stone. Can any one tell me the correct way to filter
this.

Is this an Access2007 "Multivalue Field"? Or is it a simple Text field with a
series of comma separated values?

If the latter, you're paying the penalty for violating the fundamental
principle that fields should be "atomic" - having only one value. If you're
searching this field with a criterion of

"Stone"

then yes, you will only match those records where the field consists of

"Stone"

The text strings

"Stone"

and

"Wood, Water, Fire, Stone, Metal"

are different text strings. You could get a partial solution by using
wildcards; a criterion

LIKE "*Stone*"

will find records where the five letters STONE in order occur somewhere in the
string; this may be a problem if you search for "VI" for instance, because it
will find not only VI as an entry, but also VIN, VIP and EVIL.

You REALLY need to model this as a many to many relationship with three
tables: one for your main table; a second table of properties, containing one
record for SIX, one for Stone, etc; and a third table with one record for each
property involved with a main table record, consisting of a foreign key to the
main table's primary key and a foreign key to the table of properties.

 

[roccogrand]

Kady,

In reading your question and John's response I discovered an interesting
thing about multivalue fields in Access 2007.

If you create a multivalue table with names and their corresponding
initials, and an autonumber field then Access will add a .Value subfield
automatically. This value turns out to be the key to filtering multivalue
fields.

I discovered this with a three-column table with an Autonumber field
(StaffID), a multivalue field of people's names (StaffName), and a field of
the person's initials (StaffInitials). Example: 2; George Washington; GW.

If you want to filter the records for George Washington, you don't use the
person's name or their initials instead you use the autonumber value. In
addition, you don't use the field name but the .Value field in the query. In
my case, Access named this field AssignedTo.Value automatically.

If you follow this, you will see two fieldnames in the query wizard. In my
case they are "AssignedTo" and "AssignedTo.Value". I reference the subfield
"AssignedTo.Value" in my query/filter.

In the lookup table "lkuStaffName", George Washington is record 4. This row
appears in the lookup table as: 4, George Washington, GW.

In the query wizard, the Criteria in the AssignTo.Value column is set to 4.
Note that I am not using quotation marks or any other characters around the
number 4.

Before discovering this "feature", I tried using wildcards and other fields
in the table but none of them worked.

HTH when you use the Access 2007 multivalue field in the future. This
feature is new but so far I have found it easier to use than creating a 5th
Normal Form. I guess MS succeeding in innovating their technology again.

David N.