Counting Assests

[lovelydan]

i want to design a module to count each assets on a table so i can generate an aggregated data report of for each item. this is where i got stuck:Help!

Private Sub countAssets()
     Dim mydb As Database, MysetAssets As Recordset, MysetSoftware As Recordset
        Set mydb = DBEngine.Workspaces(0).Databases(0)
    '............................................................................
     
        Set MysetAssets = mydb.OpenRecordset("qryAssetDetails", DB_OPEN_DYNASET)
        Set MysetSoftware = mydb.OpenRecordset("qrySoftwareDetails", DB_OPEN_DYNASET)
     
        Dim assName As Field, assStatus As Field, assCondition As Field
        Dim countName As Integer, countStatus As Integer, countCondition As Integer, Found As Boolean
        Dim foundName As String, foundStatus As String, foundCondition As String
     
        Set assName = MysetAssets![equipCatName]
        Set assStatus = MysetAssets![equipStatus]
        Set assCondition = MysetAssets![assCondition]
        countName = 0
        countStatus = 0
        countCondition = 0
        Found = False
     
        MysetAssets.MoveFirst
        Do Until MysetAssets.EOF
            Case assName <> ""
            Found = True
            Dim strWhere As String
     
        strWhere = "(foundname=#" & assName & "#) AND " & _
                   "(foundstatus='" & assStatus & "')"
     
     
       countname = countname + 1
     
            End Select
        Myset.MoveNext
        Loop
    End Sub

i just have a table to store different assets in a field called assName eg; CPU, Monitor, UPS, Table...etc. so what i want to do is to design a module that can counts each item separately. for example i used to use a module below to do this.
this module worked just fine the problem is, i need to amend it if a new item is introduced so i need to design a module which will just move to the first record in a table and finds items and start counting without being amended.

hope this will give you enough information. my regards

Do Until Myset.EOF
            Select Case AssetName
                Case Is = "CPU"
                    countCPU = countCPU + 1
                Case Is = "Monitor"
                    countMonitor = countMonitor + 1
                Case "UPS"
                    countUPS = countUPS + 1
                Case Is = "Kettle"
                countKettle = countKettle + 1
                Case Is = "Book"
                countBook = countBook + 1
                Case Is = "TV"
                countTV = countTV + 1
                Case Else
                    countothers = countothers + 1
        End Select
        Myset.MoveNext
        Loop

[BitBlt]

Frankly, you'd be better off spending your time designing an aggregation (i.e. "Group By") query in SQL on the database side. It would be more efficient and probably perform better. What database management system are you using?

[lovelydan]

inventory management system, would you give me a light on that...im not good with queries either, im just trying to develop a small DB 4 counting different categories of assests. an example would do..

[BitBlt]

It could look something like this:

select assetname, count(*) 
from assettable
group by assetname

Keep in mind that the names I chose above are made-up, because I don't know what your data structures look like. You'll have to do some investigating to find the actual names in your own environment. Maybe you should get the help of some friendly database administrator or senior developer where you are.