Create table from text strings within field

[mralmackay]

Hi,
Around 10 months ago I was using Excel to find out how many resolvers
had been involved in incidents based on extracts from our system. As
a result of better reporting functionality this has now been
transferred to access so the queries can be created dynamically.

What I need to know is the following:
1) Within field 'Diary' (in table P2P-Request) text "Updated By: [X
PERSON'S NAME]" can appear multiple times. For each occurence I need
to be able to pull out the next two full words (Which is the user's
name, system only uses Forename and Surname) along with the Case-ID
field within the same table.
2) For example based on the data extract for one case that I've
provided below which is Case ID 12002, I'd like this to show:

CaseID Resolver Name Resolver Occurences
12002 Al Mackay 3
12002 Gill Donaldson 1
12002 Malcolm Fox 1
12002 Tom Green 1

I'd like this to be written to a new table, e.g. Resolvers which I can
then produce simple query to show by case how many unique people were
involved in the case (for example even though I've updated this 3
times I'd only report this out as being one). What this will then
allow me to see is for this one case it's taken input from 4 different
people in order to resolve it.

Apologies for the long extract below, just hoping if you can see the
detail that this will help to work with how this could be done.

Thanks in advance for your help, really appreciated. Al.

Data in Diary Field (P2P-Request table):
26/03/2008 16:17:56 ktnr619
Case Assigned to System Team Email Text: This case has been assigned
to you, please view the details below:

The Assigned Individual has been changed to System Team

Updated By: Al Mackay

26/03/2008 14:52:57 m215039
Case Assigned to Gill Donaldson Email Text: This case has been
assigned to you, please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Gill Donaldson

26/03/2008 14:45:19 mp8710q
Case Assigned to A/P Email Text: This case has been assigned to you,
please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Malcolm Fox

18/02/2008 12:47:18 khsm541
The Categorisation of the call has been changed to
Invoices
Invoice Query
Invoice Paid/Clear for Payment


The SLA Priority has changed to 2

Case Logged. The Status is Open

Updated By: Tom Green

13/03/2008 15:50:27 ktnr619
The Case has been Re-Opened.

Updated By: Al Mackay

13/03/2008 14:43:40 m505168
The Case has been Closed.
Resolution Code: Resolution-001

Updated By: Al Mackay

___________________
Excel Solution Thread:
http://groups.google.co.uk/group/mi...c549cd835e4a?hl=en&lnk=st&q=#03cfc549cd835e4a

 

[John Spencer]

I would probably write some vba to do this. Also, I might create one
record for each incidence.

Assumption: CaseID is a number field. If it is a text field you will
need to add CHR(34) in front of it and behind it in the query string.

This routine will create one record for each instance of a name in case id.

Public Sub sCreateRecords(sTextIn, CaseID)
Dim strSQL as String
Dim aText as Variant
Dim i as long
Dim strName as String
Dim iCount as Long
Dim dbAny as Database

Set DbAny = CurrentDb()
aText = Split(sTextIn,"Updated By:")

For i = Lbound(aText) to UBound(aText)
strName = Chr(34) & _
Trim(Left(aText(i), Instr(1,aText(i),VbLf)-1)) & Chr(34)

strSQL = "INSERT INTO [TheTable] (CaseID, [Resolver Name]" & _
"Values(" & CaseID & ", " & strName & ")"

dbAny.Execute strSQL
Next i

END Sub

The above is untested air code with no error trapping or checking for
valid values. You will need to pass in the text and the case id.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Around 10 months ago I was using Excel to find out how many resolvers
had been involved in incidents based on extracts from our system. As
a result of better reporting functionality this has now been
transferred to access so the queries can be created dynamically.

What I need to know is the following:
1) Within field 'Diary' (in table P2P-Request) text "Updated By: [X
PERSON'S NAME]" can appear multiple times. For each occurence I need
to be able to pull out the next two full words (Which is the user's
name, system only uses Forename and Surname) along with the Case-ID
field within the same table.
2) For example based on the data extract for one case that I've
provided below which is Case ID 12002, I'd like this to show:

CaseID Resolver Name Resolver Occurences
12002 Al Mackay 3
12002 Gill Donaldson 1
12002 Malcolm Fox 1
12002 Tom Green 1

