Count How Many Items Contain Certain Value

[infinitx]

Hi,

Here's in essence what I want to do:

Say I have a text box with the following values in it:

NOTE: These values are item classifications

Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

I want to compute how many total Math Values there are, how many total Science Values there are, etc....

So in this case, I want the finished product to look something like this:

Math (4)
Science (2)
Reading (1)
Writing (2)

How can this be done?

P.S. - The text box is just a text box with values in it. When an item is selected for the test, its Item Classification is transfered to a text box.

Here's the code that I use:

txtTextBox=txtTextbox & vbcrlf & vbcrlf & txtItemClassification

So the text box is just free standing and not linked to a table or a query.

Thank you

 

[WayneRyan]

infinitx,

The query you want is:

Select Topic, Count(*)
From   YourTable
Group By Topic
Order By Topic;

But that would be everything in your table:

Select Topic, Count(*)
From   YourTable
Where Topic = '" & Forms![YourForm]![YourTextBox] & "'"
Group By Topic
Order By Topic;

The above will give you just the ones for your textbox. Put the
Forms![YourForm]![YourTextBox] as the Criteria for the Topic
column of your query.

Wayne

 

[infinitx]

WayneRyan,

Since the text box is free standing and not linked to any table or query, is it possible to do this without using a query?

Thank you

 

[Rich]

Why not normalise your structure, then it would be easy

 

[Pat Hartman]

Forms don't store data, tables store data. If you were to count what a form contained, the answer would always be 1.

 

[infinitx]

I just want to count how many items of the same value are in a text box. If text box is not linked to a table or a query.

 

[Pat Hartman]

There is only ONE instance of a form control. You cannot count things that do not exist.

 

[infinitx]

So you are saying that I cannot count the values in a text box? Would I have to create a table and store these values there?

If the following is one record:

Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

Could I accomplish this:

Math (4)
Science (2)
Reading (1)
Writing (2)

Thank you

 

[RichO]

Looking at your first post, I see that you are using code to concatenate these subject names into a single string stored in your unbound text box. May I ask why you want to do it this way?

There is no simple way to count the items in the string. You would have to write a piece of VB code to split the string into an array, sort, then count.

 

[infinitx]

May I ask why you want to do it this way?

That is the way I learned how to do it. If there is a better way, please tell me. Is there an easier way that would make counting easier?

Thank you

 

[Mike375]

Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

Where are the above coming from. As Rich has says trying to count words like "math" in a bunch of text will need a lot of fiddling about. Even I would not attempt that

But perhaps what is producing the different words can be counted. That is why I asked "Where are the above coming from?"

At first glance it would appear that you should have a separate table that is holding the various words with one word per record. That is, there would be four records containing "math"

With your question

If the following is one record:
Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

you again run into the problem of having to separate out the various words so the count can be done.

Mike

 

[infinitx]

So what are your suggestions about what I should do? I need to somehow have a count of the item classifications. Can I have the item classification be added to a string when the item is intially added to the test and then have some kind of check such as this:

If txtItemClassification = StrCheck Then
txtCount.Value = txtCount + 1
End If

Thank you

 

[Mike375]

Where is this

Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

coming from and what does it represent. Why is Math there 4 times and Science 2 times etc. What does it relate to or who does it relate to.

Mike

 

[infinitx]

Math
Science
Math
Reading
Writing
Science
Math
Math
Writing

These are classifications for items. This is so the user knows whether the item they are looking at is a Math Item, a Science Item, etc....

Why is Math there 4 times and Science 2 times

This goes to show that the user has selected 4 math items, 2 science items, 2 writing items, and 1 reading item to appear on the test.

Here's a table structure where everything comes from:

Item ID (Autonumber)
Item Question (Memo)
Possible Answers (Memo)
Correct Answer (Text)
ICL (Item Classification) (Text)


There is a list box on a form that lists all of the Item IDs currently in the database. When they click on the Item ID, that item is added to the test.

This is the code that I currently have in the List Box's AfterUpdate Event:

txtTextBox = txtTextbox & vbcrlf & vbcrlf & txtItemClassification

But as has been said, it is very difficult to count the words in the text box so I guess that won't work.

Did this give you a better idea of what I am doing?


Thank you

 

[Mike375]

Item ID (Autonumber)
Item Question (Memo)
Possible Answers (Memo)
Correct Answer (Text)
ICL (Item Classification) (Text)

Is the last field where the words Math, Science etc appearing and if so is there a record for each Math, Science and so on.

Is this a situation whereby

This goes to show that the user has selected 4 math items, 2 science items, 2 writing items, and 1 reading item to appear on the test

there would be 8 exams or 8 questions with four being "different" math items, two being "different" science items and two being "different" writing items.

So would there be records such as

