SQL Query on Excel and Access

[Joey_G]

Hi All,


I've tried to get the records of the a field (SN) from table A that exists in the field (OT) or field (IN) of table B and use not exists as well but the problem i face here is that when i use the sql query in access i get my both results but when query is used with excel i get the correct results only in the EXISTS statement but no records was found in NOT EXISTS statement

here's the sql statement used in Access

"SELECT * FROM [SCETRETURN] AS S WHERE EXISTS (SELECT * FROM CLAIM AS C WHERE (C.OT=S.SN OR C.IN=S.SN))                       /*HERE I GET 9 records out of 10 records

"SELECT * FROM [SCETRETURN] AS S WHERE NOT EXISTS (SELECT * FROM CLAIM AS C WHERE (C.OT=S.SN OR C.IN=S.SN))                       /*HERE I GET 1 records out of 10 records

In the access it is find but with excel

"SELECT * FROM [SCETRETURN$] AS S WHERE EXISTS (SELECT * FROM [CLAIM$] AS C WHERE (C.OT=S.SN OR C.IN=S.SN))                       /*HERE I GET 9 records out of 10 records

"SELECT * FROM [SCETRETURN$] AS S WHERE NOT EXISTS (SELECT * FROM [CLAIM$] AS C WHERE (C.OT=S.SN OR C.IN=S.SN))                       /*HERE I GET 0 records out of 10 records

That's strange as the query are the same but the results for NOT EXISTS ain't the same.

OUT OF 10 RECORDS - ACCESS - EXCEL
EXISTS STATEMENT - 9 - 9
NOT EXISTS STATEMENT - 1 - 0


Could anyone please help?

Regards,

Joey

[pclement]

Could you post the rest of your code and possibly a small sample of the data from each Worksheet? Or, just post a small Excel Workbook attachment to the forum?

[Joey_G]

Hi Paul,

Here's the excel file I've attached with the source code.

Just double click the source file text file and open the Item 1.xls same for SAve report text box.

I've just need to get the SN from the scet return that is presents in the claim worksheet either on the outdoor unit or indoor unit.

And get the SN from the scet return that is not listed in the claim worksheet

and create the new workbook with 2 worksheet CLAIM and NOT CLAIM

Regards,

Joey

[pclement]

Using the sample Excel Workbook you provided the following statements both worked properly for me:

SELECT * FROM [SCET RETURN$] AS S WHERE NOT EXISTS (SELECT * FROM [CLAIM$] AS C WHERE (C.[OUTDOOR UNIT]=S.SN OR C.[INDOOR UNIT]=S.SN))

Returned zero rows.

SELECT * FROM [SCET RETURN$] AS S WHERE EXISTS (SELECT * FROM [CLAIM$] AS C WHERE (C.[OUTDOOR UNIT]=S.SN OR C.[INDOOR UNIT]=S.SN))

Returned nine rows.

I added another row to the scet return$ Worksheet where the sn value did not match any items in the outdoor and indoor unit columns of the claim$ Worksheet. That row (the only row) was returned when I ran the first query above. The second query still returned nine rows as I would have expected.

For this test I used DAO 3.6. I also tested with ADO 2.7 and it worked just fine.