If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create a query for specified text in a field
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! |
#2
|
|||
|
|||
Create a query for specified text in a field
=?Utf-8?B?SmFjcXVlbGluZSBfTmVv?= wrote:
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. -- Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
Create a query for specified text in a field
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 Jacqueline _Neo wrote: 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! |
Thread Tools | |
Display Modes | |
|
|