Prev: dirDataCopy error message when converting to Access 2000
Next: Access hanging periodically
From: David on 3 Sep 2005 01:34
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.
From: John Mishefske on 3 Sep 2005 02:39
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
'---------------
From: Douglas J. Steele on 3 Sep 2005 08:45
"John Mishefske" <jmishefskeNO(a)SPAMyahoo.com> wrote in message
news:zybSe.63881$3S5.13729(a)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!)





From: John Mishefske on 4 Sep 2005 00:31
Douglas J. Steele wrote:
> "John Mishefske" <jmishefskeNO(a)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
'---------------
 | 
Pages: 1
Prev: dirDataCopy error message when converting to Access 2000
Next: Access hanging periodically