Problem with select query

[UHNSTrust]

I have a strange problem where I have an SQL query which runs great until I add one particular field.

The SQL that works is

SELECT tblCluster.ClusterDesc, tblTeam.TeamDesc, tblProfRole.ProfRoleDesc, tblProfRole.ClinicalLevel, tblPatient.PatientID, Count(tblLTC.LTCDesc) AS CountOfLTCDesc
FROM (((((tblCluster INNER JOIN tblTeam ON tblCluster.ClusterID = tblTeam.ClusterID) LEFT JOIN tblClinician ON tblTeam.TeamID = tblClinician.TeamID) LEFT JOIN tblPatient ON tblClinician.ClinicianID = tblPatient.ClinicianID) LEFT JOIN tblPatientLTC ON tblPatient.PatientID = tblPatientLTC.PatientID) LEFT JOIN tblProfRole ON tblClinician.ProfessionalRoleID = tblProfRole.ProfRoleID) LEFT JOIN tblLTC ON tblPatientLTC.LTCID = tblLTC.LTCID
GROUP BY tblCluster.ClusterDesc, tblTeam.TeamDesc, tblProfRole.ProfRoleDesc, tblProfRole.ClinicalLevel, tblPatient.PatientID

When I then add a field called Discharge from tblPatient (with a 'group by' in the totals) I get 'No current record' returned.

If I remove the 'totals' grouping then the query runs and returns records.

If I turn the LEFT JOIN to an INNER JOIN between tblTeam and tblClinician it will work.

I am totally puzzled. The data is on a linked SQL table. The field Discharge is a bit field. I have tried making all the tables local tables (exported from SQL server via DTS) and this works.

Does anybody have any idea why I am having problems? It all seems odd that changing all different things will make a difference.

Thanks in advance.

Jonathan

 

[dhookom]

I think the issue is that your field is bit. Try change to an expression like:
Disch:IIf([Discharge]=0,0,1)
See if you can group on this expression.

Duane MS Access MVP

 

[UHNSTrust]

Hi Duane,

Thanks for this. Will try when I am back in the office in the morning.

Jonathan

 

[UHNSTrust]

Thanks Duane,

Works!!

Jonathan