counting elements in a txt field by query

[Isi]

Hi EveryOne,

I need your help please;

I have a "TRAVEL" table and it has one of the column name "COUNTRIES".
Unfortunatelly many times the "COUNTRIES" field contains more then one countries separated them by comma like this - for example:

record_id countries
214541 Germany
224624 France, Germany, Russia
234157 France, Russia
265744 France
324571 Germany, Luxembourgh, France
etc.
etc.
...

I need a query, that count the countries name in the "COUNTRIES" field, such as - remaining example above:
countries pcs
Germany 3
France 4
Russia 2
Luxembourgh 1
etc.
etc.

Could you help me plaese ?
Thanks in advance.

Istvan

 

[plog]

Because of your structure, this is not something you can obtain with just a query. You are going to need to use VBA.

I suggest you use VBA to properly normalize your data, which means creating a new table that puts each country into its own field like so:

TRAVEL_COUNTRIES
record_id, country
214541, Germany
224624, France
224624, Germany
224624, Russia
etc.

That's how your data needs to be structured to get the data you want. Then the query portion because trivial:

SELECT country, COUNT(country) AS Total From TRAVEL_COUNTRIES;

Now, to get your data into the proper structure you must first build TRAVEL_COUNTRIES, then build a VBA function that loops through every record in TRAVEL and does an INSERT command to populate TRAVEL_COUNTRIES.

 

[theDBguy]

Hi. Welcome to AWF!

Do you have a separate table listing all countries?

 

[Isi]

Hello Plog,

I am going to try your suggest, thank you !

 

[Isi]

Hi. Welcome to AWF!

Do you have a separate table listing all countries?

Hello theDBguy!
Thanks your welcome !

I think there is such a table somewhere in the database, if not I will create it.

 

[theDBguy]

Hello theDBguy!
Thanks your welcome !

I think there is such a table somewhere in the database, if not I will create it.

I think it might be a good idea to create one, if you don't have it already. You can also use it with a normalized table structure.

 

[bastanu]

Agree with others that the best for you would be to normalize your db; for the issue at hand you could Import the attached module and use the SF_CountWords function to give you the country count in your current set up.

Cheers,
Vlad

 

[Isi]

I think it might be a good idea to create one, if you don't have it already. You can also use it with a normalized table structure.

OK, I'm going to do it, thanks!