SQL Query on Excel and Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL Query on Excel and Access

  1. #1
    Join Date
    Nov 2004
    Location
    TH
    Posts
    32

    SQL Query on Excel and Access

    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
    Code:
    "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
    Code:
    "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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Nov 2004
    Location
    TH
    Posts
    32

    Sample Data

    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
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center