I'd like this to be written to a new table, e.g. Resolvers which I can
then produce simple query to show by case how many unique people were
involved in the case (for example even though I've updated this 3
times I'd only report this out as being one). What this will then
allow me to see is for this one case it's taken input from 4 different
people in order to resolve it.

Apologies for the long extract below, just hoping if you can see the
detail that this will help to work with how this could be done.

Thanks in advance for your help, really appreciated. Al.

Data in Diary Field (P2P-Request table):
26/03/2008 16:17:56 ktnr619
Case Assigned to System Team Email Text: This case has been assigned
to you, please view the details below:

The Assigned Individual has been changed to System Team

Updated By: Al Mackay

26/03/2008 14:52:57 m215039
Case Assigned to Gill Donaldson Email Text: This case has been
assigned to you, please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Gill Donaldson

26/03/2008 14:45:19 mp8710q
Case Assigned to A/P Email Text: This case has been assigned to you,
please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Malcolm Fox

18/02/2008 12:47:18 khsm541
The Categorisation of the call has been changed to
Invoices
Invoice Query
Invoice Paid/Clear for Payment


The SLA Priority has changed to 2

Case Logged. The Status is Open

Updated By: Tom Green

13/03/2008 15:50:27 ktnr619
The Case has been Re-Opened.

Updated By: Al Mackay

13/03/2008 14:43:40 m505168
The Case has been Closed.
Resolution Code: Resolution-001

Updated By: Al Mackay

___________________
Excel Solution Thread:
http://groups.google.co.uk/group/mi...c549cd835e4a?hl=en&lnk=st&q=#03cfc549cd835e4a

 

[mralmackay]

Hi John,

Thanks for looking @ this so quickly. Couple of questions on this.
1) How do I run this? Am I pasting this into a standard module
within Access or do I need to anything in particular for this? When
pasted into a module it's not available to run as a macro for example
2) The [TheTable] part I'm assuming is the table which this 'tidied'
up data will be sent into. Does this need to be in place up-front or
will this actually do the create table part?
3) The field that the data is contained in is 'Diary', in the example
is this correct? If not, as I couldn't see the Diary field, is this
just at the top that I would amend this in the (sTextIn....) part?

Thanks again for your help and advice on the above.

Al.

I would probably write some vba to do this. Also, I might create one
record for each incidence.

Assumption: CaseID is a number field.  If it is a text field you will
need to add CHR(34) in front of it and behind it in the query string.

This routine will create one record for each instance of a name in case id..

Public Sub sCreateRecords(sTextIn, CaseID)
Dim strSQL as String
Dim aText as Variant
Dim i as long
Dim strName as String
Dim iCount as Long
Dim dbAny as Database

    Set DbAny = CurrentDb()
    aText = Split(sTextIn,"Updated By:")

    For i = Lbound(aText) to UBound(aText)
      strName = Chr(34) & _
          Trim(Left(aText(i), Instr(1,aText(i),VbLf)-1)) & Chr(34)

      strSQL = "INSERT INTO [TheTable] (CaseID, [Resolver Name]" &_
         "Values(" & CaseID & ", " & strName & ")"

      dbAny.Execute strSQL
    Next i

END Sub

The above is untested air code with no error trapping or checking for
valid values.  You will need to pass in the text and the case id.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================

Hi,
Around 10 months ago I was using Excel to find out how many resolvers
had been involved in incidents based on extracts from our system.  As
a result of better reporting functionality this has now been
transferred to access so the queries can be created dynamically.

What I need to know is the following:
1)  Within field 'Diary' (in table P2P-Request) text "Updated By:  [X
PERSON'S NAME]" can appear multiple times.  For each occurence I need
to be able to pull out the next two full words (Which is the user's
name, system only uses Forename and Surname) along with the Case-ID
field within the same table.
2)  For example based on the data extract for one case that I've
provided below which is Case ID 12002, I'd like this to show:

CaseID   Resolver Name      Resolver Occurences
12002    Al Mackay             3
12002    Gill Donaldson       1
12002    Malcolm Fox         1
12002    Tom Green            1

