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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 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?
>
> --
> 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
>

Relevant Pages

  • RE: How to extract portion of text
    ... Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part ... Dim MyArray() As String ... I really need help to solve for the 4th part query. ...
    (microsoft.public.access.queries)
  • Re: Table of cells contains comingled text (A,B,C,1,2,3)-formated as .
    ... though you'll need to call a custom VBA function in a query. ... Delimiter As String) As Long ... 'appears in Target, assuming that Target ...
    (microsoft.public.access.gettingstarted)
  • Re: TclOO: Renaming "new" and "create" methods?
    ... organizations use relational databases is because of the ability to ... normalize data and to query the data, ... base objects. ... As long as you've got *some* control over the query, ...
    (comp.lang.tcl)
  • Re: Option groups, passing a string value to table
    ... but that is the recommended approach with Relational databases. ... having the words there doesn't improve maintenance: ... query and use the query rather than the table (seldom, if ever, should you ... "Scott" wrote in message ...
    (microsoft.public.access.forms)
  • Re: Queries and OO
    ... Queries is a way to decouple how objects are mapped into a container, ... long time ago there were database types without query ... But maybe the query capabilities was one of the reasons behind the ... success story of relational databases. ...
    (comp.object)