SQL Specific Queries (MINUS or JOIN with <>)

[Guest]

I have two tables, a personnel table that list all the people that work in my
group, and an unavailable table that lists when personnel are away from the
office. The unavailable table uses the personnel ID field and has a start
time and end time to when they will be unavailable. I need to populate a
combo box with the names of people who are available for watch. I tried to
run a MINUS query in ACCESS 2000 like below,

SELECT [Personnel].[ID]
FROM Personnel
WHERE Personnel.CDO=True
MINUS
SELECT Unavailability.ID
FROM Unavailability
WHERE (Unavailability.[Start Date]<=[Date]) AND (Unavailability.[End
Date]>=[Date]);

But I keep getting a syntax error. I cannot find the error. I also tried
running a join that discards the ID’s I do not need like below,

SELECT Personnel.ID, Personnel.RANK, Personnel.NAME
FROM Personnel LEFT JOIN [Unavailable List by date] ON Personnel.ID <>
[Unavailable List by date].ID
WHERE (((Personnel.CDO)=True))
ORDER BY Personnel.GRADE DESC , Personnel.NAME;

Where the Unavailable List by date query lists the people not available on a
particular date, but I get garbled results. Specifically, I get the names of
everyone in the personnel table listed 5 times.
Help. How do I create a query that returns results that do not include
people that are not available?

 

[Guest]

Nice to be able to answer my own question.

First of all, Access does not support the minus query. Why the <> Join did
not work I still don't know, but if anyone has an answer to that I would
appreciate it.

To achieve my purpose, I used a Not In statement to exclude the Personnel
IDs that I did not want to show up in the COMBO BOX. SQL is below.

SELECT Personnel.ID, Personnel.RANK, Personnel.NAME
FROM Personnel
WHERE (((Personnel.ID) Not In (SELECT Personnel.ID FROM Personnel LEFT JOIN
Unavailability ON Personnel.ID = Unavailability.ID WHERE
(((Personnel.CDO)=True) AND ((Unavailability.[Start Date])<=[Date]) AND
((Unavailability.[End Date])>=[Date])) )) AND ((Personnel.CDO)=True))
ORDER BY Personnel.GRADE DESC , Personnel.NAME;

 

[John Vinson]

SELECT [Personnel].[ID]
FROM Personnel
WHERE Personnel.CDO=True
MINUS
SELECT Unavailability.ID
FROM Unavailability
WHERE (Unavailability.[Start Date]<=[Date]) AND (Unavailability.[End
Date]>=[Date]);

But I keep getting a syntax error. I cannot find the error. I also tried
running a join that discards the ID’s I do not need like below,

The MINUS operator is not supported in the Access/JET dialect of SQL.
It's in SQL/Server, but is only available using passthrough queries
from Access.

SELECT Personnel.ID, Personnel.RANK, Personnel.NAME
FROM Personnel LEFT JOIN [Unavailable List by date] ON Personnel.ID <>
[Unavailable List by date].ID
WHERE (((Personnel.CDO)=True))
ORDER BY Personnel.GRADE DESC , Personnel.NAME;

Where the Unavailable List by date query lists the people not available on a
particular date, but I get garbled results. Specifically, I get the names of
everyone in the personnel table listed 5 times.
Help. How do I create a query that returns results that do not include
people that are not available?

A "Frustrated Outer Join" query will do the trick here:

SELECT Personnel.ID, Personnel.RANK, Personnel.NAME
FROM Personnel LEFT JOIN [Unavailable List by date] ON Personnel.ID =
[Unavailable List by date].ID
WHERE Personnel.CDO AND [Unavailable list by date].ID IS NULL
ORDER BY Personnel.GRADE DESC , Personnel.NAME;

The LEFT JOIN returns all records in Personnel and matching records
from the unavailable list, with NULL values where no record exists;
using a criterion of NULL on the ID limits retrieval to those records
which do not have a match.

John W. Vinson[MVP]