Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Use Query result to produce report

  1. #1
    Join Date
    15th August 2006
    Posts
    1

    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.






    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th August 2005
    Posts
    844

    Re: Use Query result to produce report

    Instead of using
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Store A Query Result In Worksheet
    By excelninja in forum Excel and/or Access Help
    Replies: 1
    Last Post: December 22nd, 2007, 20:00
  2. Formular To Produce A Negative Result
    By jserr in forum EXCEL HELP
    Replies: 2
    Last Post: February 20th, 2007, 11:49
  3. Find In Row & Report Result In Cell
    By Jordan in forum EXCEL HELP
    Replies: 8
    Last Post: April 15th, 2005, 18:25
  4. First Result in Query
    By torpedo13 in forum Excel and/or Access Help
    Replies: 6
    Last Post: April 12th, 2005, 08:26
  5. query result not showing in access report
    By opeyemi1 in forum Excel and/or Access Help
    Replies: 8
    Last Post: June 2nd, 2004, 21:56

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno