Separating information in a table

[Lupson2011]

Hi

I am working with a database which someone else designed the tables.

There is a table which has a row which is entitled 'Types of help'. This basically lists all the types of help a group has recieved, with each separated by a comma (for example: developing your application, marketing, finance)

Each group has a list of a different types of help, all in this format.

What I want to do is to produce a query / report which shows the collated amounts of the different types of help (for example, the number of groups who recieved 'developing your application help' or 'marketing' help)

Now, this is easy when they are already separated, but I am finding it a little harder in findign the coding which can separate them out on the comma format). Hope this is making sense

So for example, if there are two groups with the following help

Group 1: Developing Your Application, Marketing, Finance, Publicity
Group 2: Marketing, Finance, Publicity, Evaluation

What i want final query/report to show if everything works is the following

Developing Your Appication 1
Marketing 2
Finance 2
Publicity 2
Evaluation 1


Any help with the useful bit of code to do this?

Thanks



marc

[June7]

Is the data saved as multi-value or is it a single text string?

Review: http://office.microsoft.com/en-us/ac...001233722.aspx

[Lupson2011]

hi again

I have attached a screenshot which I think should hopefully answer your question?

Its the AdviceGiven column

Marc

[orange]

I don't have Acc2010, but have mocked up your request in Acc2003.
I used your fields Project and AdviceGiven to create a table called MarcAdviceGiven

Below is the code to separate the AdviceGiven field into individual comma separated strings, and
to write these individual strings into a new table TempTableForQuery

'---------------------------------------------------------------------------------------
' Procedure : SplitAdviceGiven
' Author    : Jack
' Date      : 10/05/2012
' Purpose   : Sample code to parse a column into discrete strings separated at comma.
'This uses the Split function and creates a Temporary Table to be used for queries.
'see https://www.accessforums.net/showthread.php/24677-Separating-information-in-a-table
'
'The incoming records (created in table "MarcAdviceGiven") have field layout
'  id  auto
'  project  string
'  advicegiven  string (multi strings separated by comma)
'
'The output table ("TempTableForQuery") has layout
'  id   auto
'  project string
'  advicestring  (individual strings extracted from incoming advicegiven)
'
'---------------------------------------------------------------------------------------
'
Sub SplitAdviceGiven()
Dim rs As DAO.Recordset  'incoming records
Dim rsTemp As DAO.Recordset 'temp table for use with query
Dim db As DAO.Database
Dim arrHold() As String
Dim i As Integer
   On Error GoTo SplitAdviceGiven_Error

Set db = CurrentDb
Set rs = db.OpenRecordset("MarcAdviceGiven")
Set rsTemp = db.OpenRecordset("TempTableForQuery")
Do While Not rs.EOF
arrHold() = Split(rs!adviceGiven, ",")
For i = LBound(arrHold) To UBound(arrHold)
 'Print the project and the individual advice string to immediate window
 Debug.Print rs!Project & " - " & Trim(arrHold(i))
 'Output project and adviceString to the tempTableforQuery
    rsTemp.AddNew
    rsTemp!Project = rs!Project
    rsTemp!AdviceString = Trim(arrHold(i))
    rsTemp.Update
 Next i
rs.MoveNext
Loop

rs.Close
rsTemp.Close

   On Error GoTo 0
   Exit Sub

SplitAdviceGiven_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure SplitAdviceGiven of Module AWF_Related"
End Sub

Note I used only a few records from your sample to create test data.

Here is my test data:

Project,AdviceGiven
"MLF000056","Developing your application, Writing business plans,Contingency Planning"
"SLF000112","Developing your application, Marketing and Publicity, Community Involvement"
"MLF000108","Developing your application, Human Resource Management, Community Involvement"
"MLF000134","Writing business plans, Monitoring and Evaluation, Governance Issues"

Once the Temporary table is created you can run a query based on the following:

SELECT TempTableForQuery.AdviceString, Count(TempTableForQuery.AdviceString) AS CountOfAdviceString
FROM TempTableForQuery
GROUP BY TempTableForQuery.AdviceString;

The contents of the TempTableForQuery is shown in AdviceGivenOutput.jpg.


The output of the query is shown in AdviceGivenQueryResults.jpg.

