Does ACCESS have the MINUS keyword

[David]

ACCESS says there is a syntax error in this statement. I am trying to
find out which records are not in the second table.

SELECT Charges.ID, Charges.Total
FROM Charges
MINUS
SELECT Invoice_Details.ChargeID AS ID, Invoice_Details.Amount As Total
FROM Invoice_Details;

Can I do this in Access? If not, is there a way to get the result
that I am looking for?

Thanks.

[John Mishefske]

David wrote:

ACCESS says there is a syntax error in this statement. I am trying to
find out which records are not in the second table.

SELECT Charges.ID, Charges.Total
FROM Charges
MINUS
SELECT Invoice_Details.ChargeID AS ID, Invoice_Details.Amount As Total
FROM Invoice_Details;

Can I do this in Access? If not, is there a way to get the result
that I am looking for?

JET SQL (the DB Engine behind Access) does not support the MINUS keyword. You might be
able to use the WHERE .. NOT IN clause:

SELECT GameDate FROM SportsEvents
WHERE GameDate NOT IN (SELECT SpecialOfferDate FROM EventPromotions)
--
'---------------
'John Mishefske
'---------------

[Douglas J. Steele]

"John Mishefske" <jm**********@SPAMyahoo.com> wrote in message
news:zy*******************@tornado.rdc-kc.rr.com...

David wrote:

ACCESS says there is a syntax error in this statement. I am trying to
find out which records are not in the second table.

SELECT Charges.ID, Charges.Total
FROM Charges
MINUS
SELECT Invoice_Details.ChargeID AS ID, Invoice_Details.Amount As Total
FROM Invoice_Details;

Can I do this in Access? If not, is there a way to get the result
that I am looking for?

JET SQL (the DB Engine behind Access) does not support the MINUS keyword.
You might be able to use the WHERE .. NOT IN clause:

SELECT GameDate FROM SportsEvents
WHERE GameDate NOT IN (SELECT SpecialOfferDate FROM EventPromotions)

Alternatively, try using a LEFT JOIN:

SELECT Charges.ID, Charges.Total
FROM Charges LEFT JOIN Invoice_Details
ON Charges.ID = Invoice_Details.ChargeID
WHERE Invoice_Details.ChargeID = Null

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[John Mishefske]

Douglas J. Steele wrote:

"John Mishefske" <jm**********@SPAMyahoo.com>

JET SQL (the DB Engine behind Access) does not support the MINUS keyword.
You might be able to use the WHERE .. NOT IN clause:

SELECT GameDate FROM SportsEvents
WHERE GameDate NOT IN (SELECT SpecialOfferDate FROM EventPromotions)

Alternatively, try using a LEFT JOIN:

SELECT Charges.ID, Charges.Total
FROM Charges LEFT JOIN Invoice_Details
ON Charges.ID = Invoice_Details.ChargeID
WHERE Invoice_Details.ChargeID = Null

Which will definitely perform better than the 'NOT IN' suggestion I made where the tables
can be related. Thanks Douglas.

--
'---------------
'John Mishefske
'---------------