Use Like from another table as criteria

[skwilliams]

I have a query to which pulls data from certain foreign countries. The country data comes in through the city field in my table [tblMain]. I have a reference table [tblReference] with a list of countries [ForeignCountries]. This list is constantly being updated. I set my query criteria to (Like "*" & [tblReference]![ForeignCountries] & "*") **without the parenthesis.

It only seems to pull data if the country is the only thing in the field. If it contains characters before or after it, nothing shows.

Any ideas??

 

[JHB]

I've tried with some sample tables, it works ok. See attached picture.
(Are you sure the countries names are spelled correct? )

SELECT CityAndCountry.AField, CityAndCountry.CityAndCountry
FROM CityAndCountry, Country
WHERE (((CityAndCountry.CityAndCountry) Like "*" & [Country]![Country] & "*"));

 

[skwilliams]

Here's the SQL

SELECT tblMain.dtDate, tblMain.Company, tblMain.Attention, tblMain.FName, tblMain.LName, tblMain.Address1, tblMain.Address2, tblMain.Address3, tblMain.City, tblMain.State, tblMain.Postal, tblMain.Country, tblMain.Cat, tblMain.Sample1, tblMain.Sample2, tblMain.Sample3, tblMain.Sample4, tblMain.Sample5, tblMain.Sample6, tblMain.Sample7, tblMain.Sample8, tblMain.Sample9, tblMain.Sample10, tblMain.Barcode, tblMain.OrderNumber, tblMain.ShippingMethod, tblMain.Count, tblMain.FileName, tblMain.PCSent, tblMain.SamSent
FROM tblReference INNER JOIN tblMain ON tblReference.ForeignCountries = tblMain.City
WHERE (((tblMain.City) Like "*" & [tblReference]![ForeignCountries] & "*"));

A list of countries from the reference table ForeignCountries
Russia
Ukraine
Brazil
Chelyabinsk
France
Germany
Poland
Lithuania
Cheboksary
Czech
Belgium
Italy
Austria
Australia
Belize
Argentina
Columbia
Ukraina
Bolivia
Honduras
Venezuela
Spain
Zhlobin

and a few samples from the city field in the main table to be pulled.

Kurgan Russia 640006
Makeevka-ukraine
Belaya Tserkov Kiev Region Ukraine
Zhlobin
Kharkov Ukraine
Dzhankoj Ukraine

The only one to pull is Zhlobin. I'm guessing it's because there is nothing preceding or following it.

 

[jdraw]

tblReference INNER JOIN tblMain ON tblReference.ForeignCountries = tblMain.City
WHERE (((tblMain.City) Like "*" & [tblReference]![ForeignCountries] & "*"))

I think the highlighted part (especially the "=") is causing you problems. You joining based on these fields being equal, but your WHERE clause is looking for partial match.

Haven't looked at it in detail but will do so later.

Have to go out.

Can you give us some data from tblMain.City?

 

[skwilliams]

That's the data at the bottom of my last post.

Kurgan Russia 640006
Makeevka-ukraine
Belaya Tserkov Kiev Region Ukraine
Zhlobin
Kharkov Ukraine
Dzhankoj Ukraine

Thanks.

 

[jdraw]

You have to restructure the data to do what you want.
You need to separate Cities from Countries in the ForeignCountries list.
Zhlobin is a city not a Country.
same for Cheboksary.

There is nothing consistent in the City field of your tblMain. You join tables on fields, where the fields in each table have the same data type and values. You don't have that set up in your current tables.

 

[skwilliams]

Unfornately this is the way the data is coming into us from the customers.

Not sure the best way to separate without doing it manually. Not really an option.

 

[jdraw]

Please post some sample data for each of your inputs as csv or txt files.

Do you have regular inputs from these suppliers? Do you have some say in the format in which they should be received?

As with any ongoing process, better to design for efficiency at the beginning rather than reinventing a process every time a new input arrives.

 

[skwilliams]

Attached is the txt file.

These files come in daily from our corporate office. We have no say in the layout.

 

[skwilliams]

If a write the variables into the SQL rather than selecting the list from tblReference, it works perfectly.

Here's the SQL
SELECT tblMain.dtDate, tblMain.Company, tblMain.Attention, tblMain.FName, tblMain.LName, tblMain.Address1, tblMain.Address2, tblMain.Address3, tblMain.City, tblMain.State, tblMain.Postal, tblMain.Country, tblMain.Cat, tblMain.Sample1, tblMain.Sample2, tblMain.Sample3, tblMain.Sample4, tblMain.Sample5, tblMain.Sample6, tblMain.Sample7, tblMain.Sample8, tblMain.Sample9, tblMain.Sample10, tblMain.Barcode, tblMain.OrderNumber, tblMain.ShippingMethod, tblMain.Count, tblMain.FileName, tblMain.PCSent, tblMain.SamSent
FROM tblMain
WHERE (((tblMain.dtDate)=Date()) AND ((tblMain.City) Like "*Russia*")) OR (((tblMain.City) Like "*Ukraine*")) OR (((tblMain.City) Like "*Korolev*")) OR (((tblMain.City) Like "*Moscow*")) OR (((tblMain.City) Like "*Kiev*")) OR (((tblMain.City) Like "*Belarus*")) OR (((tblMain.City) Like "*Zhlobin*")) OR (((tblMain.City) Like "*Samara*"));

and the results

