Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Adding a child Table

    Hello I need to add a child table that will tell us who the pariticpants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tble and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesnt show the present counselor always shows the old counselor can anyone tell me what I did wrong??

    Code:
    SELECT     Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName, 
                          People_tbl.LastName, People_tbl.[Parent ID]
    FROM         People_tbl RIGHT OUTER JOIN
                          Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
    WHERE     (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you post the CREATE TABLE scripts of the involved tables, provide some example data, the result you want and the wrong result you get?

    You talk about the "Counselor_tbl" but that table is not used in the query.

    "WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)": there can be a lot of John Smith's in your database. Using the first and last name of a person is not a solid way to select a person.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hey Desiree, it is good to see you again!

    The SQL statement that you sent doesn't seem to match with the question that you sent, so I'm a bit corn-fused.

    Please check to be sure that this is the SQL you meant, and if it is then I need some help understanding what the question is!

    BTW, happy belated birthday too! I tried to call, but you must have been out partying! Good for you!!!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking

    Patttttt how are you????? Long time no seeee

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    SORRY about that. I found out what I did wrong now it shows the current counselor here is my SQL statement

    Code:
    SELECT DISTINCT 
                          People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea, 
                          People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.ScairHoursOnly, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, 
                          TanfActivity_tbl.EventDate, Counselors_tbl.CounselorsName, Counselors_tbl.[Parent ID] AS Expr1, Counselors_tbl.Counselor, 
                          Counselors_tbl.CounselorID
    FROM         People_tbl INNER JOIN
                          TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
                          Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
    WHERE     (People_tbl.TANF = N'Yes') AND (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate)
    ORDER BY People_tbl.ServiceArea, People_tbl.LastName

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank you Pat which number did you call???

    Quote Originally Posted by Pat Phelan View Post
    Hey Desiree, it is good to see you again!

    The SQL statement that you sent doesn't seem to match with the question that you sent, so I'm a bit corn-fused.

    Please check to be sure that this is the SQL you meant, and if it is then I need some help understanding what the question is!

    BTW, happy belated birthday too! I tried to call, but you must have been out partying! Good for you!!!

    -PatP

Posting Permissions

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