Table of cells contains comingled text (A,B,C,1,2,3)-formated as .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have a "Log sheet" of digital photo file numbers which has a column formated
as text with alpha & numerical (formated as text) entries to designate the
printing instructions for each file.

So a cell may contain a single entry as "A" or "C" or "1" or "5" etc. or it
could contain multiple combintations as "B,3" or "D,1,1" which give the lab
instructions on what to print and how many of each unit to print.

Need to total the number of "A" or "B" or "1" or "3" entries that occur in
the "Log" table. Can this be done?
 
Yes, though you'll need to call a custom VBA function in a query. Access
- like all relational databases, is optimised to work with data in which
each field contains a single value, not multiple values as here.

Paste this function into a new module in your database:

Public Function CountOccurencesInDelimString( _
Target As Variant, _
CharToCount As String, _
Delimiter As String) As Long

'Counts the number of times CharToCount
'appears in Target, assuming that Target
'is a string divided into fields by the
'single character Delimiter.
'e.g. if Target is "1,2,1,3,4"
' CharToCount is "1"
'and Delimiter is ","
'the function returns 2.

Dim Items As Variant
Dim j As Long
Dim Acc As Long

If IsNull(Target) Then
CountOccurencesInDelimString = 0
Exit Function
End If

Items = Split(Target, Delimiter)
For j = 0 To UBound(Items)
If Items(j) = CharToCount Then Acc = Acc + 1
Next

CountOccurencesInDelimString = Acc
End Function


Then create a Totals query which calls the function and sums the result,
using a separate calculated field for each of the items you want to
total (A,B,1,3). Each of these fields will look like this in the query
design grid, where Num_1 is the name of the calculated field with the
number of "1"s and FieldName is the name of the actual field in the
table:

Num_1: Sum(CountOccurencesInDelimString([FieldName],"1",","))

The SQL view of the query will be like this:

SELECT
SUM(CountOccurencesInDelimString([NumberTxt], "1", ",")) AS Num_1,
SUM(CountOccurencesInDelimString([NumberTxt], "3", ",")) AS Num_3,
SUM(CountOccurencesInDelimString([NumberTxt], "A", ",")) AS Num_A,
SUM(CountOccurencesInDelimString([NumberTxt], "B", ",")) AS Num_B
FROM tblMyTable
;

If there are many records in the table this may take a minute or so to
run.
 
John Nurick said:
Yes, though you'll need to call a custom VBA function in a query. Access
- like all relational databases, is optimised to work with data in which
each field contains a single value, not multiple values as here.

Paste this function into a new module in your database:

Public Function CountOccurencesInDelimString( _
Target As Variant, _
CharToCount As String, _
Delimiter As String) As Long

'Counts the number of times CharToCount
'appears in Target, assuming that Target
'is a string divided into fields by the
'single character Delimiter.
'e.g. if Target is "1,2,1,3,4"
' CharToCount is "1"
'and Delimiter is ","
'the function returns 2.

Dim Items As Variant
Dim j As Long
Dim Acc As Long

If IsNull(Target) Then
CountOccurencesInDelimString = 0
Exit Function
End If

Items = Split(Target, Delimiter)
For j = 0 To UBound(Items)
If Items(j) = CharToCount Then Acc = Acc + 1
Next

CountOccurencesInDelimString = Acc
End Function


Then create a Totals query which calls the function and sums the result,
using a separate calculated field for each of the items you want to
total (A,B,1,3). Each of these fields will look like this in the query
design grid, where Num_1 is the name of the calculated field with the
number of "1"s and FieldName is the name of the actual field in the
table:

Num_1: Sum(CountOccurencesInDelimString([FieldName],"1",","))

The SQL view of the query will be like this:

SELECT
SUM(CountOccurencesInDelimString([NumberTxt], "1", ",")) AS Num_1,
SUM(CountOccurencesInDelimString([NumberTxt], "3", ",")) AS Num_3,
SUM(CountOccurencesInDelimString([NumberTxt], "A", ",")) AS Num_A,
SUM(CountOccurencesInDelimString([NumberTxt], "B", ",")) AS Num_B
FROM tblMyTable
;

If there are many records in the table this may take a minute or so to
run.


Have a "Log sheet" of digital photo file numbers which has a column formated
as text with alpha & numerical (formated as text) entries to designate the
printing instructions for each file.

So a cell may contain a single entry as "A" or "C" or "1" or "5" etc. or it
could contain multiple combintations as "B,3" or "D,1,1" which give the lab
instructions on what to print and how many of each unit to print.

Need to total the number of "A" or "B" or "1" or "3" entries that occur in
the "Log" table. Can this be done?
Click to expand...
Click to expand...

THANKS! & SUCH A SPEEDY REPLY - I'm a Newbie to using these user groups and
Excel - but this looks like it will work - it will be late tonight before I
get to implement these details, but it looks like the right answer - Thanks
for the HELP.

Wayne M
 
WIM4246 said:
John Nurick,
Click to expand...

This looks like the right track that I should be doing, but it notice now
that the Info refers to Access, and I thought that I had submitted my
question as an New Excel user.

If these instructions are to also work in Excel, I guess I'm not proficient
enough to apply the terminology correctly, because I'm not even getting a
formula recognitxion error - but I'm trying to run these functions from a
cell within my Excel Work Sheet.

Can you explain or give a short example of the syntax and where I should be
entering it?


Thanks, Wayne M
Can it be entere into the same Work Sheet - as in a cell following the Data
Column -

& where does the "Range" of the Data Selection to be used get entered?

John Nurick said:
Yes, though you'll need to call a custom VBA function in a query. Access
- like all relational databases, is optimised to work with data in which
each field contains a single value, not multiple values as here.

Paste this function into a new module in your database:

Public Function CountOccurencesInDelimString( _
Target As Variant, _
CharToCount As String, _
Delimiter As String) As Long

'Counts the number of times CharToCount
'appears in Target, assuming that Target
'is a string divided into fields by the
'single character Delimiter.
'e.g. if Target is "1,2,1,3,4"
' CharToCount is "1"
'and Delimiter is ","
'the function returns 2.

Dim Items As Variant
Dim j As Long
Dim Acc As Long

If IsNull(Target) Then
CountOccurencesInDelimString = 0
Exit Function
End If

Items = Split(Target, Delimiter)
For j = 0 To UBound(Items)
If Items(j) = CharToCount Then Acc = Acc + 1
Next

CountOccurencesInDelimString = Acc
End Function


Then create a Totals query which calls the function and sums the result,
using a separate calculated field for each of the items you want to
total (A,B,1,3). Each of these fields will look like this in the query
design grid, where Num_1 is the name of the calculated field with the
number of "1"s and FieldName is the name of the actual field in the
table:

Num_1: Sum(CountOccurencesInDelimString([FieldName],"1",","))

The SQL view of the query will be like this:

SELECT
SUM(CountOccurencesInDelimString([NumberTxt], "1", ",")) AS Num_1,
SUM(CountOccurencesInDelimString([NumberTxt], "3", ",")) AS Num_3,
SUM(CountOccurencesInDelimString([NumberTxt], "A", ",")) AS Num_A,
SUM(CountOccurencesInDelimString([NumberTxt], "B", ",")) AS Num_B
FROM tblMyTable
;

If there are many records in the table this may take a minute or so to
run.


Have a "Log sheet" of digital photo file numbers which has a column formated
as text with alpha & numerical (formated as text) entries to designate the
printing instructions for each file.

So a cell may contain a single entry as "A" or "C" or "1" or "5" etc. or it
could contain multiple combintations as "B,3" or "D,1,1" which give the lab
instructions on what to print and how many of each unit to print.

Need to total the number of "A" or "B" or "1" or "3" entries that occur in
the "Log" table. Can this be done?
Click to expand...
Click to expand...

THANKS! & SUCH A SPEEDY REPLY - I'm a Newbie to using these user groups and
Excel - but this looks like it will work - it will be late tonight before I
get to implement these details, but it looks like the right answer - Thanks
for the HELP.

Wayne M
Click to expand...
 
Since you posted your question in an Access database forum I read it as
referring to an Access problem. If you're using Excel, you should ask in
an Excel forum. But first, try using a formula like this in a cell to
the right of one of your "1,2,1,A,C" values - which I'll assume is in
cell A2:

=CountOccurencesInDelimString($A2, "1", ",")

Then fill down the column with the same formula (so the references
adjust to $A3, $A4...).

Next use a =SUM() formula at the foot of this column: that should give
you the count of "1"s.

Repeat in adjacent columns for the other values you want to count.

WIM4246 said:
John Nurick,
Click to expand...

This looks like the right track that I should be doing, but it notice now
that the Info refers to Access, and I thought that I had submitted my
question as an New Excel user.

If these instructions are to also work in Excel, I guess I'm not proficient
enough to apply the terminology correctly, because I'm not even getting a
formula recognitxion error - but I'm trying to run these functions from a
cell within my Excel Work Sheet.

Can you explain or give a short example of the syntax and where I should be
entering it?


Thanks, Wayne M
Can it be entere into the same Work Sheet - as in a cell following the Data
Column -

& where does the "Range" of the Data Selection to be used get entered?

John Nurick said:
Yes, though you'll need to call a custom VBA function in a query. Access
- like all relational databases, is optimised to work with data in which
each field contains a single value, not multiple values as here.

Paste this function into a new module in your database:

Public Function CountOccurencesInDelimString( _
Target As Variant, _
CharToCount As String, _
Delimiter As String) As Long

'Counts the number of times CharToCount
'appears in Target, assuming that Target
'is a string divided into fields by the
'single character Delimiter.
'e.g. if Target is "1,2,1,3,4"
' CharToCount is "1"
'and Delimiter is ","
'the function returns 2.

Dim Items As Variant
Dim j As Long
Dim Acc As Long

If IsNull(Target) Then
CountOccurencesInDelimString = 0
Exit Function
End If

Items = Split(Target, Delimiter)
For j = 0 To UBound(Items)
If Items(j) = CharToCount Then Acc = Acc + 1
Next

CountOccurencesInDelimString = Acc
End Function


Then create a Totals query which calls the function and sums the result,
using a separate calculated field for each of the items you want to
total (A,B,1,3). Each of these fields will look like this in the query
design grid, where Num_1 is the name of the calculated field with the
number of "1"s and FieldName is the name of the actual field in the
table:

Num_1: Sum(CountOccurencesInDelimString([FieldName],"1",","))

The SQL view of the query will be like this:

SELECT
SUM(CountOccurencesInDelimString([NumberTxt], "1", ",")) AS Num_1,
SUM(CountOccurencesInDelimString([NumberTxt], "3", ",")) AS Num_3,
SUM(CountOccurencesInDelimString([NumberTxt], "A", ",")) AS Num_A,
SUM(CountOccurencesInDelimString([NumberTxt], "B", ",")) AS Num_B
FROM tblMyTable
;

If there are many records in the table this may take a minute or so to
run.


On Sat, 2 Oct 2004 07:37:01 -0700, "WIM4246"

Have a "Log sheet" of digital photo file numbers which has a column formated
as text with alpha & numerical (formated as text) entries to designate the
printing instructions for each file.

So a cell may contain a single entry as "A" or "C" or "1" or "5" etc. or it
could contain multiple combintations as "B,3" or "D,1,1" which give the lab
instructions on what to print and how many of each unit to print.

Need to total the number of "A" or "B" or "1" or "3" entries that occur in
the "Log" table. Can this be done?
Click to expand...

THANKS! & SUCH A SPEEDY REPLY - I'm a Newbie to using these user groups and
Excel - but this looks like it will work - it will be late tonight before I
get to implement these details, but it looks like the right answer - Thanks
for the HELP.

Wayne M
Click to expand...
Click to expand...
 
If I correctly understand, this procedure will end up with a column for each
value to the right of the comingled cell. If the value being searched for
does not exist in that cell the result will be zero. Then individually
summing all the "Value Columns" (A,B,C ... 1,2,3 etc) will give me the answer
for each value.

Attempting to use the suggested syntax results in a NAME ERROR. I have
exhausted every solution that I can find for the cause of this Name Error.
(Such as installing "Analysis Tool Pack" & all the other suggestions for Name
Errors - but I'm not sure that I'm executing the suggestions correctly in all
cases) Also tried to get a post to an Excel Group, and can't seem to get it
started so I think the answer your are giving is correct, I'm just making
some error in its application.

Any further advice would be greatly appreciated.

Thanks
 
If I correctly understand, this procedure will end up with a column for each
value to the right of the comingled cell. If the value being searched for
does not exist in that cell the result will be zero. Then individually
summing all the "Value Columns" (A,B,C ... 1,2,3 etc) will give me the answer
for each value.
Click to expand...

That's the idea.
Attempting to use the suggested syntax results in a NAME ERROR. I have
exhausted every solution that I can find for the cause of this Name Error.
(Such as installing "Analysis Tool Pack" & all the other suggestions for Name
Errors - but I'm not sure that I'm executing the suggestions correctly in all
cases) Also tried to get a post to an Excel Group, and can't seem to get it
started so I think the answer your are giving is correct, I'm just making
some error in its application.
Click to expand...

Did you paste the CountOccurencesInDelimString function into a module in
your workbook? If not, any use of its name in a worksheet formula will
give you a #NAME? error. (Also, make sure that the name of the module is
not the same as the name of the function.)
 
Back
Top