Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    73

    Unanswered: Ranking Details on a report

    I have a report that counts record details for each group the report is sorted by. Does anyone know if I can rank them in the report from the highest record count to the lowest?

    I am using this statment to count the records in each group in the report
    Code:
    ="Summary for " & "'Failure Reason' = " & " " & [FailureReason] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"
    The statement is in a label box in the section footer.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well that's a little tricky. Once a detail is printed in a report, you can't go and move it around again.

    You'd have to pull a count figure into each record and add another grouping level.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Posts
    73
    Do you know where I can get an example of this?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use a subselect to pull the number.

    SELECT yourid, field1, field2, (SELECT COUNT(field1) FROM yourtable a WHERE a.yourid=yourTable.yourid) AS groupingLevel
    FROM yourTable
    GROUP BY yourid
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Posts
    73
    I will try that ....... Thanks

  6. #6
    Join Date
    Apr 2004
    Posts
    73
    I have been playing around with this and can't seem to get it to work. Here is the SQL code I have to select the fields.
    Code:
    Alter Procedure usp_rptQualityReport As
    
    SELECT 
    	tblRMAData.RMANumber,
    	tblRMAData.JobName, 
    	tblRMAData.Date, 
    	tblFailureReasons.LintItemID, 
    	tblLineItems.Qty, 
    	tblLineItems.Model, 
    	tblLineItems.ReportDate, 
    	tblFailureReasons.FailureReason, 
    	tblTestComponentFailures.ComponentID, 
    	tblTestComponentFailures.FailureCause
    FROM 
    	tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
    
    WHERE 
    	(((tblFailureReasons.FailureReason) <> N'NONE'))
    
    ORDER BY
    	tblFailureReasons.FailureReason
    I can't seem to get what you gave me incorporated into it.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    procedure... are you working on an .adp or otherwise MSSQL based query?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Apr 2004
    Posts
    73
    yes ... it is an adp project.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by thatdude
    yes ... it is an adp project.

    Allrighty... could you post your table structure? And what do you intend to do using N'NONE'?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would suggest having the query do the grouping and counting before you get to the report. Use GROUP BY and COUNT to get the groups and counts.

  11. #11
    Join Date
    Apr 2004
    Posts
    73
    I am using a pass thru query on an access front end to run the SQL Stored Procedure. "NONE" is only to exclude results from the query that have this value.

  12. #12
    Join Date
    Apr 2004
    Posts
    73
    I made some changes and created a view in SQL. But I still can't get them to count and then rank the records.

    Here is the Stored Procedure I am using:
    Code:
    Alter Procedure usp_rptQualityReport3 As
    
    SELECT * FROM viewQualityReport
    
    (SELECT COUNT(FailureReason)) AS groupingLevel
    
    WHERE 
    	(((viewQualityReport.FailureReason) <> N'NONE'))
    
    ORDER BY
    	FailureReason

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try running this in QA and to see how it works:

    Code:
    CREATE TABLE #temp (tempID INT, field1 NVARCHAR(50))
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (1, 'ENTRY ONE')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (2, 'ENTRY TWO')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (3, 'ENTRY THREE')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (1, 'ENTRY FOUR')
    GO
    
    SELECT tempID, field1, 
       (SELECT COUNT(*) FROM #temp t WHERE t.tempID = #temp.tempID)
    FROM #temp
    ORDER BY 3 DESC
    GO
    
    DROP TABLE #temp
    commented version:
    Code:
    /* nothing tricky here, just creating a temp	*/
    /* table and populating it with some data	*/
    
    CREATE TABLE #temp (tempID INT, field1 NVARCHAR(50))
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (1, 'ENTRY ONE')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (2, 'ENTRY TWO')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (3, 'ENTRY THREE')
    GO
    INSERT INTO #temp (tempID, field1)
    VALUES (1, 'ENTRY FOUR')
    GO
    
    /* Here's the meat of it.  We start by selecting*/
    /* the fields from the temp table		*/
    SELECT tempID, field1, 
    /* Now we attach a subselect that pulls the 	*/
    /* count of tempID				*/
    	(SELECT COUNT(*) FROM #temp t WHERE t.tempID = #temp.tempID)
    FROM #temp
    /* Order  by references the 3rd column. 	*/
    /* Alternately, you could restate the entire	*/
    /* subselect in the orderby clause, but I think */
    /* that's too clunky				*/
    ORDER BY 3 DESC
    GO
    /* Drop the temp table */
    DROP TABLE #temp
    Last edited by Teddy; 07-15-04 at 11:24.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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