counting problem

[JBurlison]

Ok i have 1-X diffrent Machine types (around 30 or so) i want to make a report that counts how meny on each machine type there is. First shown on a form then in report how do i count them in report view (like =Count([control])??? or somethig along those lines.) and counting each type and displying it on a form.

 

[pbaldy]

Try a control source of

=Sum(IIf(MachineType = "ABC", 1, 0))

Though with that many I would use a subreport based on a totals query that returned the count of each type.

 

[JBurlison]

Challange

Hummm how could you make that so it dose not requre a code change if a new type is added this is what im thinking:

Private Sub Command16_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsType As DAO.Recordset
    Dim MType As String
    Dim TypeCount As Integer
    Dim RecCount As Integer
    Dim loopcount As Integer
    Dim CurRcCount As Integer
    Dim TotalTypeCount As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Main Inventory", dbOpenDynaset)
    Set rsType = db.OpenRecordset("Type of Product", dbOpenDynaset)
    
With rs
    .MoveFirst
    .MoveLast
    .RecordCount = RecCount
End With
    
With rsType
    .MoveFirst
    .MoveLast
    .RecordCount = TotalTypeCount
    .MoveFirst
    Do Until TypeCount >= TotalTypeCount
    .Fields("Type of Product") = MType
        With rs
            .MoveFirst
             Do Until CurRcCount >= RecCount
                If .Fields = MType Then
                    Me.Label(0 + TypeCount) = MType
                    Me.Text(0 + TypeCount) = TypeCount
                    CurRcCount = CurRcCount + 1
                End If
                .MoveNext
            Loop
        End With
    TypeCount = TypeCount + 1
    .MoveNext
    Loop
 End With
End Sub

Code Counts how meny of each machine type are in inventory, have not tried it but im sure it dose not work.

 

[namliam]

To difficult I think...

Why not "simply" use a query?

 

[pbaldy]

Like I said, I'd probably use a subreport based on a totals query. It would be dynamic and automatically pick up new types.

 

[JBurlison]

how could i create a query like that?

 

[pbaldy]

Generally start a new query and click on the icon that looks like a big "E". That will add a totals row to your query and default everything to "Group By". You want to Group By your Type field and presumably count some other field. Without knowing your table structure, it's hard to be more specific. The SQL might look something like:

SELECT MachineType, Count(Whatever) AS TotalCount
FROM TableName
GROUP BY MachineType

 

[JBurlison]

Not fammiliar with SQL this is what comes up in SQL

SELECT [Main Inventory].[Product Type]
FROM [Main Inventory]
ORDER BY [Main Inventory].[Product Type];

 

[pbaldy]

It doesn't look like you clicked on that icon (or View/Totals with the query in design mode). Try this for chuckles:

SELECT [Product Type], Count([Product Type]) As TotalType
FROM [Main Inventory]
GROUP BY [Product Type]
ORDER BY [Product Type]

The spaces in your names are more trouble than they're worth in the long run.

 

[JBurlison]

Nice works like a charm! Thanks again!