Tricky problem cross-referencing two tables

[Guest]

Ok. Here goes:

Table 1 contains at most, 14 characters of a value i need to reference in
Table 2 (which is, in turn, linked to many others by a primary). Some of the
matching values from each table are less than 14 characters (i.e. a name:
John Smith v. John Smith), so, no problem. Some in Table 2, however, exceed
14 characters, so that their match in Table one is not sufficient for a
normal query (i.e. Jonathon B Smi v. Jonathon B. Smith).

Now, I know Access will let me look for these one at a time using a wildcard
in the "Find" function (i.e. Jonathon B Smi*), and so, will return all rows i
seek, but I need to repeat this about 900 times, returning all matching
values for Table 1 from Table 2 and the task is more than daunting.

Can anyone help? I'm about to throw my laptop out the window!

 

[John Spencer (MVP)]

Let me know the address and time, so I can catch it. Shame on you! Abusing the
poor computer.

You can do this with a non-equi join


SELECT *
FROM Table1 Inner Join Table2
ON Table1.TheField = LEFT(Table2.TheField,14)

or


SELECT *
FROM Table2 Inner Join Table1
ON Table2.TheField LIKE Table1.TheField & "*"

Or

SELECT *
FROM Table1, Table2
WHERE Table2.TheField LIKE Table1.TheField & "*"

 

[Guest]

Thanks a million, John, for so promptly giving me the answers you did. From
what I know of Excel, I can see the criteria you're suggesting should return
for me the results I'm seeking, but try as I may, I can't seem to apply it.
(I'm an advanced user with Excel but an absolute Access idiot)

The results I'm getting seem to match what i was getting before without
having applied the criteria.

is there anyway you could try to explain this again, assuming i'm in my
first week of access? THANK YOU!!!

 

[John Spencer (MVP)]

Can you post a sample of the query you were proposing to use?

Open the Query in design view, switch to SQL view, and copy and paste the text
to the newsgroup.

Perhaps I or someone else can then suggest a modification to it.

 

[Guest]

Again, thanks a whole lot John. That got us through to yet another problem
.... lol.

we converted a flat file to text delimited and in the process, access didn't
keep everything within one record, so i need to develop criteria to eliminate
the
repetition and combine four records into one: it's turned into an a-p grid
with a-d being column one and m-p being column four. abcd are = so only a.
efgh
are = so only e. i,j,k,l should combine into i. m-p should be eliminated
(access' complimentary line number). thanks!

I hope this explanation adequately describes the problem. And please feel
free to say "looks like you need professional help". We may just...

 

[John Spencer (MVP)]

I'm sorry, but I don't understand your explanation at all. You may be right and
it may be time to get a professional in to help you solve your problem. That
may not be cheap, but it may be cheaper than all the time you are spending now
to get through your problem.