Database Search with string portion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

IN MS Access Query Design Mode

We have 2 tables

Table-1 Supplier Table with Full Address – Mutlifields (Name, Address,
email etc)
Table-2 Short Title Supplier –Single field

Example1) Sony International, Sony Local, Global Sony, J-Sony-Singapore as
supplier name in Table A
2) Pepsi Holdings, Pepisico, Singapore Pepsi as supplier name in Table 2

These 2 companies have short names as Sony, Pepsi in Table 2.

Using MS Access Query in Design Mode , how do I link these 2 tables and
extract data from Table-1 for Sony, Pepsi

My search normally at a time is to look for data in Table-1 for 50 Short
name suppliers.

Any help will ge greatly appreciated.
 
Have you setup a relationship between the two tables? One way of doing this
is to create a lookup field in Table1 that stores the value from Table2. You
use the Lookup Wizard when setting the DataType of lookup field in Table1.

Once the relationship is setup then when you pull the two tables into the
Query Design view you will see a line joining the two tables and your
relationship is set and your query should work.
 
SELECT *
FROM SupplierTable INNER JOIN ShortTitles
ON SupplierTable.Name LIKE "*" & ShortTitles.Name & "*"

Or just add the two tables to the query grid with no join and under the Name
field set the criteria to something like the following:


Field: Name
Table: SupplierTable
Criteria: LIKE "*" & [ShortTitles].[Name] & "*"
 
Superb

Mank Many Thanks.

John Spencer said:
SELECT *
FROM SupplierTable INNER JOIN ShortTitles
ON SupplierTable.Name LIKE "*" & ShortTitles.Name & "*"

Or just add the two tables to the query grid with no join and under the Name
field set the criteria to something like the following:


Field: Name
Table: SupplierTable
Criteria: LIKE "*" & [ShortTitles].[Name] & "*"


IN MS Access Query Design Mode

We have 2 tables

Table-1 Supplier Table with Full Address – Mutlifields (Name, Address,
email etc)
Table-2 Short Title Supplier –Single field

Example1) Sony International, Sony Local, Global Sony, J-Sony-Singapore as
supplier name in Table A
2) Pepsi Holdings, Pepisico, Singapore Pepsi as supplier name in Table 2

These 2 companies have short names as Sony, Pepsi in Table 2.

Using MS Access Query in Design Mode , how do I link these 2 tables and
extract data from Table-1 for Sony, Pepsi

My search normally at a time is to look for data in Table-1 for 50 Short
name suppliers.

Any help will ge greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top