Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: Ranking and Crosstab query

    Good morning, I found the following query's by goggling, I changed the table name and field names to fit my use. The query's work well but for one problem, the ranking query ranks the column heading by alphabet not position in the table. Like so
    Code:
    ID    F
    1     x
    1     a
    1     b
    2     b
    2     x
    3     a
    3     x
    3     b
    
    Becomes
    
    ID   F1   F2   F3
    1     a    b     x
    2     b    x
    3     a    b     x
    Is there a way to get the ranking query to rank by position in table. Thanks for any help

  2. #2
    Join Date
    Apr 2009
    Posts
    85
    I actuality forgot to post the query's

    Ranking query [qgrpInsRanking]:
    SELECT tblInsurance.ID, tblInsurance.Insurance, tblInsurance.InsuredName, Count([tblInsurance]![ID]) AS ColHead
    FROM tblInsurance INNER JOIN tblInsurance AS tblInsurance_1 ON (tblInsurance.InsuredName = tblInsurance_1.InsuredName) AND (tblInsurance.ID = tblInsurance_1.ID)
    WHERE (((tblInsurance.Insurance)>=[tblInsurance_1]![Insurance]))
    GROUP BY tblInsurance.ID, tblInsurance.Insurance, tblInsurance.InsuredName;
    Crosstab query
    TRANSFORM First(qgrpInsRanking.Insurance) AS FirstOfInsurance
    SELECT qgrpInsRanking.ID, qgrpInsRanking.InsuredName
    FROM qgrpInsRanking
    GROUP BY qgrpInsRanking.ID, qgrpInsRanking.InsuredName
    PIVOT "Ins" & [ColHead];

Posting Permissions

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