Item ID...Item Question...Possible Answers...Correct Answer..ICL
1...........xyz.................abc.....................lmn..................math1
2...........efg.................pqr......................rst...................math2
3...........abc................uvw.....................def..................Science1

etc.

Does the above type of thing relate to some individual who either takes these tests or gives these tests or both. If these were relating to an individual taking or giving tests then the above example could be a table that via a query supports a subform.

By the way, once you have math, science etc in a field, whether it be in a table or a field created in a query, then counting etc becomes easy.

Also, if there are several versions af math, science etc then each of these "categories" might need its own field.

Thus you might have fields like

Math........Science

with records like this

Math1......Science for Everyone
Math2......Science 1
Math3......Science 2

etc. This would be similar to having a field for Suburb and the all the records for that field contain the name of suburbs and then a field for State and all the records for that field contain the name of states.

Mike

 

[infinitx]

I have found what I was looking for!

CyberLynx on dBforums provided a great example of exactly what I was looking for.

Check out the attachment.

Thank you all for helping as I learned a lot!


Thank you

 

[raskew]

Think we can agree that a text-box with multiple items is not a good way to go.

However, it makes for a fun exercise to develop a routine that would take
"Math Science Math Reading Writing Science Math Math Writing"

...and return the count for each item in the string.

Try copying / pasting this to a new module, then call it from the debug window with:
call DriveMe("zTest", "Math Science Math Reading Writing Science Math Math Writing", " ")

Note: The routine will delete/recreate table zTest and query zTest2.

Sub DriveMe(ptName As String, ByVal pItem As String, pdelim As String)
'call DriveMe("zTest", "Math Science Math Reading Writing Science Math Math Writing", " ")
Dim db        As Database
Dim qd        As QueryDef
Dim rs        As Recordset
Dim strHold   As String
Dim strName   As String
Dim strSQL    As String
Dim test      As String
Dim NL, TB, Msg, Msg2, Title, Response

    NL = Chr(13) & Chr(10)  ' Define newline.
    TB = Chr(9) ' Define tab.
    Title = "What to do, what to do?"
    'Create header for MsgBox
    Msg = "Class" & TB & TB & "Count" & NL & NL
    
    Set db = CurrentDb
    strName = ptName
    strHold = Trim(pItem) & IIf(Right(pItem, Len(pdelim)) <> pdelim, pdelim, "")
    strSQL = "SELECT Item, Count(Item) AS CountOfItem" _
    & " FROM " & strName _
    & " GROUP BY Item;"
   
    
'Step 1)    Prepare temp table
    On Error Resume Next
    With db
        .Execute "DROP TABLE " & strName & ";"
        .Execute "CREATE TABLE " & strName & " ( Item TEXT (25) );"
    End With
   ' db.TableDefs.Refresh
    
'Step 2)    Populate temp table with elements from strHold
    Set rs = db.OpenRecordset(strName)
    Do While InStr(strHold, pdelim) > 0
       With rs
          .AddNew
          !Item = Trim(ySplit(strHold, pdelim, True))
          .Update
       End With
       strHold = ySplit(strHold, pdelim, False)
    Loop
    rs.Close
    
'Step 3)    Prepare temp query
    test = db.QueryDefs("ztest2").Name
    If Err <> 3265 Then
       db.QueryDefs.Delete ("ztest2")
    End If

    Set qd = db.CreateQueryDef("ztest2", strSQL)
    Set rs = db.OpenRecordset("ztest2")
    Do While Not rs.EOF
       Msg2 = Msg2 & rs!Item & Space(14 - Len(rs!Item)) & TB & "(" & rs!countofitem & ")" & NL
       rs.MoveNext
    Loop
    rs.Close
'Step 4)    Display the results and clean-up
    Msg = Msg & Msg2 & NL
    Msg2 = ""
    Response = MsgBox(Msg, , Title)
    
    db.QueryDefs.Delete ("zTest2")
    qd.Close
    db.Close
    Set db = Nothing

End Sub

Function ySplit(ByVal pTarget As String, _
                pItem As String, _
                Optional ShowLeft As Boolean = True) _
                As String
'*******************************************
'Purpose:    Splits a string to the left or
'            right of pItem.  A97 workaround
'            for Split() function.
'Coded by:   raskew
'Inputs:     1)  ySplit("The quick+ brown fox", "+", True)
'            2)  ysplit("The quick+ brown fox", "+", False)
'Output:     1)  The quick
'            2)  brown fox
'*******************************************

Dim strLeft  As String
Dim strRight As String
Dim n        As Integer

    n = InStr(pTarget, pItem)
    
    If n = 0 Then
       ySplit = pTarget
    Else
       ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
       strLeft = Left(pTarget, n - 1)
       strRight = Mid(pTarget, n + 1)
       ySplit = IIf(ShowLeft, strLeft, strRight)
    End If
    
End Function

Bob