Adding a child Table

desireemm · #1 (**permalink**) 01-28-12, 21:19

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)

Wim · #2 (**permalink**) 01-29-12, 03:18

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.

Pat Phelan · #3 (**permalink**) 01-29-12, 11:22

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

desireemm · #4 (**permalink**) 01-29-12, 17:10

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

desireemm · #5 (**permalink**) 01-29-12, 17:11

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

desireemm · #6 (**permalink**) 01-29-12, 17:19

Thank you Pat which number did you call???

Quote:

Originally Posted by Pat Phelan

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

Internet Services

Web Development

Digital Marketing

Consumer Tech