none
Microsoft Access 2003 Count Function RRS feed

  • Question

  • I have created Combo Boxes with a value list for each box. The combo box has 6 pre-determined values to select from. These values are in a values list. Once that value is selected from the drop down, the value is displayed and then the record is saved. What I'm trying to do is count how many of the six values have been selected and display that on the report form.

    For example: The person selects the cable length for a motor from a combo box. In this case M1, M2 and M3. I have a combo box for each motor. The available selections from the value list is HSS15, HSS20, HSS25. In this case someone picked for this record the following: M1=HSS15, M2=HSS15 and M3=HSS20.  Just to keep things simple, the next record is a duplicate of the first. What I would like to do is count how many of each motor cable have been selected. In this case, I should have 4 of the HSS15 and 2 of the HSS20. How can I keep a running count and display it in real time on my entry form and of course get a count total on the report.

    Thanks

    Wednesday, May 10, 2017 11:38 AM

Answers

  • Use a Totals query: create new query, select your table, click the Sigma button, drag the Motor field to the grid, and in the second column write: theCount: count(*)


    -Tom. Microsoft Access MVP

    • Marked as answer by Nemo Brannan Thursday, May 11, 2017 12:08 PM
    Wednesday, May 10, 2017 1:44 PM

All replies

  • Use a Totals query: create new query, select your table, click the Sigma button, drag the Motor field to the grid, and in the second column write: theCount: count(*)


    -Tom. Microsoft Access MVP

    • Marked as answer by Nemo Brannan Thursday, May 11, 2017 12:08 PM
    Wednesday, May 10, 2017 1:44 PM
  • I'm afraid that your table design is badly flawed; data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    You are also encoding data in value lists, which also violates the Information Rule.  Value lists should only be used for sets which are immutably fixed in the external world, e.g. days of the week, months of the year etc.

    The table needs decomposing into three related tables, one of which models whatever is the entity time currently represented by the table, one which models Motors, i.e. has six rows, one for each motor type, and finally a table which models the many-to-many relationship type between the two other tables by having two foreign keys, each of which references the primary key of one of the two referenced tables.  The primary key of the table is a composite one of the two foreign keys.

    Returning the number of each motor used over a set of row is then a simple matter of counting the rows per motor value in a query as Tom has described.

    For data input, instead of three combo boxes, the form would include a subform in continuous forms view, based on the table which models the many-to-many relationship type.  The subform would be linked to the parent form on the latter's table's primary key, and would include a single combo box in which a motor is selected.  Its RowSource property would be a query on the Motors table.  To select three motors would be a case of inserting three rows into the subform, selecting a different motor in each case.

    Ken Sheridan, Stafford, England

    Wednesday, May 10, 2017 5:23 PM
  • Hello,

    I think you need to loop all records and fields to calculate the count. To get it at runtime, you call the macro in Form_AfterUpdate event.

    Sub loopRecordset()
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim j As Integer
    Dim m As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM cable")
    rs.MoveFirst
      Do Until rs.EOF = True
       Dim val As String
       For Each fld In rs.Fields
       val = fld.Value
       Select Case val
       Case "HSS15"
       i = i + 1
       Case "HSS20"
       j = j + 1
       Case "HSS25"
       m = m + 1
       End Select
        Next
        rs.MoveNext
        Loop
        Debug.Print "HSS15: " & i; " HSS20: " & j; " HSS25: " & m;
    End Sub
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 11, 2017 8:24 AM
    Moderator
  • It works!!!!!!. Now all I have to do is add up my cable totals and I'm set. Many Thanks Tom. I use a drop down menu to select the cable type for each motor. I'd show you a screen shot but Microsoft says I can't include and image until I'm verified or whatever. Once again many thanks.
    Thursday, May 11, 2017 12:04 PM
  • Hey there Celeste. Thanks for the code. I'll try to implement when I can. I'm a very basic/novice user of Access. I learn as I go. I'll give it a shot once I figure out where to implement it.

    1000 Thanks

    Thursday, May 11, 2017 12:07 PM