I'd like this to be written to a new table, e.g. Resolvers which I can
then produce simple query to show by case how many unique people were
involved in the case (for example even though I've updated this 3
times I'd only report this out as being one).   What this will then
allow me to see is for this one case it's taken input from 4 different
people in order to resolve it.

Apologies for the long extract below, just hoping if you can see the
detail that this will help to work with how this could be done.

Thanks in advance for your help, really appreciated.  Al.

Data in Diary Field (P2P-Request table):
26/03/2008 16:17:56 ktnr619
Case Assigned to System Team Email Text: This case has been assigned
to you, please view the details below:

The Assigned Individual has been changed to System Team

Updated By:  Al Mackay

26/03/2008 14:52:57 m215039
Case Assigned to Gill Donaldson Email Text: This case has been
assigned to you, please view the details below:

The Assigned Individual has been changed to A/P

Updated By:  Gill Donaldson

26/03/2008 14:45:19 mp8710q
Case Assigned to A/P Email Text: This case has been assigned to you,
please view the details below:

The Assigned Individual has been changed to A/P

Updated By:  Malcolm Fox

18/02/2008 12:47:18 khsm541
The Categorisation of the call has been changed to
Invoices
Invoice Query
Invoice Paid/Clear for Payment

The SLA Priority has changed to 2

Case Logged.  The Status is Open

Updated By:  Tom Green

13/03/2008 15:50:27 ktnr619
The Case has been Re-Opened.

Updated By:  Al Mackay

13/03/2008 14:43:40 m505168
The Case has been Closed.
Resolution Code: Resolution-001

Updated By:  Al Mackay

___________________
Excel Solution Thread:
http://groups.google.co.uk/group/microsoft.public.excel.programming/b...-Hide quoted text -

- Show quoted text -

 

[John Spencer]

1) Paste the code into a standard VBA module and save the module. The
MODULE cannot have the same name as the function.
2) Yes, you will need to have a table already created to get the result.
3) You would call the function with something like
sCreateRecords [Diary],[CaseID]

You might do this from a query or from other vba code.

For instance, if you were doing this from a query
Field: Expr1: sCreateRecords([Diary],[CaseId])

You would obviously need a way to restrict the records you run this against
or you would continually build records in the destination table.
Perhaps something like the following.

SELECT sCreateRecords([Diary],[CaseId])
FROM [P2P-Request}
WHERE CaseID Not In (SELECT CaseID FROM [TheNewTable])


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for looking @ this so quickly. Couple of questions on this.
1) How do I run this? Am I pasting this into a standard module
within Access or do I need to anything in particular for this? When
pasted into a module it's not available to run as a macro for example
2) The [TheTable] part I'm assuming is the table which this 'tidied'
up data will be sent into. Does this need to be in place up-front or
will this actually do the create table part?
3) The field that the data is contained in is 'Diary', in the example
is this correct? If not, as I couldn't see the Diary field, is this
just at the top that I would amend this in the (sTextIn....) part?

Thanks again for your help and advice on the above.

Al.

I would probably write some vba to do this. Also, I might create one
record for each incidence.

Assumption: CaseID is a number field. If it is a text field you will
need to add CHR(34) in front of it and behind it in the query string.

This routine will create one record for each instance of a name in case
id.

Public Sub sCreateRecords(sTextIn, CaseID)
Dim strSQL as String
Dim aText as Variant
Dim i as long
Dim strName as String
Dim iCount as Long
Dim dbAny as Database

Set DbAny = CurrentDb()
aText = Split(sTextIn,"Updated By:")

For i = Lbound(aText) to UBound(aText)
strName = Chr(34) & _
Trim(Left(aText(i), Instr(1,aText(i),VbLf)-1)) & Chr(34)

strSQL = "INSERT INTO [TheTable] (CaseID, [Resolver Name]" & _
"Values(" & CaseID & ", " & strName & ")"

dbAny.Execute strSQL
Next i

END Sub

The above is untested air code with no error trapping or checking for
valid values. You will need to pass in the text and the case id.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Around 10 months ago I was using Excel to find out how many resolvers
had been involved in incidents based on extracts from our system. As
a result of better reporting functionality this has now been
transferred to access so the queries can be created dynamically.

What I need to know is the following:
1) Within field 'Diary' (in table P2P-Request) text "Updated By: [X
PERSON'S NAME]" can appear multiple times. For each occurence I need
to be able to pull out the next two full words (Which is the user's
name, system only uses Forename and Surname) along with the Case-ID
field within the same table.
2) For example based on the data extract for one case that I've
provided below which is Case ID 12002, I'd like this to show:

CaseID Resolver Name Resolver Occurences
12002 Al Mackay 3
12002 Gill Donaldson 1
12002 Malcolm Fox 1
12002 Tom Green 1