You will have to adjust names as per your Tables and needs.

The purpose was to show a sample of how to separate the strings and create a query to do counts.

[June7]

I think multi-values would have a space after commas, so inclined to think field holds single string and orange's suggested code should work.

[Lupson2011]

Sorry for being a bit thick, perhaps a Fridfay afternoon moment, but where would that code sit behind?

Thanks for the answer, much appreciated

Marc

[orange]

It could sit behind a button on a form.
How familiar are you with forms and /or vba.

Where are you that it is Friday PM?

This is written as a procedure.
It takes only the Project and AdviceGiven fields from a recordset.
For each project,
it splits the AdviceGiven field contents into separate strings separated at the commas
it writes a record for each project * each extracted string to a TempTable
(I added a PK (ID field) to the TempTable for testing- it has no bearing on the approach).

After the TempTable has been created (after all AdviceGiven data has been parsed),
a Select query is run against that temp table to show Each distinct individual extracted string and
a count of the number of times the string occurs in your original data [the data with the original Project and AdviceGiven].

Post back if you need more.

[Lupson2011]

It could sit behind a button on a form.
How familiar are you with forms and /or vba.

Where are you that it is Friday PM?

This is written as a procedure.
It takes only the Project and AdviceGiven fields from a recordset.
For each project,
it splits the AdviceGiven field contents into separate strings separated at the commas
it writes a record for each project * each extracted string to a TempTable
(I added a PK (ID field) to the TempTable for testing- it has no bearing on the approach).

After the TempTable has been created (after all AdviceGiven data has been parsed),
a Select query is run against that temp table to show Each distinct individual extracted string and
a count of the number of times the string occurs in your original data [the data with the original Project and AdviceGiven].

Post back if you need more.

Hi

Sorry for delay in posting back. I do know forms and some VBA, but still learning that side. I created the MarcAdviceGiven table with appropritate field and data. I created a simple form and place the code behind a button, however when I click it to run the code I get a compile error: Expected End sub.. is creating a blank with button the best way to sun the code to create the Temp Table?

Cheers

Marc

[orange]

Hi Marc,

This was written as a stand alone procedure. The main point was to show that you could read the AdviceGiven field, and by using the Split function, get the individual phrases into a table. Once the strings were isolated, a query would get your Counts of how often each phrase occurred .

What do you need at this point?
Do you follow the vba in the procedure?

[Lupson2011]

Well, I have set up the outgoing table of MarcAdviceGiven, so that's all ready. I just need to know how to run the code, where to put it. Once I have that, then it should be all fine?

My limited knowledge, vba coding normal goes behind forms/buttons etc. Is this different as its a stand alone procedure. Does it get treate differently?

Sorry if being a bit VBA dim..I am actually due to go on a 2 day course next week in VBA..

Thanks

Marc

[orange]

I looked at the sample you posted. Then, because I didn't have your original table, I created the table MarcAdviceGiven (an input table) and typed in some of the records from your post. So the table MarcAdviceGiven, is my mock up of your table. Since we are just reading the AdviceGiven field, you could use your original table for input. I don't know what that table was called. The TempTable was the output table.

[Lupson2011]

Well, I have set up the outgoing table of MarcAdviceGiven, so that's all ready. I just need to know how to run the code, where to put it. Once I have that, then it should be all fine?

My limited knowledge, vba coding normal goes behind forms/buttons etc. Is this different as its a stand alone procedure. Does it get treate differently?

Sorry if being a bit VBA dim..I am actually due to go on a 2 day course next week in VBA..

Thanks

Marc

[Lupson2011]

Hi i understand that, but where do I run the code from. The procedure you wrote for me, which is great, where is it run from. Or where does it sit? if that makes sense?

[orange]

I have set up the outgoing table of MarcAdviceGiven, so that's all ready

No, this was my "mockup of your original table" - it is not an output. You could just use the original table for input. That would be my advice to you at this point. You have the table with all of the Projects and AdviceGiven data.

Let's create a form with a button, and we have the Click of the button run the procedure. But we have to set up the procedure to use your original table with all of the data first.

[Lupson2011]

Hi

ok, set up the procedure by changing name to match original table name. Created a form and button, clicked the button and got this. Any help?