Inter Join with no resulting records

  • Thread starter Thread starter kevinjwilson
  • Start date Start date
K

kevinjwilson

Please see the two sql statments below. The first generates resulting
records, the second adds a criteria but returns no records. Reviewing
the original record set, it does contain data that meets the criteria.
The field in question is a yes/no field in access. Please provide
guidance regarding the use of the inter join and criteria in a query.
It does not seem to be working for me.

Thanks

SELECT dbo_tblStudy.Creator, dbo_tblStudy.CreationTime, [Tbl_PSG
QA].[Titration?], [Tbl_PSG QA].[Reviewed by], [Tbl_PSG QA].[Time btn
pressure Inc], [Tbl_PSG QA].[Approp pressure change], [Tbl_PSG
QA].[overall qualtiy of titration], [Tbl_PSG QA].EEG, [Tbl_PSG QA].EOG,
[Tbl_PSG QA].EKG, [Tbl_PSG QA].[EMG-chin], [Tbl_PSG QA].[EMG-Legs],
[Tbl_PSG QA].[EMG-IC], [Tbl_PSG QA].[Nasal Flow], [Tbl_PSG QA].[CPAP
Flow], [Tbl_PSG QA].[R Effort (RC)], [Tbl_PSG QA].[R Effort (AB)],
[Tbl_PSG QA].[CPAP Pressure], [Tbl_PSG QA].SaO2, [Tbl_PSG
QA].[Artifacts Recognized], [Tbl_PSG QA].Comments, [Tbl_PSG
QA].Artifact_Popping, [Tbl_PSG QA].Artifact_Repiratory, [Tbl_PSG
QA].Artifact_Sweat, [Tbl_PSG QA].[Artifact_EKG in EEG], [Tbl_PSG
QA].[Artifact_EKG in EOG], [Tbl_PSG QA].[Artifact_EMG in EEG]
FROM [Tbl_PSG QA] INNER JOIN dbo_tblStudy ON [Tbl_PSG QA].[Study GUID]
= dbo_tblStudy.StudyGUID;

SELECT dbo_tblStudy.Creator, dbo_tblStudy.CreationTime, [Tbl_PSG
QA].[Titration?], [Tbl_PSG QA].[Reviewed by], [Tbl_PSG QA].[Time btn
pressure Inc], [Tbl_PSG QA].[Approp pressure change], [Tbl_PSG
QA].[overall qualtiy of titration], [Tbl_PSG QA].EEG, [Tbl_PSG QA].EOG,
[Tbl_PSG QA].EKG, [Tbl_PSG QA].[EMG-chin], [Tbl_PSG QA].[EMG-Legs],
[Tbl_PSG QA].[EMG-IC], [Tbl_PSG QA].[Nasal Flow], [Tbl_PSG QA].[CPAP
Flow], [Tbl_PSG QA].[R Effort (RC)], [Tbl_PSG QA].[R Effort (AB)],
[Tbl_PSG QA].[CPAP Pressure], [Tbl_PSG QA].SaO2, [Tbl_PSG
QA].[Artifacts Recognized], [Tbl_PSG QA].Comments, [Tbl_PSG
QA].Artifact_Popping, [Tbl_PSG QA].Artifact_Repiratory, [Tbl_PSG
QA].Artifact_Sweat, [Tbl_PSG QA].[Artifact_EKG in EEG], [Tbl_PSG
QA].[Artifact_EKG in EOG], [Tbl_PSG QA].[Artifact_EMG in EEG]
FROM [Tbl_PSG QA] INNER JOIN dbo_tblStudy ON [Tbl_PSG QA].[Study GUID]
= dbo_tblStudy.StudyGUID
WHERE ((([Tbl_PSG QA].[Titration?])=-1));
 
The only difference between the 2 SQL statements is the addition of the
WHERE clause, I take it? If there are records where the yes/no field
[Titration?] in table [Tbl_PSG QA] is True (checked), the 2nd query should
return those records.

The field in the JOIN contains GUID in its name. If that hints at the data
type, it might be worth reading what michka has to say about the problems
JET has in treating GUIDs consistently:
http://www.trigeminal.com/usenet/usenet011.asp?1033

