I need to populate a field using "like" (1 Viewer)

Randy · Dec 20, 2019

I have a file from a bank which I load into tblBank. fields are
date
amt
desc

I have a table tblVendor that has fields
Vendor
Category

I need to link the tblbank.desc to the tblvendor.vendor to bring in the vendor and category

but of course the desc in the tblbank is something like this
1/12/2020 mcdonalds Lawrenceville, ga
1/5/2020 LA Fitness monthly fee California
etc.

so I need to basically do a "like" command and if the desc has a match to a vendor bring in the vendor and category.

I cannot figure out how to do this in my query.

theDBguy · Dec 20, 2019

Hi. You would have to do it in SQL view. For example:

Code:

SELECT tblBank.*, tblVendor.Vendor FROM tblBank
INNER JOIN tblVendor
ON tblBank.Desc Like "*" & tblVendor.Vendor & "*"

Randy · Dec 20, 2019

ok that worked like a charm. one question, I have 2,436 rows in tblbank, but after I ran the sql statement I only got 1,476 rows. So I have some missing vendors in TblVendor.
can I use a different JOIN and get all 2,436 rows, and the 960 rows would just show no vendor?

theDBguy · Dec 20, 2019

Randy said:
ok that worked like a charm. one question, I have 2,436 rows in tblbank, but after I ran the sql statement I only got 1,476 rows. So I have some missing vendors in TblVendor.
can I use a different JOIN and get all 2,436 rows, and the 960 rows would just show no vendor?

Yes. Change the INNER JOIN to a LEFT JOIN.

Randy · Dec 20, 2019

thanks I had figured that out, but I have been working on a small problem,
tblbanks as 2,721 records
the query produces 2,729 records

I am researching the 8 record differnce

isladogs · Dec 20, 2019

8 duplicates?
If you don't need the query results to be editable, try SELECT DISTINCT …..

theDBguy · Dec 20, 2019

Randy said:
thanks I had figured that out, but I have been working on a small problem,
tblbanks as 2,721 records
the query produces 2,729 records

I am researching the 8 record differnce

Yeah, sounds like a vendor matched with more than one bank or a bank matched more than one vendor, and there were overlaps.

I need to populate a field using "like" (1 Viewer)

Randy

Registered User.

theDBguy

I’m here to help

Randy

Registered User.

theDBguy

I’m here to help

Randy

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread