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

From: WIM4246 (WIM4246_at_discussions.microsoft.com)
Date: 10/02/04 

Date: Sat, 2 Oct 2004 13:43:06 -0700

"John Nurick" wrote:

> 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"
> <WIM4246@discussions.microsoft.com> wrote:
>
> >Have a "Log ***" 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?
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.

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
>