I have an issue I need to resolve, and have developed an extremely messy and time consuming way of implementing it. I am looking to streamline the process to enable faster and more frequent updates, and was hoping someone might be able to help me.

I have a long list of "areas" (ie Area 1, Area 2, 3, 4, etc all the way to 9000). Each area has a certain number of languages assigned to it, anywhere from 1 - 7. Each area can be listed a random number of times, ie Area 1 can be listed 20 times (so the total list has over 30,000 records). Each time an area is listed, it can have different languages attached to it. Example:

(each semi-colon indicates a new column)

Area 1 ; English ; French ; Italian ; German ; Farsi
Area 1 ; English
Area 1 ; French ; Hindi
Area 1 ; French ; Italian ; Tagalog
Area 2 ; English
Area 2 ; American Sign Language
Area 3 ; French

What I am trying to do, is have a final list of all areas listed just ONCE, and any languages that appear 2 or more times for each area. Example, if English appears twice or more for Area 1, it would be listed, but if Italian only appears once, it won't be:

Area 1 ; English ; French
Area 2 ; Hindi ;
Area 3 ; French

I can use excel/access/vba anything. I am eager to hear your ideas! Let me know if you need more clarification as this was hard to explain.

Thank you!!

So from your explanation, from this list,

Area 1 ; English ; French ; Italian ; German ; Farsi
Area 1 ; English
Area 1 ; French ; Hindi
Area 1 ; French ; Italian ; Tagalog
Area 2 ; English
Area 2 ; American Sign Language
Area 3 ; French

You would actually yield:

Area 1 ; English ; French ; Italian
Area 2 ;
Area 3 ;

Is this correct? So what does this mean: "each semi-colon indicates a new column?"

Does that mean your list that looks like this:

Area 1 ; English ; French ; Italian ; German ; Farsi
Area 1 ; English
Area 1 ; French ; Hindi
Area 1 ; French ; Italian ; Tagalog
Area 2 ; English
Area 2 ; American Sign Language
Area 3 ; French

actually looks like this:

Area 1 ; English
Area 1 ; French
Area 1 ; Italian
Area 1 ; German
Area 1 ; Farsi
Area 1 ; English
Area 1 ; French
Area 1 ; Hindi
Area 1 ; French
Area 1 ; Italian
Area 1 ; Tagalog
Area 2 ; English
Area 2 ; American Sign Language
Area 3 ; French

??? If that is the case, then just do this (I used a table called tblArea_Language):

SELECT tblArea_Language.Area, tblArea_Language.Langauge, Count(tblArea_Language.Langauge) AS CountOfLangauge
FROM tblArea_Language
GROUP BY tblArea_Language.Area, tblArea_Language.Langauge
HAVING (((Count(tblArea_Language.Langauge))>1));

So from my dataset:

Area Langauge
Area 1 English
Area 1 English
Area 1 French
Area 1 Italian
Area 1 Italian
Area 2 Farsi
Area 2 Farsi
Area 2 ASL
Area 3 Hindi

The query yields this data:

Area Langauge CountOfLangauge
Area 1 English 2
Area 1 Italian 2
Area 2 Farsi 2

Is this what you want?



mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for your fast response! Sorry it was really difficult to explain, so I used semicolons to show the reader where a new column began in my access table. I didn't use the best example. Let me try to explain again, and then I will see if what you suggested works. I will also try the link you suggested.

My table looks like this, where a ';' indicated the starting of a new column for simplicity sake:

Area 1 ; English ; French ; Tamil ; Farsi
Area 1 ; French ; English ; Sign Language ; Italian
Area 1 ; English ; French ; German ; Italian
Area 2 ; English ; French
Area 2 ; English ; German
Area 3 ; English ; Farsi
Area 3 ; French

So, because Area 1 has English listed 3 times, I want to include it as a language for Area 1. However Tamil is only listed once for Area 1, so I do not want to include it. So for area 1, the final output would be:

Area 1 ; English ; French ; Italian

because all of those languages appear more than once anywhere under area 1 in the original table. Same would hold true for areas 2 - 3, listing just English for Area 2, and nothing for area 3, because no language appears twice or more.

Hope that helped clear things up. I will see what else I can dig up in that link and your response! Thanks again.

Can you send the lay out of your table? Does it look like this:

Area Language Language Language
Area 1 English French Italian
Area 1 English Farsi ASL
Area 2 French Spanish
Area 3 Tagolog Japanese

???


mmcdonal

Look it up at: http://wrox.books24x7.com

Yes it looks exactly like that. Unfortunately due to confidentiality reasons I cannot send it to you. Please let me know if you need more clarification.

The final result would look like this, listing only languages that appeared twice or more in the original list:

