Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Sorting (Access 2003)

    I have created a Crosstab query from an ther query. Works fine except I can't get the column headings to display in the order I need..

    History:
    This is in a Training Database and the user needs an accounting of all new employees and the courses they have taken.

    They select there courses from a list, then set the order they want to see them.
    This creates a user table with the following:

    Best_Title number
    Best_Type number
    Best_Order number

    <pre>Query that feeds CrossTab

    SELECT tbl_User_Best_Title_Type.Best_Order <---- user's sort order.
    , tbl_User_Best_Title_Type.Best_Title
    , tbl_User_Best_Title_Type.Best_Type
    , tbl_Course_Titles.CourseTitle <---- col. hdngs
    , tbl_Course_Type.CT_CourseType
    , tbl_Employee_List.EL_LastName
    , tbl_Employee_List.EL_FirstName
    , tbl_Attendance_List.Attended
    FROM ((tbl_Course_Type
    INNER JOIN tbl_User_Best_Title_Type
    ON tbl_Course_Type.CT_Id =
    tbl_User_Best_Title_Type.Best_Type)
    INNER JOIN ((tbl_Course_List
    INNER JOIN tbl_Attendance_List
    ON tbl_Course_List.Course_Id =
    tbl_Attendance_List.Course_Id)
    INNER JOIN tbl_Employee_List
    ON tbl_Attendance_List.UserId = tbl_Employee_List.EL_Id)
    ON (tbl__User_Best_Title_Type.Best_Type
    = tbl_Course_List.CourseType)
    AND (tbl_User_Best_Title_Type.Best_Title
    = tbl_Course_List.Course_No))
    INNER JOIN tbl_Course_Titles
    ON tbl_User_Best_Title_Type.Best_Title =
    tbl_Course_Titles.Course_No
    WHERE (((tbl_Attendance_List.Attended)=True)
    AND ((tbl_Employee_List.EL_New_EMPL_Badge)=True))
    ORDER BY tbl_User_Best_Title_Type.Best_Order;

    .
    CrossTab Query

    TRANSFORM Count(Qy_Best_Accum_4_CrossTab.Best_Type) AS CountOfBest_Type
    SELECT Qy_Best_Accum_4_CrossTab.EL_LastName
    , Qy_Best_Accum_4_CrossTab.EL_FirstName
    , Sum(Qy_Best_Accum_4_CrossTab.Best_Type) AS [Total Of Best_Type]
    FROM Qy_Best_Accum_4_CrossTab
    GROUP BY Qy_Best_Accum_4_CrossTab.EL_LastName
    , Qy_Best_Accum_4_CrossTab.EL_FirstName
    PIVOT Qy_Best_Accum_4_CrossTab.CourseTitle;text</pre>



    They want course Title as the column headings, employee names alphabetically down the side.

    The course Titles are appearing as headings but I need them to be in the sort order set by the user.

    Any help would be greatly appreciated. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Crosstab Sorting (Access 2003)

    Are the courses fixed? If so, you can specify the column headings explicitly in the Query Properties window.

    Otherwise: how/where will the query be used?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Sorting (Access 2003)

    Hans,
    I'm not sure what you mean by fixed.
    Here is what I have.
    Employee table
    Course_List - contains all the courses given - one entry for each time the course is given
    Course_Title - List of all the Courses that are /have been offered
    Course_Type - The grouping for courses (i.e. Type MS/Office would then have Access, Excel, Word, Outlook, etc.)
    Attendee_List - Emp-id, Course_id, registered, Attended

    I have a form with a listbox containing all the Titles and Types given. This is multiselect.
    The user selects the courses from the list that they need to report on.
    I build a table with their selection from the listbox the contains Type id, Title id, and a sort field (default to 100).
    They then get a form with the new table as the source to set the sort order they want the Titles to appear.
    I then run the CrossTab query I describe above.

    There is a save method so in 2 months when they have offered more courses they can retrieve their selection and then add more titles, change the sort order and run the Query. They then send this to Excel for heading formating.

    There can be from 1 to 60 courses (they claim 60 will be the max ,but there are over 300 courses offered.)
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Crosstab Sorting (Access 2003)

    A crosstab query has a property Column Headings. If this is not blank, the crosstab query will display the column headers as specified, in the order specified. In SQL it takes the form

    ... PIVOT Qy_Best_Accum_4_CrossTab.CourseTitle In ("Maths","Geography","French","History")

    You can assemble the In (...) part in code, then modify the SQL for the query.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Sorting (Access 2003)

    Hans,
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>, <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>, <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Exactly what I needed.

    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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