Prev: Help With SQL Update
Next: Docmd.SendObject
From: Jacqueline _Neo Jacqueline on 6 May 2010 04:06
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!


From: XPS35 on 6 May 2010 04:58
=?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

From: John Spencer on 6 May 2010 08:48
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!
>
>
 | 
Pages: 1
Prev: Help With SQL Update
Next: Docmd.SendObject