I'd like this to be written to a new table, e.g. Resolvers which I can
then produce simple query to show by case how many unique people were
involved in the case (for example even though I've updated this 3
times I'd only report this out as being one). What this will then
allow me to see is for this one case it's taken input from 4 different
people in order to resolve it.

Apologies for the long extract below, just hoping if you can see the
detail that this will help to work with how this could be done.

Thanks in advance for your help, really appreciated. Al.

Data in Diary Field (P2P-Request table):
26/03/2008 16:17:56 ktnr619
Case Assigned to System Team Email Text: This case has been assigned
to you, please view the details below:

The Assigned Individual has been changed to System Team

Updated By: Al Mackay

26/03/2008 14:52:57 m215039
Case Assigned to Gill Donaldson Email Text: This case has been
assigned to you, please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Gill Donaldson

26/03/2008 14:45:19 mp8710q
Case Assigned to A/P Email Text: This case has been assigned to you,
please view the details below:

The Assigned Individual has been changed to A/P

Updated By: Malcolm Fox

18/02/2008 12:47:18 khsm541
The Categorisation of the call has been changed to
Invoices
Invoice Query
Invoice Paid/Clear for Payment

The SLA Priority has changed to 2

Case Logged. The Status is Open

Updated By: Tom Green

13/03/2008 15:50:27 ktnr619
The Case has been Re-Opened.

Updated By: Al Mackay

13/03/2008 14:43:40 m505168
The Case has been Closed.
Resolution Code: Resolution-001

Updated By: Al Mackay

___________________
Excel Solution Thread:
http://groups.google.co.uk/group/microsoft.public.excel.programming/b...-
Hide quoted text -

- Show quoted text -

 

[mralmackay]

Hi John,

Apologies, tried this again but still seem to be struggling (with a
little tweaking). When I run the query I get Undefined function
'sCreateRecords' in expression.

I've included modified version below as this included my tweaking when
this originally didn't work. Appreciate it if you can review and note
any mistakes that I've done.

Thanks, Al.

TABLE - Resolved:
- Case-ID (Number Field)
- Resolver Name (Text Field)

QUERY:
SELECT sCreateRecords([Diary],[Case-Id])
FROM [P2P-Request]
WHERE Case-ID Not In (SELECT Case-ID FROM [Resolved]);

MODULE1:
Public Sub sCreateRecords(sTextIn, CaseID)
Dim strSQL As String
Dim aText As Variant
Dim i As Long
Dim strName As String
Dim iCount As Long
Dim dbAny As Database


Set dbAny = CurrentDb()
aText = Split(sTextIn, "Updated By: ")


For i = LBound(aText) To UBound(aText)
strName = Chr(34) & _
Trim(Left(aText(i), InStr(1, aText(i), vbLf) - 1)) & Chr(34)

strSQL = "INSERT INTO [Resolved].[Case-ID], [Resolved].[Resolver
Name]" & _
"Values(" & [Resolved].[Case-ID] & ", " & strName & ")"

dbAny.Execute strSQL
Next i

End Sub

1) Paste the code into a standard VBA module and save the module.  The
MODULE cannot have the same name as the function.
2) Yes, you will need to have a table already created to get the result.
3) You would call the function with something like
     sCreateRecords [Diary],[CaseID]

You might do this from a query or from other vba code.

For instance, if you were doing this from a query
   Field: Expr1: sCreateRecords([Diary],[CaseId])

You would obviously need a way to restrict the records you run this against
or you would continually build records in the destination table.
Perhaps something like the following.

SELECT sCreateRecords([Diary],[CaseId])
FROM [P2P-Request}
WHERE CaseID Not In (SELECT CaseID FROM [TheNewTable])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Hi John,

Thanks for looking @ this so quickly.  Couple of questions on this.
1)  How do I run this?  Am I pasting this into a standard module
within Access or do I need to anything in particular for this?  When
pasted into a module it's not available to run as a macro for example
2)  The [TheTable] part I'm assuming is the table which this 'tidied'
up data will be sent into.  Does this need to be in place up-front or
will this actually do the create table part?
3)  The field that the data is contained in is 'Diary', in the example
is this correct?  If not, as I couldn't see the Diary field, is this
just at the top that I would amend this in the (sTextIn....) part?

Thanks again for your help and advice on the above.

Al.

I would probably write some vba to do this. Also, I might create one
record for each incidence.

Assumption: CaseID is a number field. If it is a text field you will
need to add CHR(34) in front of it and behind it in the query string.

This routine will create one record for each instance of a name in case
id.

Public Sub sCreateRecords(sTextIn, CaseID)
Dim strSQL as String
Dim aText as Variant
Dim i as long
Dim strName as String
Dim iCount as Long
Dim dbAny as Database

Set DbAny = CurrentDb()
aText = Split(sTextIn,"Updated By:")

For i = Lbound(aText) to UBound(aText)
strName = Chr(34) & _
Trim(Left(aText(i), Instr(1,aText(i),VbLf)-1)) & Chr(34)

strSQL = "INSERT INTO [TheTable] (CaseID, [Resolver Name]" & _
"Values(" & CaseID & ", " & strName & ")"