FName LName Address1 Address2 Address3 City State
ANTONUK OKS KOLTSOVA 15-A; 264; KIEV 03194 UA UKRAINA KIEV AE
Laure Sitek 490 Creek Bend Dr Moscow Mills MO
Lexi Ebel 1008 W A St Moscow ID
Elena Golyakova Svyatoozerskaya St 21-190 Moscow-russia 111625 AL
Lyubov Gergel Skulptora Mukhinoy-5-332 Russia 119634 Moscow AL
Lyndsay Birmelin 10 Countryside Dr Moscow PA
Brianna Johnson 1579 S Lenter St Apt 9 Moscow ID
Lyudmila Bolshakova Odoevskogo 38/63 Minsk-belarus FL
Lyudmila Bolshakov Odoevskogo 38/63 Minsk-belarus FL
Mikhail Mescheryakov Kirova Street 375-8 443114 Russia Samarskaya Obl Samara AL
Sergey Gusev Pr-t Kosmonavtov 6-90 141075 Russia Korolev NY
Mariya Zadernovskaya Zelinskogo 33-188 Velikiy Novgorod Russia CT
Kate Vodlinschuk 142793 Russia Moscowvatutinki 44-66 44-66 Moscow CA
Tiffani Standley 1136 N Polk Ext Apt 805 Moscow ID
Antonov Alex R. Eidemana Str.13-85 Kharkov Ukraine AE
Alena Finoshyna Str.krymskay 55/17 Fpo Dzhankoj Ukraine AL
Anna Boshko Grizodybova 37 Makeevka-ukraine SD
Antonina Stepanova Mrn 17 D 2 Kv 46 247210 Belarus Zhlobin AK
Alla Demakova Belorechenskaya 38/1 116 Russia 109469 Moscow TX
Ekaterina Egorova Argentovskogo 22/1 Kurgan Russia 640006 AL
Zinaida Komar Mitrofanova 13/16 Apartment 10 Kiev Region Ukraine 09108 Belaya Tserkov Kiev Region Ukraine AL
OLGA GERASIMCHUK OSTROVSKOGO 36/13 UKRAINE BUCHA KIEV REGION CA
AMY HASSINUS 891 STATE ROUTE 502 MOSCOW PA
HEATHER MOELLER 90 MAJESTIC LAKES BLVD MOSCOW MILLS MO
ELYSSA SPAETH 402 S BLAINE ST APT 201 MOSCOW ID

 

[jdraw]

Got your file, Brought it into Access 2010. Took a quick look. There's no data in Attention, BARCODE or ADDRESS3 so I dropped them from my Query. I brought the OrderDate field in as text since it screws up the import if you leave it as date/time. I did a CDate on it to use it in the query.

Here's a sample of the 162 records. I see what you mean but it will require logic to sort out these "misused fields" ---database wants atomic values. But there's always a logic work around. Do you really deal with all of the other fields (checkboxes). It seems to be some combination of csv, no quotes generally but not always???

In my view its a bit of a mess unless you have some mappings as to what the data means. Even though its in a variety of fields, I think it could be parsed into the proper meaningful fields and processed.
How come the Russian Cities are in the addresses but the St and City for those relate to USA?? These are the anomalies that need answers in order to program a transformation.

 

[skwilliams]

I actually don't use many of the fields such as the checkboxes, Barcode and Address3. These are used by our other companies. Strange that it works if I input the string to look for.

 

[skwilliams]

I could do it the way to just showed you, but it would mean going into the query to add a country instead of a user being able to input one into the tblReference table. Big difference in being user friendly.

 

[jdraw]

I just added some text and another jpg.

Can you explain, using the input data you sent, exactly WHAT you want to do?

Well parsing strings isn't just doing a check for equal.

 

[skwilliams]

This data comes daily from corporate. It is pulled into a table called "tblMain". I want to remove any records from these foreign countries before processing the data further. I need to look at it first before deleting it though because the customer could be from Moscow, Kentucky instead of Moscow, Russia.

This would be quicker way to look at just questionable data rather than all the records from that day as they can be very lengthy.

Does that make sense?

 

[jdraw]

Yes. I would write a routine to look for certain words in the Address1 and address2 fields (at least that's where I see Russia. I do not see any other foreign countries in the data you gave. However, if you have a list of such countries, I can help you create a routine to find these records.

This is the basic query, You could certainly add moe fields.

SELECT ABB.COMPANY, ABB.FNAME, ABB.LNAME, ABB.ADDRESS1
, ABB.ADDRESS2, ABB.CITY, ABB.[POSTAL CODE], ABB.STATE
, ABB.COUNTRY, ABB.[ORDER NUMBER], ABB.[SHIPPING METHOD]
, CDate([ORDER RECV DATE]) AS Orderdate
FROM ABB
Where 
Address1 Like "*Russia*"  Or
Address2  Like "*Russia*"

 

[Brianwarnock]

Have you tried your first post's SQL without the Join, it is the join that allows the one record and prevents the rest. It might be slow as I think it will do a cartesian join and then filter on the Where clause.

Of course I could be talking rubbish.

Brian

 

[jdraw]

I used your sample data abb.text and the list of ForeignCities/Countries form your post to build a small database.
The Foreign terms are in table PossibleForeignCity_Country
The incoming data from ABB.txt I put in a table called ABB

There is a Form with 1 button -- click the button to look for any of the ForeignText terms in the incoming data.

The vba code (subprocedure) is called CheckIfForeign

There is one query in the database called QueryToCheckABBForForeign. It is important to NOT DELETE this query. The SQL for this query gets changed based on the subprocedure.
The query will Open and will list the ABB records containing one of the Foreigntext terms and will identify the records by Order Number.

I hope this is useful to you.
Good luck with your project.