Querys returning different data

[ShaunG]

I've managed to get the same results from my tables to my querys but my form is not showing all the fields, if I could grab some advise as to where I'm going wrong would be great.

the sql I have for the query is -

SELECT tblJobDetails.*, tblSiteSupervisor.SupervisorID_PK, tblSiteSupervisor.FirstName, tblSiteSupervisor.LastName, tblSiteSupervisor.ContactNumber, tblBuilders.BuilderID_PK, 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;

all the jobdetails are showing on the form but the builder and supervisor details are not.



would it have to do with the sql code as to why some fields are not showing up on the form or is it the way I have the form set up?

Im trying to google research sql to understand it but still not quite got my head around the language as yet.

[aytee111]

Without knowing anything about your form, it is hard to answer. Is it a display only form where you are displaying all the data from the query? That would be a simple form which the wizard could create for you. If, however, it is a data entry form with Job Details table as the record source, then the builder and the supervisor would be comboboxes based on the relevant tables - select builder, cascade combobox to show supervisors belonging to that builder - voila, there will be the information.

[ShaunG]

Without knowing anything about your form, it is hard to answer. Is it a display only form where you are displaying all the data from the query? That would be a simple form which the wizard could create for you. If, however, it is a data entry form with Job Details table as the record source, then the builder and the supervisor would be comboboxes based on the relevant tables - select builder, cascade combobox to show supervisors belonging to that builder - voila, there will be the information.

Yeah I can now see the question was a bit vague.... i dont quite know how i was getting different data from table to query but I realised I had a few issues, my relationships werent setup right also my form controls weren’t set up correct.

Had to separate my builder table 1 to many to jobdetails and then supervisors 1 to many to jobdetails instead of

builders 1 to many to supervisors and supervisors 1 to many to jobdetails

i found this worked better as supervisors my change builders in the future but its still good to know the supervisor that worked on old jobs just incase ...

without changing the relationship when I change the builder the supervisor worked for it changed all old finished jobs...
Now I can change it if needed and doesnt affect any other job... there May be a better way that but i found this worked.

the querys worked fine with this as well!