dbAny.Execute strSQL
Next i

END Sub

The above is untested air code with no error trapping or checking for
valid values. You will need to pass in the text and the case id.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Around 10 months ago I was using Excel to find out how many resolvers
had been involved in incidents based on extracts from our system. As
a result of better reporting functionality this has now been
transferred to access so the queries can be created dynamically.
What I need to know is the following:
1) Within field 'Diary' (in table P2P-Request) text "Updated By: [X
PERSON'S NAME]" can appear multiple times. For each occurence I need
to be able to pull out the next two full words (Which is the user's
name, system only uses Forename and Surname) along with the Case-ID
field within the same table.
2) For example based on the data extract for one case that I've
provided below which is Case ID 12002, I'd like this to show:
CaseID Resolver Name Resolver Occurences
12002 Al Mackay 3
12002 Gill Donaldson 1
12002 Malcolm Fox 1
12002 Tom Green 1
I'd like this to be written to a new table, e.g. Resolvers which I can
then produce simple query to show by case how many unique people were
involved in the case (for example even though I've updated this 3
times I'd only report this out as being one). What this will then
allow me to see is for this one case it's taken input from 4 different
people in order to resolve it.
Apologies for the long extract below, just hoping if you can see the
detail that this will help to work with how this could be done.
Thanks in advance for your help, really appreciated. Al.
Data in Diary Field (P2P-Request table):
26/03/2008 16:17:56 ktnr619
Case Assigned to System Team Email Text: This case has been assigned
to you, please view the details below:
The Assigned Individual has been changed to System Team
Updated By: Al Mackay
26/03/2008 14:52:57 m215039
Case Assigned to Gill Donaldson Email Text: This case has been
assigned to you, please view the details below:
The Assigned Individual has been changed to A/P
Updated By: Gill Donaldson
26/03/2008 14:45:19 mp8710q
Case Assigned to A/P Email Text: This case has been assigned to you,
please view the details below:
The Assigned Individual has been changed to A/P
Updated By: Malcolm Fox
18/02/2008 12:47:18 khsm541
The Categorisation of the call has been changed to
Invoices
Invoice Query
Invoice Paid/Clear for Payment
The SLA Priority has changed to 2
Case Logged. The Status is Open
Updated By: Tom Green
13/03/2008 15:50:27 ktnr619
The Case has been Re-Opened.
Updated By: Al Mackay
13/03/2008 14:43:40 m505168
The Case has been Closed.
Resolution Code: Resolution-001
Updated By: Al Mackay
___________________
Excel Solution Thread:
http://groups.google.co.uk/group/microsoft.public.excel.programming/b....
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Whoops! You cannot use a sub in a query. You must use a FUNCTION in a
query.

TABLE - Resolved:
- Case-ID (Number Field)
- Resolver Name (Text Field)

QUERY: (Requires brackets around Case-Id field since Case-ID would
be interpreted as subtract ID field from Case field.)
SELECT sCreateRecords([Diary],[Case-Id])
FROM [P2P-Request]
WHERE [Case-ID] Not In (SELECT [Case-ID] FROM [Resolved]);


MODULE1:
Public FUNCTION sCreateRecords(sTextIn, CaseID) as Boolean
Dim strSQL As String
Dim aText As Variant
Dim i As Long
Dim strName As String
Dim iCount As Long
Dim dbAny As Database


Set dbAny = CurrentDb()
aText = Split(sTextIn, "Updated By: ")


For i = LBound(aText) To UBound(aText)
strName = Chr(34) & _
Trim(Left(aText(i), InStr(1, aText(i), vbLf) - 1)) & Chr(34)

strSQL = "INSERT INTO [Resolved].[Case-ID], [Resolved].[Resolver
Name]" & _
"Values(" & [Resolved].[Case-ID] & ", " & strName & ")"

dbAny.Execute strSQL
sCreateRecords = True 'Return True if any records were created
Next i

End FUNCTION


Using a query in this manner MIGHT fail. If it does, you might have to use
a query to build a recordset and loop through the recordset using VBA. That
sub or function might look like

Public Sub sDoIt() 'Name this anything you like
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim strSQL as String

Set dbAny = CurrentDB()
strSQL = "SELECT [Diary],[Case-Id] FROM [P2P-Request]" & _
" WHERE [Case-ID] Not In (SELECT [Case-ID] FROM [Resolved]);"
Set rstAny = dbAny.OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
Do While Not rstAny.EOF = False
sCreateRecords rstAny!Diary, rstAny![Case-id]
rstAny.MoveNext
Wend


End Sub