Querys returning different data

[ShaunG]

My Querys are returning the wrong data, the sql and joins are the problem.

I changed from inner joins to right outer joins to begin with to retrieve data if there was null data in builder or supervisor fields, but I was sorta shooting in the dark.



ive done some reading and still not quite getting my head around the joins and how they work, if some could explain it using my sql as an example would be great, thanks.

SELECT tblBuilders.BuilderID_PK, tblBuilders.BuilderName, tblBuilders.ContactNumber, tblSiteSupervisor.SupervisorID_PK, tblSiteSupervisor.FirstName, tblSiteSupervisor.LastName, tblSiteSupervisor.ContactNumber, tblJobDetails.JobNoID_PK, tblJobDetails.SiteBusiness_Name, tblJobDetails.ClientsFirst_Name, tblJobDetails.Last_Name, tblJobDetails.Ph_No_1, tblJobDetails.Ph_No_2, tblJobDetails.StreetNum, tblJobDetails.Suburb, tblJobDetails.DateEntered, tblJobDetails.StreetName
FROM (tblBuilders RIGHT OUTER JOIN tblSiteSupervisor ON tblBuilders.BuilderID_PK = tblSiteSupervisor.BuilderID_FK) RIGHT OUTER JOIN tblJobDetails ON tblSiteSupervisor.SupervisorID_PK = tblJobDetails.SiteSupervisorID_FK
ORDER BY tblJobDetails.JobNoID_PK DESC;

[aytee111]

Not enough information - what results do you want to get from this query? All jobs? All clients? What is "wrong data"?

[ShaunG]

Not enough information - what results do you want to get from this query? All jobs? All clients? What is "wrong data"?

The data in the tables dont match the data in query...

in the table job 28 belongs to builder (A) but in the query it belongs to Builder(B)

the query is suppose to pullup up the job details with or with out builders, but if theres builders involved there details with the supervisor details pulled up as well.

[aytee111]

So - all jobs, then an outer join to supervisors table, same join from there to builders table. In the query design window, right-click on the line linking to the tables and select the option "all records from jobs and only the records from supervisors that match". Do the same for the link between supervisors and builders.

[ShaunG]

had a look and it, looks like thats already done

[aytee111]

From the SQL in your first post, it looks like you typed that in, not Access. Remove the links and relink the tables, let Access create the SQL for you.

[ShaunG]

From the SQL in your first post, it looks like you typed that in, not Access. Remove the links and relink the tables, let Access create the SQL for you.

nahh Access typed it I adjusted the type of join that's it...

Ill redo as you've said and see how I go

[ShaunG]

From the SQL in your first post, it looks like you typed that in, not Access. Remove the links and relink the tables, let Access create the SQL for you.

redone the links and changed the settings to suit my post #4 and still different details from table to query, its seems to be allocating the same builder to all the jobs when 3 different builders being used at the moment.

[aytee111]

Post the SQL.

[ShaunG]

Post the SQL.

SELECT tblJobDetails.JobNoID_PK, tblJobDetails.SiteBusiness_Name, tblJobDetails.ClientsFirst_Name, tblJobDetails.Last_Name, tblJobDetails.Ph_No_1, tblJobDetails.Ph_No_2, tblJobDetails.StreetNum, tblJobDetails.StreetName, tblJobDetails.Suburb, tblJobDetails.DateEntered, tblSiteSupervisor.FirstName, tblSiteSupervisor.LastName, tblSiteSupervisor.ContactNumber, tblBuilders.BuilderName, tblBuilders.ContactNumber
FROM (tblBuilders RIGHT JOIN tblSiteSupervisor ON tblBuilders.BuilderID_PK = tblSiteSupervisor.BuilderID_FK) RIGHT JOIN tblJobDetails ON tblSiteSupervisor.SupervisorID_PK = tblJobDetails.SiteSupervisorID_FK;

[aytee111]

This works for me, so without seeing your data I am not sure where to go from here. I suggest you do these one at a time, first create a query with all the jobs and their supervisors. When that works, create a query with supervisors and their buildings. Maybe you can track down where the problem is occurring.

[ShaunG]

This works for me, so without seeing your data I am not sure where to go from here. I suggest you do these one at a time, first create a query with all the jobs and their supervisors. When that works, create a query with supervisors and their buildings. Maybe you can track down where the problem is occurring.

yeah to easy ill create a few more querys and go from there, cheers for the help.

[ShaunG]

This works for me, so without seeing your data I am not sure where to go from here. I suggest you do these one at a time, first create a query with all the jobs and their supervisors. When that works, create a query with supervisors and their buildings. Maybe you can track down where the problem is occurring.

and works for me when I recreate it..... whats the deal does my head in. thanks again

[aytee111]

Good old MS, up to their tricks again!!

[ShaunG]

Good old MS, up to their tricks again!!

lol I stand corrected again its up shit creek... Arhh gotta love it

well that's me for tonight