One to Many Lookup

[rollvoer99]

I have two tables in a select query, tblRoot and tblParts. In the tblparts
there is over 100k part numbers. The tblroot only has about 200 but will
change based on demands.

tblroot has the beginnings of part numbers and the tblparts has the entire
part number. What I am trying to do is use tblroots as the base within the
tblParts. If this is confusing please let me explain.

For example
32112 would be the [base] field within [tblroot]. The amount of numbers can
change. Could be 5 could be 10. Could have dashes or not.

I want to take the 32112 and look in [PN] field of [tblParts] and and find
all the PN's that have it in them.

tblroot tblParts
Base PN
32112 32112-4
32112589
32112-4-100
49-500 49-500-1
49-500-2

Is there anyway to obtain this one to many type lookup. I have search and
tried many things only to do it manually. Any help would be appreciated.

 

[Klatuu]

I would suggest you add a field to tblParts that is the same as the base
field in tblRoot. Then you can use that field to relate the tables. Then
you can create a query with the tables joined on that field.

 

[John Spencer]

You can use a non-equi join to do this. A non-equi join cannot be set up or
displayed in the query design view. You must use the SQL view.

SELECT TblRoot.Base
, tblparts.PN
FROM tblParts INNER JOIN TblRoot
ON tblParts.PN LIKE tblRoot.Base & "*"

You can also do this with the much less efficient practice of adding both
tables with no join and then using the criteria in a where clause
SELECT TblRoot.Base
, tblparts.PN
FROM tblParts , TblRoot
WHERE tblParts.PN LIKE tblRoot.Base & "*"

In the query grid
-- Add both tables
-- Delete any join lines
-- Add your fields
-- Under tblParts PN field set the criteria to
---- LIKE [tblRoot].[Base] & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[rollvoer99]

That would not work as the tblroot with the field base may change. One time
I could be searching for the first 3 characters and the next time 4. I still
need a join point and that doesnt seem feasible. If you still think it would
work can you please give some direction.

 

[rollvoer99]

Working with your first suggestion and the last I find that this is not a ver
effiicient process, but works none the less. I greatly appreciate your help.

Cheers

 

[John Spencer]

Do you have indexes on the fields Base and PN? If not, the addition of
indexes could significantly improve the speed.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

You can use a non-equi join to do this. A non-equi join cannot be set up or
displayed in the query design view. You must use the SQL view.

SELECT TblRoot.Base
, tblparts.PN
FROM tblParts INNER JOIN TblRoot
ON tblParts.PN LIKE tblRoot.Base & "*"

You can also do this with the much less efficient practice of adding both
tables with no join and then using the criteria in a where clause
SELECT TblRoot.Base
, tblparts.PN
FROM tblParts , TblRoot
WHERE tblParts.PN LIKE tblRoot.Base & "*"

Why do you say, "less efficient practice"? The two queries are
logically equivalent and, I think I am correct in saying, considered
as equivalent by Jet's optimizer; unless I am reading the file
incorrectly, the SHOWPLAN.OUT results are identical for both queries:

--- temp query ---

- Inputs to Query -
Table 'tblParts'
Table 'TblRoot'
- End inputs to Query -

01) Inner Join table 'TblRoot' to table 'tblParts'
using X-Prod join
then test expression "tblParts.PN ALike tblRoot.Base & '%'"

Note both plans contains both terms "Inner Join" and "X-Prod join".

Further note I changed your

LIKE "*"

to

ALIKE '%'

so that it is ANSI Query Mode neutral ;-)

I realize that the practice of using the old style join is less
explicit than the INNER JOIN syntax but still I'm puzzled by your use
of the word 'efficient'.

Jamie.

--

 

[John Spencer]

I could be wrong.

It just seems to me that when I've used the second syntax - a cartesian
join - with large tables the speed of the second query is noticably slower.
If you say the showplan results are identical, then perhaps I am mistaken.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..