Multi-Value Field Query

[Guest]

Hi,

Any help would be greatly appreciated.

I have a large output report from a database. I am trying to narrow down
the information in it. The goal is to take a list of users and isolate the
rows that apply to those users. I have one table, 'Users', with their names
populated. I have another Table 'RawData' with many fields, but I need to
query from three in particular, 'CreatedBy', 'Owner', 'Employees'.
'Employees' is a multi-value field, meaning their are multiple names in it.
What is the best way to run a query against those three fields, that will
return the ID of the row that has a user in one of those three fields. I
currently have two queries, one that gets the id from the 'CreatedBy' and
'Owner' and then I have a third query that runs a union on those. I do not
know how to run a query for that list of users against the 'Employees' field
(essentially to search within the field). Can someone please help me out
with that last query, or if you know how to combine all three into one?

Thanks, I know you guys are the best, because I've been able to find answers
on here before.

 

[Allen Browne]

In query design view, add .Value to the multi-value field name in the Field
row.

For example, if the field is named Employee, use:
Employee.Value

This will give you a record for every employee. You can then use Criteria
under this to limit it to one employee.

If you are serious about designing in Access, I suggest you create your own
relational tables instead of using these multi-valued fields. Although
Access does create super-hidden relational tables behind the scenes, you are
better of with exposed tables as subforms instead of these little monsters.

 

[Guest]

Thanks for the help, but I am not sure if it is what I am after, can you
perhaps work with me for another round?

So a little bit more background. I am trying to use Access as a filtering
tool, rather than as a stand-alone DB. Each week I get a dump of data from a
proprietary system. That system outputs about 70000 rows. Of those rows I
am really only interested in about 100 of them, the 100 that apply to the
employees in my location. The rows I am interested in have them listed in
three areas 'CreatedBy', 'Owner', 'Employees'. Now they can be listed in all
3 columns, or just in 1 or 2. Now the 'Employees' column is not formatted
appropriately (I wish it was relational) and it lists all employees involved
in the project divided by commas instead of assigning each their own column.
I have about 20 employees so I don't want to enter a parameter value for each
one.

I want to be able to query that column with multiple names in it for each
one of my employees, e.g. If I have John, Bob, and Jenny and Jenny works on
project A with Dean, Mark, & Mary. Then the column would look like, Dean,
Mark, Mary, Jenny. I want to return all rows with Mary in it. On the same
query I would also like to look for all rows that have Bob and John in them
as well. I am thinking that when I import the data to access I may have to
break out that column, but I was hoping to avoid doing that each week.
Hopefully this background helps a little bit more.

As a quick recap, my goal is to have a single query that will take a list of
names and identify every row in which those names has a hit in 3 different
columns, one of which has many names in it including those not on my list.
If anyone knows the SQL for this I would greatly appreciate it. Or if you
know how to have a query to just query the multi-name column.

Thanks.

 

[Allen Browne]

Okay, my previous reply is not relevant. It was directed at what Microsoft
calls a Multi-Valued field, which is not the same as what you have (a
comma-separated list of items in one field.)

You can query for Jon in a field that contains a list of names like this:
Like "*Jon*"
However, that's also going to match Jonathan, Jones, Aijon, etc, so it may
not be what you need.

If you're not too worried about that, you could find the rows that have both
Jon and Mary as:
Like "*Jon*" And Like "*Mary*"

If you do want to parse each name and write to a related table, it can be
done with Split(). This doesn't work very well in the query interface in
Access, so you probably need to write a VBA function that does this for you.
OpenRecordset on the source table and the (related) target table. Loop
through the source records. Split() into an array. Write a target record for
each item in the array (from LBound to UBound.)