You might also try True instead of -1 in the WHERE clause. Some databases
use 1 for True, and your dbo prefix hints that we might be talking about an
attached table from another data source.

Access does have problems with yes/no fields in outer joins (since JET can't
handle the resultant nulls), but that does not affect the inner joins unless
[Tbl_PSG QA] is also an attached table from a data source that does handle
nulls in yes/no fields, or there is an intermediate query/view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Please see the two sql statments below. The first generates resulting
records, the second adds a criteria but returns no records. Reviewing
the original record set, it does contain data that meets the criteria.
The field in question is a yes/no field in access. Please provide
guidance regarding the use of the inter join and criteria in a query.
It does not seem to be working for me.

Thanks

SELECT dbo_tblStudy.Creator, dbo_tblStudy.CreationTime, [Tbl_PSG
QA].[Titration?], [Tbl_PSG QA].[Reviewed by], [Tbl_PSG QA].[Time btn
pressure Inc], [Tbl_PSG QA].[Approp pressure change], [Tbl_PSG
QA].[overall qualtiy of titration], [Tbl_PSG QA].EEG, [Tbl_PSG QA].EOG,
[Tbl_PSG QA].EKG, [Tbl_PSG QA].[EMG-chin], [Tbl_PSG QA].[EMG-Legs],
[Tbl_PSG QA].[EMG-IC], [Tbl_PSG QA].[Nasal Flow], [Tbl_PSG QA].[CPAP
Flow], [Tbl_PSG QA].[R Effort (RC)], [Tbl_PSG QA].[R Effort (AB)],
[Tbl_PSG QA].[CPAP Pressure], [Tbl_PSG QA].SaO2, [Tbl_PSG
QA].[Artifacts Recognized], [Tbl_PSG QA].Comments, [Tbl_PSG
QA].Artifact_Popping, [Tbl_PSG QA].Artifact_Repiratory, [Tbl_PSG
QA].Artifact_Sweat, [Tbl_PSG QA].[Artifact_EKG in EEG], [Tbl_PSG
QA].[Artifact_EKG in EOG], [Tbl_PSG QA].[Artifact_EMG in EEG]
FROM [Tbl_PSG QA] INNER JOIN dbo_tblStudy ON [Tbl_PSG QA].[Study GUID]
= dbo_tblStudy.StudyGUID;

SELECT dbo_tblStudy.Creator, dbo_tblStudy.CreationTime, [Tbl_PSG
QA].[Titration?], [Tbl_PSG QA].[Reviewed by], [Tbl_PSG QA].[Time btn
pressure Inc], [Tbl_PSG QA].[Approp pressure change], [Tbl_PSG
QA].[overall qualtiy of titration], [Tbl_PSG QA].EEG, [Tbl_PSG QA].EOG,
[Tbl_PSG QA].EKG, [Tbl_PSG QA].[EMG-chin], [Tbl_PSG QA].[EMG-Legs],
[Tbl_PSG QA].[EMG-IC], [Tbl_PSG QA].[Nasal Flow], [Tbl_PSG QA].[CPAP
Flow], [Tbl_PSG QA].[R Effort (RC)], [Tbl_PSG QA].[R Effort (AB)],
[Tbl_PSG QA].[CPAP Pressure], [Tbl_PSG QA].SaO2, [Tbl_PSG
QA].[Artifacts Recognized], [Tbl_PSG QA].Comments, [Tbl_PSG
QA].Artifact_Popping, [Tbl_PSG QA].Artifact_Repiratory, [Tbl_PSG
QA].Artifact_Sweat, [Tbl_PSG QA].[Artifact_EKG in EEG], [Tbl_PSG
QA].[Artifact_EKG in EOG], [Tbl_PSG QA].[Artifact_EMG in EEG]
FROM [Tbl_PSG QA] INNER JOIN dbo_tblStudy ON [Tbl_PSG QA].[Study GUID]
= dbo_tblStudy.StudyGUID
WHERE ((([Tbl_PSG QA].[Titration?])=-1));
Click to expand...
 
Back
Top