Trouble with COUNT

[marshallb]

I have a feild on one of my tables that lists all of the different "AFSC's" for the personnel I track. I need to count the number of times each AFSC appers in that appears in the field. I have asked google and been through several pages here and can't quite wrap my head around it. This is the closet I have come to the solution: =Count(IIf([AFSC]="3D1X1",1)). All I get in the datasheet veiw is "CountOfAFSC". Can anyone help?

 

[Steve R.]

Below is an example from Access 2007 Help using COUNT within SQL. My opinion, you should NOT use the IIF function. You would accomplish what you are asking through the use of the WHERE clause.

Sub CountX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
   
    ' Calculate the number of orders shipped 
    ' to the United Kingdom.
    Set rst = dbs.OpenRecordset("SELECT" _
        & " Count (ShipCountry)" _
        & " AS [UK Orders] FROM Orders" _
        & " WHERE ShipCountry = 'UK';")
    
    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the 
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

You may want to look into the DCOUNT function too. DCOUNT would be slower.

IntResult=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

 

[marshallb]

I have not worked with SQL yet. I am not even sure where to start with that. I am trying to count the number of times each AFSC appears in that field on my personalinfo table. I have tried using Dcount but didn't get that to work either. My ultimate goal is to be able to create a report with the numbers of each afsc assigned to my unit. I have to give these numbers to the leadership every week and am tired of counting them.

 

[Steve R.]

DCOUNT appears to be what you need. You can try something like this:

IntResult=DCount("*", "TableName", "[AFSC]] = '3D1X1'")
debug.print intResult

The major shortcoming of the code above is that it will only work with the value "3D1X1". If you are also after other values, you will have to either manually substitute it or use a variable (textbox) in a form.

Additionally, look up in Access 2007 Help "How to: Include Quotation Marks in String Expressions". Also look up DLOOKUP. While you won't be using DLOOKUP, the explanatory text is better than that found in DCOUNT.

 

[marshallb]

Thank you for your help. I found a way to do it that actually makes it easier. On to my next step.

I am able generate a list that gives me the numbers of each AFSC (qryAFSC Assigned), but I am trying to set up another query to show me how many of each AFSC has an estimated return date greater than 30 days from today. I have included a screenshot of what I am trying to do (qryAFSC DEPLOYED) and what the result is. Any help would be greatly appreciated.