Create a query for specified text in a field

[Jacqueline _Neo]

Hi there,

I have a number of fields in my query. They are Customer, Document, Title.
In this query, i have include a input dialog for the user to enter the
customer name.
In the Customer field, there are several customers in one record that uses
the same document.
Example,

Customer | Document | Title
ABC/3H/SST | 12346 | This is a test procedure
ABC/3H | 11234 | Procedure

I would like the output shows a list of the documents used for the specified
customer. Example,
I want to know the list of documents that is for Customer: ABC so the output
will be

Customer | Document | Title
ABC | 12346 | This is a test procedure
ABC | 11234 | Procedure

For Customer : SST

Customer | Document | Title
SST | 12346 | This is a test procedure


How do I go about creating this scenario for this case? I tried to set the
criteria : Like "SST*" or Like "ABC*" under the Customer field but the output
shows everything and did not do a filter.

Pls help.

Thanks!

 

[XPS35]

Hi there,

I have a number of fields in my query. They are Customer, Document, Title.
In this query, i have include a input dialog for the user to enter the
customer name.
In the Customer field, there are several customers in one record that uses
the same document.
Example,

Customer | Document | Title
ABC/3H/SST | 12346 | This is a test procedure
ABC/3H | 11234 | Procedure

I would like the output shows a list of the documents used for the specified
customer. Example,
I want to know the list of documents that is for Customer: ABC so the output
will be

Customer | Document | Title
ABC | 12346 | This is a test procedure
ABC | 11234 | Procedure

For Customer : SST

Customer | Document | Title
SST | 12346 | This is a test procedure


How do I go about creating this scenario for this case? I tried to set the
criteria : Like "SST*" or Like "ABC*" under the Customer field but the output
shows everything and did not do a filter.

Pls help.

Thanks!

So there are 2 or more customer names in 1 field?
If the table used for the query has the same structure, you must
consider to redesign your database to a structure with at least 3
tables:
- Customer
- Document
- DocumentForCustomer

In that way theren wil be only one customer in the customer field in
DocumentForCustomer, which makes selecting a customer easy.

 

[John Spencer]

This is a tough problem to solve for more than one customer at a time due to
the combination of multiple customers in one field.

For a SINGLE customer
Parameters [Which Customer] Text;
SELECT [Which Customer] as TheCustomer
, Document
, Title
FROM [SomeTable]
WHERE Customer LIKE "*" & [Which Customer] & "*"

IF you need to do this for multiple customers at once and cannot change the
structure of your source table, your best solution would be to create a table
of the unique customer values and use that in a non-equi join.

SELECT [CustomerList].Customer
, [SomeTable].Document
, [SomeTable].Title
FROM [SomeTable] INNER JOIN [CustomerList]
On [SomeTable].Customer LIKE "*" & [CustomerList].Customer & "*"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County