SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict singersower's Avatar
    Join Date
    Nov 2004
    Location
    TX
    Posts
    240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question joined tables with duplicate column names

    I have 2 joined tables with several duplicate column names and I am very confused as to how to "rename" them and then do my query.
    In the generated Excel report I need to reference both at different times. Here is the Query:
    Code:
    "SELECT R.VolNewCompleted " _
        & " , R.VolParticipation"_
        & " , R.VolClassesOffered"_
        & ", T.StaffFall"_
        & ", T.StaffSpring"_
        & ", T.StaffComet"_
        & " , T.StaffSeed"_
        & " , T.StaffCaseSuper"_
        & " , T.StaffAnnual"_
        & ", T.StaffNational"_
        & " , T.StaffTrainer"_
        & " , T.StaffAdvManage"_
        & ", T.StaffDiversity"_
        & ", T.StaffOther1"_
        & ", T.StaffOther2"_
        & ", T.BoardOrientCnt"_
        & ", T.BoardOrientHrs"_
        & ", T.BoardTrainCnt"_
        & ", T.BoardTrainHrs"_
        & ", T.BoardPlanCnt"_
        & ", T.BoardPlanHrs"_
         & ", T.BoardEvalCnt"_
    	 & ", R.Quarter "_ 
      & "FROM StatsTraining as T "_
      & "INNER JOIN "_
      & "StatsRetention as R "_
      & "ON R.OfficeID = T.OfficeID "_
      & " WHERE (T.OfficeID=" & strOfficeID & ") AND (R.FiscalYear=" & strFiscalYear & ")"
    Right now, with this code, it only tells it to identify the FiscalYear from one of the tables and so my reports are not accurate. How to I either split it into 2 queries or denote that the query needs to associate the R.FiscalYear and/or T.Fiscal year with their respective correlating records?

    The duplicate columns (fields) in both tables are: FiscalYear, Quarter, VolNewCompleted, VolParticipation, VolClassesOffered.


    Thanx so much in advance, I am really having a hard time sorting this one out.
    Singersower
    HopeSpring Design

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, could we stick to plain unadorned sql syntax in this forum? that perl code makes my eyes water

    to differentiate identically-named colulmns from separate tables in the same query, use column aliases
    Code:
    SELECT R.VolNewCompleted       as Retention_VolNewCompleted
         , R.VolParticipation      as Retention_VolParticipation
         , R.VolClassesOffered     as Retention_VolClassesOffered
         , T.VolNewCompleted       as Training_VolNewCompleted
         , T.VolParticipation      as Training_VolParticipation
         , T.VolClassesOffered     as Training_VolClassesOffered
         , T.StaffFall
         , T.StaffSpring
         , T.StaffComet
         , T.StaffSeed
         , T.StaffCaseSuper
         , T.StaffAnnual
         , T.StaffNational
         , T.StaffTrainer
         , T.StaffAdvManage
         , T.StaffDiversity
         , T.StaffOther1
         , T.StaffOther2
         , T.BoardOrientCnt
         , T.BoardOrientHrs
         , T.BoardTrainCnt
         , T.BoardTrainHrs
         , T.BoardPlanCnt
         , T.BoardPlanHrs
         , T.BoardEvalCnt
         , R.Quarter               as Retention_Quarter  
      FROM StatsTraining as T 
    INNER 
      JOIN StatsRetention as R 
        ON R.OfficeID = T.OfficeID 
     WHERE T.OfficeID = strOfficeID
       AND R.FiscalYear = strFiscalYear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict singersower's Avatar
    Join Date
    Nov 2004
    Location
    TX
    Posts
    240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question okay

    OhB KayB...

    So, we can declare the fields to be renamed and identify them as such. (Plus one more)

    Code:
    SELECT R.VolNewCompleted       as Retention_VolNewCompleted
         , R.VolParticipation      as Retention_VolParticipation
         , R.VolClassesOffered     as Retention_VolClassesOffered
         , T.VolNewCompleted       as Training_VolNewCompleted
         , T.VolParticipation      as Training_VolParticipation
         , T.VolClassesOffered     as Training_VolClassesOffered
         , T.StaffFall
         , T.StaffSpring
         , T.StaffComet
         , T.StaffSeed
         , T.StaffCaseSuper
         , T.StaffAnnual
         , T.StaffNational
         , T.StaffTrainer
         , T.StaffAdvManage
         , T.StaffDiversity
         , T.StaffOther1
         , T.StaffOther2
         , T.BoardOrientCnt
         , T.BoardOrientHrs
         , T.BoardTrainCnt
         , T.BoardTrainHrs
         , T.BoardPlanCnt
         , T.BoardPlanHrs
         , T.BoardEvalCnt
         , R.Quarter               as Retention_Quarter  
         , T.Quarter               as Training_Quarter
      FROM StatsTraining as T 
    INNER 
      JOIN StatsRetention as R 
        ON R.OfficeID = T.OfficeID 
     WHERE T.OfficeID = strOfficeID
       AND R.FiscalYear = strFiscalYear
    I'm not sure if at this point this question needs to go to the ASP forum....

    I then open the recordset in the usual fashion..... then:

    Code:
     intQuarter = rsStatsCase("Quarter")
    
        aTrain(intQuarter,cintVolNewCompleted) = rsStatsCase("VolNewCompleted")
        aTrain(intQuarter,cintVolParticipation) = rsStatsCase("VolParticipation")
        aTrain(intQuarter,cintVolClassesOffered) = rsStatsCase("VolClassesOffered")
        aTrain(intQuarter,cintStaffFall) = rsStatsCase("StaffFall")
        aTrain(intQuarter,cintStaffSpring) = rsStatsCase("StaffSpring")
        aTrain(intQuarter,cintStaffComet) = rsStatsCase("StaffComet")
        aTrain(intQuarter,cintStaffSeed) = rsStatsCase("StaffSeed")    
        aTrain(intQuarter,cintStaffCaseSuper) = rsStatsCase("StaffCaseSuper")    
        aTrain(intQuarter,cintStaffAnnual) = rsStatsCase("StaffAnnual")    
        aTrain(intQuarter,cintStaffNational) = rsStatsCase("StaffNational")    
        aTrain(intQuarter,cintStaffTrainer) = rsStatsCase("StaffTrainer")    
        aTrain(intQuarter,cintStaffAdvManage) = rsStatsCase("StaffAdvManage")
        aTrain(intQuarter,cintStaffDiversity) = rsStatsCase("StaffDiversity")        
        aTrain(intQuarter,cintStaffOther1) = rsStatsCase("StaffOther1")    
        aTrain(intQuarter,cintStaffOther2) = rsStatsCase("StaffOther2")    
        aTrain(intQuarter,cintBoardOrientCnt) = rsStatsCase("BoardOrientCnt")
        aTrain(intQuarter,cintBoardOrientHrs) = rsStatsCase("BoardOrientHrs")
        aTrain(intQuarter,cintBoardTrainCnt) = rsStatsCase("BoardTrainCnt")
        aTrain(intQuarter,cintBoardTrainHrs) = rsStatsCase("BoardTrainHrs")    
        aTrain(intQuarter,cintBoardPlanCnt) = rsStatsCase("BoardPlanCnt")
        aTrain(intQuarter,cintBoardPlanHrs) = rsStatsCase("BoardPlanHrs")
        aTrain(intQuarter,cintBoardEvalCnt) = rsStatsCase("BoardEvalCnt")
    
        rsStatsCase.MoveNext
      Wend
    then generate the excel report with calls:
    Code:
     Response.Write ("<tr><th colspan=""5"">Staff Training</th></tr>")
      Call CreateRow("Fall Staff Retreat",aTrain,cintStaffFall)
      Call CreateRow("Spring Directors Retreat",aTrain,cintStaffSpring) 
      ............
    How do I make sure that the strings like "cintStaffFall" get pulled from the correct reference to the tables?
    Singersower
    HopeSpring Design

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    moving to asp forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you use the syntax
    SELECT someField AS myTable_someField

    then you have to refer to the field in your recordset as
    myRS("myTable_someField")
    and not
    myRS("someField")

  6. #6
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Jim is correct. In your query reference it as the original name ,but one the value is pulled back the name has been changed so you would reference it with its new name.

    Make sense?


Bookmarks

Posting Permissions

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