Create a query for specified text in a field

  • Thread starter Jacqueline _Neo
  • Start date
J

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!
 
X

XPS35

=?Utf-8?B?SmFjcXVlbGluZSBfTmVv?= said:
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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top