Area Lang1 Lang2 Lang3, etc
Area 1 English French Italian
Area 2 English French
Area 3 English
Area 4 French Japanese

You want the result in the same format? You wouldn't want

Area Language
Area 1 English
Area 1 French
Area 2 Farsi
Area 2 ASL
Area 3 ...?

Where do you want to keep the data? Do you want it in a table that is updated, or do you just need it in memory to run a report on, etc?

Looks like some lovely nested loops here...

I am leaving for the day but will try to check in over the weekend.


mmcdonal

Look it up at: http://wrox.books24x7.com

Well let me try to explain more specifically. Each "area" contains a number of people that work there. An area shows up once for each employee that works in that area, for example Area 1 may show up 3 times, meaning there are 3 employees that work there. I want to be able to list "common" languages in each area, so if a language is shown more than once, i am considering it "common" enough to include in the final result. For example:

Area 1 Eng Fr German
Area 1 Eng Fr Italian
Area 1 Fr German
Area 2 Eng

In the above example, Area 1 has 3 employees because it is listed 3 times, Area 2 has just one employee. Eng, Fr and German are commonly spoken in area 1, thus the final table would be consolodated to show:

Area 1 Eng Fr German
Area 2 <blank>

Listing each area just once. I would like the data to be kept in a new table in access, or even output to excel, doesn't matter to me. I have a list of all "areas" and i need to attach this list of languages to each area in that master list, so it doesn't matter the final format, and i do not need it to remain in memory.

Thanks so much for your help.

So you want to list each area once in a common table, and then have a memo field that would look like this:

"Area 1", "English ; French ; German"

Or do you want n number of fields like:

"Area 1", "English", "French", "German"?

There wouldn't be any purpose to having more than one language column.

We can do the first one pretty easily.

To get started, create your master Area_Language table. Make the first column a text field. I guess you want to store the whole text string "Area 1" instead of calling the field Area, and then listing 1, 2, 3, etc? Then create your Language field as a memo field, so you can have n number of languages and not be limited by 255 characters. No primary key is needed on this table, but you can make the Area field the PK with no dupes.

Then create a Delete query to empty the table when you run the function.

Then create an Append query to append unique Area names. Take your current table and do a SELECT DISTINCT on the Area column, then make the query an Append query, and have it append the results to the Area_Language table. When you run that query you will get all the areas pushed to the new table, but just one of each.

Once you have that, we can create the code to compile the list. To be clear, in your current table, you have an Area column followed by how many language columns? Also, how many languages do you have?


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi mmcdonal,

I have created the delete and append queries to create a table that currently has distinct areas, and one language column.

I have about 106 total languages, but the end result will probably have about 40 languages that are spoken 2 or more times. Having all languages that are spoken more than once in ONE column is fine, as opposed to having each language in a separate column.

In my current table, I have one area column, followed by 8 langauge columns (meaning one person that works in an area can speak up to 8 different languages, but I think this only happens once in one area, so we can ignore the 8th column and just say that there are 7 language columns).

Thanks.

My assumption is that when languages are entered, they are done in randmon order, like this:

Area 1 English French
Area 1 French Italian
Area 1 Italian English

etc, so there is no point creating a query to COUNT() the number of times a language appears in its column. A better solution, using one table, would have been check boxes.

Area French English Italian etc
Area 1 Yes No Yes ...

So here is the psuedo code I would use:

Turn off warnings
Run the Delete Query
Run the Append Query
Turn on warnings

Create a recordset (rs)
Open the Area_Language Table
   Take the a value in the Area_Language.Area column
      Create a second recordset (rs2)
         Use the Area value to open a recordset on your main table WHERE Area = Area...
           Using a Select Case statement, continue to add to count integers for each language
             iEnglish = 0
             iFrench = 0
             etc
             Take Col1, Col2, Col3, Col4. etc languages
             Check each one and increment language counter
              Select Case Col1
                 Case "English"
                      iEnglish = iEnglish + 1
                 Case "French"
                      iFrench = iFrench + 1
                 ...
              End Select
              Build String of languages
              sLangString = ""
              If iEnglish > 1 Then
                 sLangString = "English; "
              End If
              If iFrench > 1 Then
                 If sLangString <> "" Then
                     sLangString = sLangString & "French; "
                 End If
              Else
                 sLangString = "French; "
              End If
              ...

              Update Recordset
              rs("Languages") = sLangString
              rs.Update
              rs2.Close
           rs.MoveNext
Loop
rs.Close

This will take a few moments to run but will be accurate. There is a lot of repetitive coding here because of the flat table design.

Will this work for you? Does anyone have a better (more elegant) suggestion?

mmcdonal

Look it up at: http://wrox.books24x7.com