Filter Fields Containing Values Found in Another Table

[shm135]

Please see the following scenario:

[Tickets].[Description] contains the following records:

1. There was bad weather.
2. Food illness
3. This was a human error.
4. Weather issue
5. Bad timing of arrival

[Exceptions].[Reason] contains 3 records:

1. Weather
2. Human Error
3. Bad Timing

I want to filter [Tickets].[Description] to show only those records that contain any value found in [Exceptions].[Reason]- so, in the example above, Record 1, 3, 4 and 5 should be returned in my query.

Any advice would be greatly appreciated!

Thanks!

 

[Bob Barrows]

Please see the following scenario:

[Tickets].[Description] contains the following records:

1. There was bad weather.
2. Food illness
3. This was a human error.
4. Weather issue
5. Bad timing of arrival

[Exceptions].[Reason] contains 3 records:

1. Weather
2. Human Error
3. Bad Timing

I want to filter [Tickets].[Description] to show only those records
that contain any value found in [Exceptions].[Reason]- so, in the
example above, Record 1, 3, 4 and 5 should be returned in my query.

Any advice would be greatly appreciated!

Thanks!

Here's one way:
select description from tickets
where exists (
select * from exceptions
where description like "*" & reason & "*"
)

 

[shm135]

Please see the following scenario: [Tickets].[Description] contains the following records: 1. There was bad weather. 2. Food illness 3. This was a human error. 4. Weather issue 5. Bad timing of arrival [Exceptions].[Reason]contains 3 records: 1. Weather 2. Human Error 3. Bad Timing I want to filter [Tickets].[Description] to show only those records that contain any value found in [Exceptions].[Reason]- so, in the example above, Record 1, 3, 4 and 5 should be returned in my query. Any advice would be greatly appreciated! Thanks!

Bob- Thank you, that worked perfectly!

 

[John W. Vinson]

Please see the following scenario:

[Tickets].[Description] contains the following records:

1. There was bad weather.
2. Food illness
3. This was a human error.
4. Weather issue
5. Bad timing of arrival

[Exceptions].[Reason] contains 3 records:

1. Weather
2. Human Error
3. Bad Timing

I want to filter [Tickets].[Description] to show only those records that contain any value found in [Exceptions].[Reason]- so, in the example above, Record 1, 3, 4 and 5 should be returned in my query.

Any advice would be greatly appreciated!

Thanks!

I see Bob gave you an excellent answer, but I'd like to "unask" the question.
Your Reason table is going to be awfully limiting! Suppose Descrption contains

6. Major thunderstorm and tornado alert.
7. Somebody goofed.
8. Arrival came after it was expected.

A human would classify these as Weather, Human Error and Bad Timing
respectively - but since they didn't include the phrasing you expect they
would have been missed.

Parsing free, unconstrained text is an advanced natural-language programming
exercise, and is FAR from trivial - in fact I'd say (Watson's performance on
Jeopardy to the contrary notwithstanding) it's still an unsolved problem.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gene Wirchenko]

On Wed, 17 Oct 2012 15:56:24 -0600, John W. Vinson

[snip]

I see Bob gave you an excellent answer, but I'd like to "unask" the question.
Your Reason table is going to be awfully limiting! Suppose Descrption contains

6. Major thunderstorm and tornado alert.
7. Somebody goofed.
8. Arrival came after it was expected.

A human would classify these as Weather, Human Error and Bad Timing
respectively - but since they didn't include the phrasing you expect they
would have been missed.

Parsing free, unconstrained text is an advanced natural-language programming
exercise, and is FAR from trivial - in fact I'd say (Watson's performance on
Jeopardy to the contrary notwithstanding) it's still an unsolved problem.

I was thinking the same.

If you need the class of error, consider having a column for that
and a column for text. Naturally, there is no guarantee that freeform
text will match the error class.

What if it is more than one? "A combination of weather and human
error led to...".

Sincerely,

Gene Wirchenko