Use Query result to produce report

[spartacus13210]

hi there,
i am trying to produce a report in excel and not knowing much i am sort of stuck. Hopefully, one of you can help me on this matter.

The sql below is pulling out how many days help logs from a certain facility have been open. using that information, i want to create a report that puts the query result into brackets of days open.

Basically, i want to show the count of logs in a particular bracket [I am attaching a sample report (samplereport.doc) to help you visualize the information and layout].

The report should provide a count of the number of logs that remain open at each facility for a varying numbers of days. The last column in the report does a count of how many call logs with the call status of "Long Term" were recived by the facility.

Sub LogAging() 
     ' Log Aging
     
     
     
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "ODBC;DRIVER=SQL Server;SERVER=xxxxxxxserver;UID=xxxxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xxxxxxxdatabase;Trusted_Connection=Yes" _ 
        , Destination:=Range("A7")) 
        .CommandText = Array( _ 
        "SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) AS Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) & "FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID AND ((CallLog.CallStatus" _ 
        , " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID") 
        .Name = "HospitalLogAging" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .PreserveColumnInfo = True 
        .Refresh BackgroundQuery:=True 
         
    End With 
End Sub 

The above query supplies the information need to complete the report, the only two columns that are needed for creating the report are Exp1 and HospID. The file--queryresult.xls is populated with values for the above two columns.

I would really appreciate any help regarding this post.

Thanks,
Anupam


Also posted at: http://www.vbaexpress.com/forum/showthread.php?t=9172

[ShosMeister]

Instead of using

SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) As Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) & "FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID And ((CallLog.CallStatus" _ 
, " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID 

Why not eliminate the return of the CallStatus, RecvdDate and CallID and add in a COUNT(Exp1) and use the Group By clause on your HospID. If you take your sample .xls file and use the Data:Subtotals function on it as I suggested, you appear to get at least the raw data that you want. Then it may be easier for you to formulate getting the report you need.

As an alternative, you may be able to take the raw data and use a pivot table (which I am not familiar with) to generate the proper totals and format for the report. If that's the method you want to try, I'd suggest posting that in the Excel VBA help section or one of the OZ guys could move it there if they feel it's more appropriate.