Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab or not to crosstab (A2K)

    Is there a way to convert the top table in the image to the one on the bottom. Crosstab says you need more than 2 columns when I try to use that method.
    I don't necessarily need it in a table if I can just display it in a query.
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab or not to crosstab (A2K)

    The bottom image doesn't correspond to something meaningful in Access. Queries have rows and columns (just like tables); a single row in a query represents data that belong together. The data in a row in your bottom image have nothing to do with each other. It might be possible to obtain it as a result of an extremely artificial method, but I don't know how to do that at the moment.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab or not to crosstab (A2K)

    You could force display of the data in the method you want if you had a separate query for each category and built a report for each query displaying only the models pertaining to that category. Next create a report containing each of the category reports as a subreport placing them side by side and inserting the title of each category above each subreport..
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab or not to crosstab (A2K)

    Thanks Hans-- I see your point.
    Judy,
    That may work for what I want. I'll give it a try

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab or not to crosstab (A2K)

    My solution:

    A query to sort the data by category, model and add a "Sort order" field,

    SELECT Test.Category, Test.Model, 1 AS [Sort Order] INTO [Test 2]
    FROM Test
    GROUP BY Test.Category, Test.Model, 1
    ORDER BY Test.Category, Test.Model;


    A macro to create sort orders by category:

    Function Index_test_2()
    Dim db As Database
    Dim rs As Recordset
    Dim count_it As Integer
    Dim Current_Category As String

    Current_Category = ""
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Test 2")

    If rs.recordcount > 0 Then
    rs.MoveFirst
    While Not rs.EOF
    If Current_Category <> rs![Category] Then
    count_it = 0
    Current_Category = rs![Category]
    End If
    count_it = count_it + 1
    rs.Edit
    rs![Sort Order] = count_it
    rs.Update
    rs.MoveNext
    Wend
    End If

    End Function


    Then crosstab the result on the sort order:

    TRANSFORM Max([Test 2].Model) AS [The Value]
    SELECT [Test 2].[Sort Order]
    FROM [Test 2]
    GROUP BY [Test 2].[Sort Order]
    PIVOT [Test 2].Category;

    Not perfect, but it works. This is Access97 code and queries.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab or not to crosstab (A2K)

    JDN,
    Clever and works perfectly
    Thanks

Posting Permissions

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