New query field based on field containing multiple information at the same time

[Dsnant]

Hello,

I am not an expert in Access and I am trying to simplify my task here instead of hard-coding a solution somehow.

Here is my problem:
- We do have multiple regions: Central Europe, Northern Europe, Southern Europe, North America, Central America, South America, East Asia... (21 in totals). Each region is made of multiple countries.
- We have promotions in these régions, but promotions can cover multiple regions at once. For example, a promotion can be in North America, but it could also be in all Americas at once. Therefore, the promotion would list North America, Central America and South America.
- I have a table with all countries corresponding to each region.
- My goal is to list all countries covered by the promotion.
- It is easy when a promotion is only in one region, but I am not sure how to proceed when promotion covers multiple regions.
- The format I get when a promotion covers multiple regions is "North America, Central America, South America". It is all in the same field, and this is why it is an issue.

Let me know if there is a solution, or if I should try to proceed differently.

Thanks in advance,

 

[Joe4]

The format I get when a promotion covers multiple regions is "North America, Central America, South America". It is all in the same field, and this is why it is an issue.

That is not good database design. You should never have multiple pieces of information in a single field.
You should have a separate record for each region that has a promotion, i.e.

[B]Region                Promotion[/B]
North America         Promotion1
Central America       Promotion1
South America         Promotion1
East Asia             Promotion1
...

You can then link this back to your country/region table to get all the countries that offer a particular promotion.

 

[Dsnant]

Thanks Joe4,
Unfortunately, I am stuck with that information the way it is. It comes from our sales organization, and I won't get it in any other way.
Also, I have few hundreds promotions to deal with.

 

[Joe4]

How is the data sent to you?
Is it in an Excel or text file?
If it was me, I would create a little Excel macro to "fix" the data so it is in proper format before importing it into Access.

 

[Dsnant]

The Data is sent to me in Excel.
Each promotions as a different list of geos. Most of them have 3 or 4, but some have all 21 regions when the promotion is global.

I need the list of countries as I'll need to match this with country information coming from a different system where regions are not set up, only countries.

How would you "fix" the data?
Would you create one field per region? I feel like creating one field per region would then require creating one field per country.
Would you fix it in Excel or in Access?

My original idea was that I could check the country associated to a sale and see if it was in the list of countries for a specific promotions.

 

[Joe4]

If all you really need to do is locate what promotions certain Regions offer, you could do so with a simple query based on your current structure.
Basically, you would just do a query and put the following criteria under the Region field (where you have your list of regions in one single field), like this (for North America):

Like "*North America*"

That would return all records that have "North America" anywhere in the Region field.

So if that is the only thing you need to do, you can probably get away without having to fix the data, and work with it "as-is".

But if you need to do anything else with it, you may want to consider fixing up the data before importing it (working with data that is not normalized in Access can be maddening!).
Basically, it would just be a little Excel macro that would convert the data from the format you are describing, to the format I am showing in my first reply.
If you want to do that, if you can explain the exact layout of your data files (what columns the data is in, what row it starts on, what else is in the file), I can come up with the Excel macro you would need.

 

[Dsnant]

Basically, if we have a promotion XYZ running on product ABC for "North America, Central America, South America", I want to identify a sale of product ABC done in Argentina as part of the promo XYZ.
In short, I want to match "Argentina" against "North America, Central America, South America" and return the Promo Name XYZ or even a Yes. Of course, it would need to work for all other countries in these regions.

This is why I think the first option you descibe might be appropriate. I think it would be matching a country with an entire list of countries.

Like(*Argentina*) = "... All Countries in North America, Central America, South America..."

The expression above should return the name of the promo or even Yes.

Now, my original problem is still here. How do I go from a list of regions to a list of countries?
I was trying to build a function, but was unsuccessful. It looked like my list was too long.

If it helps to visualize the issue, I have attached the lists of countries for 3 European régions.

• Central Europe = Austria; Germany; Liechtenstein; Switzerland
• Northern Europe = Armenia; Azerbaijan; Belarus; Denmark; Estonia; Faroe Islands; Finland; Georgia; Greenland; Guernsey; Iceland; Ireland; Isle of Man; Jersey; Kazakhstan; Kyrgyzstan; Latvia; Lithuania; Moldova, Republic Of; Norway; Russian Federation; Svalbard & Jan Mayen Islands; Sweden; Tajikistan; Turkmenistan; Ukraine; United Kingdom; Uzbekistan
• South/West Europe = Andorra; Belgium; France; Holy See (Vatican City State); Italy; Luxembourg; Monaco; Netherlands; Portugal; San Marino; Spain

I really realy appreciate the help.
Thanks,

 

[Joe4]

Now, my original problem is still here. How do I go from a list of regions to a list of countries?

You need to have a table which list each country individually, and which Region it belongs to, i.e.

[B]Country           Region[/B]
Argentina         South America
Austria           Central Europe
Germany           Central Europe
....

Then, you can link this table to your other tables on the Region field, so you can tie the individual countries in with the promotions.

 

[Dsnant]

I am going to try that.

Thanks a lot.

 

[Joe4]

You are welcome!