joined tables with duplicate column names

[singersower]

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:

"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.

[r937]

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

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

[singersower]

OhB KayB...

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

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:

 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:

 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?

[r937]

moving to asp forum

[jimfraser]

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")

[Thing]

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?