adding a Child Table

Question

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??

 

 

SELECT DISTINCT 
                      People_tbl.FirstName, People_tbl.LastName, TanfActivity_tbl.Catagoryforhours, TanfActivity_tbl.Earnedhours, TanfActivity_tbl.ServicesCovered, 
                      People_tbl.[Parent ID], TanfActivity_tbl.Month, TanfActivity_tbl.Week, TanfActivity_tbl.ScairCaseWorker, People_tbl.ReferralStatus, 
                      TanfActivity_tbl.EventDate, People_tbl.ServiceArea
FROM         TanfActivity_tbl RIGHT OUTER JOIN
                      People_tbl ON TanfActivity_tbl.[Parent ID] = People_tbl.[Parent ID]
WHERE     (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) AND (TanfActivity_tbl.ServicesC

 

Answer 1

It is hard to suggest something useful without seen the table's structure + sample data + desired result

Just a guess 

SELECT DISTINCT 

                      People_tbl.FirstName, People_tbl.LastName, TanfActivity_tbl.Catagoryforhours, TanfActivity_tbl.Earnedhours, TanfActivity_tbl.ServicesCovered, 

                      People_tbl.[Parent ID], TanfActivity_tbl.Month, TanfActivity_tbl.Week, TanfActivity_tbl.ScairCaseWorker, People_tbl.ReferralStatus, 

                      TanfActivity_tbl.EventDate, People_tbl.ServiceArea

FROM         TanfActivity_tbl RIGHT OUTER JOIN

                      People_tbl ON TanfActivity_tbl.[Parent ID] = People_tbl.[Parent ID]

AND (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate) AND (TanfActivity_tbl.ServicesC)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Where's the Counsler TABLE in that query?

Answer 3

please post table structure with expected output be it a junk data.