Not enough data being found

[JR2913]

This is driving me mad! I have a list box on a form that should provide a complete list of services that an individual is involved in providing. The SQL used is:

SELECT tblServices.[service start], tblServices.[service name], tblServiceLocation.[Location Name], tblServiceRoom.Room, tblServices.[time of start]
FROM (tblServices INNER JOIN tblServiceRoom ON tblServices.Room = tblServiceRoom.RoomID) INNER JOIN tblServiceLocation ON tblServices.Location = tblServiceLocation.LocationID
WHERE (((tblServices.Practitioner1)=[Forms]![frmPractitioners]![PracID])) OR (((tblServices.Practitioner2)=[Forms]![frmPractitioners]![PracID])) OR (((tblServices.Practitioner3)=[Forms]![frmPractitioners]![PracID]))
ORDER BY tblServices.[service name];


The resulting list should include all of the services provided by the practitioner whose details are on screen (identified via a text box called PracID) but an incomplete list is displayed - e.g. 4 out of 7 services for one individual and 4 out of 8 for another. Practitioners are identified by their ID, which can be in one of 3 fields (Practitioner1, Practitioner2 or Practitioner3) in the table being searched. The Inner Joins aren't relevant to the problem, I don't think, as these are just identifying other objects by name.

If anyone out there can spot why I'm not getting a full list of results, I would be most grateful for some advice!

 

[annsolomon]

Are you SURE your joins aren't relevant? Have you TRIED using LEFT joins instead? Ann

 

[Zathras]

I think Ann is correct. Try this version of the query without the joins to see if it retrieves all of the records:

SELECT tblServices.[service start], tblServices.[service name], tblServices.[Location], tblServices.Room, tblServices.[time of start]
FROM tblServices
WHERE (((tblServices.Practitioner1)=[Forms]![frmPractitioners]![PracID])) OR (((tblServices.Practitioner2)=[Forms]![frmPractitioners]![PracID])) OR (((tblServices.Practitioner3)=[Forms]![frmPractitioners]![PracID]))
ORDER BY tblServices.[service name];

It would seem that tblServices has either a Room that is not in tblServiceRoom or a Location that is not in tblServiceLocation.

 

[JR2913]

Well guys, you were right - there was some duff data in the two tables and the query wasn't picking those records out. Corrected the data and it works fine now.

Thanks for the help.