Sub-query no results

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am trying to create a sub-query from which to base a report. I want
the sub-query
to show items from a table that are not in a separate query. When I
run the sub-query
I get no results but I have checked the data and I should be....could
someone tell
me what I might be missing? Below is the SQL statement from the query:

SELECT AShift.AShiftIDNumber, AShift.EntryDate, AShift.Panel,
AShift.Rank, AShift.Concern, AShift.Comments
FROM AShift
WHERE (((Exists (Select ConcernCompareqry.AShiftIDNumber FROM
ConcernCompareqry WHERE ConcernCompareqry.AShiftIDNumber =
AShift.AShiftIDNumber))=False));
 
SELECT AShift.AShiftIDNumber
, AShift.EntryDate
, AShift.Panel
, AShift.Rank
, AShift.Concern
, AShift.Comments
FROM AShift
WHERE NOT Exists
(Select ConcernCompareqry.AShiftIDNumber
FROM ConcernCompareqry
WHERE ConcernCompareqry.AShiftIDNumber =AShift.AShiftIDNumber)

Personnally I would probably use a frustrated outer join - it should be
faster

SELECT AShift.AShiftIDNumber
, AShift.EntryDate
, AShift.Panel, AShift.Rank, AShift.Concern
, AShift.Comments
FROM AShift LEFT JOIN ConcernCompareqry
ON AShift.AshiftIDNumber = ConcernCompareqry.AShiftIDNumber
WHERE ConcernCompareqry.AShiftIDNumber is Null

If those both run and return no records, then I would look more closely at
the data.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT AShift.AShiftIDNumber
, AShift.EntryDate
, AShift.Panel
, AShift.Rank
, AShift.Concern
, AShift.Comments
FROM AShift
WHERE NOT Exists
(Select ConcernCompareqry.AShiftIDNumber
FROM ConcernCompareqry
WHERE ConcernCompareqry.AShiftIDNumber =AShift.AShiftIDNumber)

Personnally I would probably use a frustrated outer join - it should be
faster

SELECT AShift.AShiftIDNumber
, AShift.EntryDate
, AShift.Panel, AShift.Rank, AShift.Concern
, AShift.Comments
FROM AShift LEFT JOIN ConcernCompareqry
ON AShift.AshiftIDNumber = ConcernCompareqry.AShiftIDNumber
WHERE ConcernCompareqry.AShiftIDNumber is Null

If those both run and return no records, then I would look more closely at
the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.







- Show quoted text -
Click to expand...

Thank you John. The first SQL statement you wrote is what I
originally wrote, its just after you close the query and
look at the SQL statement again, its converts to what I copied in my
original post.

However, your outer join worked perfectly. Thank you!
 
Back
Top