Announcement

Collapse
No announcement yet.

Use Query result to produce report

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use Query result to produce report

    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.






    Code:
    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
    Attached Files

  • #2
    Re: Use Query result to produce report

    Instead of using
    Code:
    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.
    ---
    Old Programmers Never Die ... They just lose their bits

    Comment

    Working...
    X