Results 1 to 3 of 3

Thread: SQL Statement

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2003
    Location
    India
    Posts
    318

    SQL Statement

    Hi,

    Please can anybody tell me what is wrong with this SQL Statement?


    Code:
    strsql = "TRANSFORM Max(Availability.Avail) AS MaxOfAvail " & _
            "SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffMaster.Discipline, " & _
            "StaffMaster.Designation FROM (StaffMaster LEFT JOIN Availability ON " & _
            "StaffMaster.EmpCode = Availability.EmpCode WHERE (StaffMaster.Designation<>'Project Manager' " & _
            "AND StaffMaster.Designation NOT LIKE 'Head%' AND StaffMaster.Designation NOT LIKE " & _
            "'%Manager%')) LEFT JOIN Designation ON StaffMaster.Discipline = Designation.Discipline " & _
            "GROUP BY StaffMaster.Designation ORDER BY StaffMaster.Discipline, Designation.Priority, " & _
            "StaffMaster.EmpName PIVOT Availability.Month_Year"
    Thanks

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: SQL Statement

    Is that MS SQL 2005 syntax?

    I've not yet used TRANSFORM but posted an example of PIVOT in the sticky at the top of the DB section of the forum here.

    http://www.vbforums.com/showpost.php...67&postcount=4

    What error are you getting? What is not working?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2003
    Location
    India
    Posts
    318

    Re: SQL Statement

    My database is Access.

    I had been using the same SQL for some time. But now that I changed the table structure, I had to change the SQL too. My intention is to get the staff listed in the report according to thier priority of designation which is also a field of the table StaffMaster. In order to get the priority, I added numbers to the beginning of the designation. But later I realised that this is not flexible and so I created a different table called designation that has 3 fields, like, Designation, Priority and Discipline. So I changed the SQL to sort it according to the Priority for each Discipline. So that's the whole story.

    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
  •  



Click Here to Expand Forum to Full Width