UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Where Not Exists Not Producing Correct Values, Access 2013    
 
   
SomekindaVB
post Jun 11 2019, 07:29 PM
Post#1



Posts: 300
Joined: 15-December 16



Hi All,

I'm trying to do an insert into. Before I add the insert clause I needed to see if it was going to work.

However, this is producing far more records than it should and I don't know why. Is there any particular reason why this would not work?

CODE
SELECT *
FROM Qry_MyQuery
WHERE NOT EXISTS (
    Select
        [CustomerID],
        [ActivityID],
        [RelatedReferenceID],
        [RelatedReferenceType],
        [Manager],
        [Due Date] as DueDate,
        ItemType
    FROM Tbl_MyTable
    Where
    [CustomerID] = CustomerID and
    [ActivityID] = 58 And
    [RelatedReferenceID] = ArrangementID And
    [RelatedReferenceType] = ArrangementType And
    [ItemType] = 'Account' And
    [Due Date] = Enddate )


Cheers
Go to the top of the page
 
MadPiet
post Jun 11 2019, 07:58 PM
Post#2



Posts: 3,171
Joined: 27-February 09



I noticed a few things that don't look right.
1. you don't need to specify ANY fields in the NOT EXISTS () subquery. You can use "SELECT 1 FROM tbl_MyTable t"
2. You're not aliasing either MyQuery or MyTable, so you can't correlate the two.

CODE
SELECT {Field List}
FROM MyQuery q
WHERE NOT EXISTS (SELECT 1
                               FROM MyTable t
                               WHERE t.CustomerID = q.CustomerID  /* joins MyQuery q and MyTable t */
                               AND …. )


Note the aliasing in the query. That's crucial to making this work. (I'm referring to fields in MyQuery q in the subquery and joining that to fields in the outer query. There's no reason to list any fields in the query against MyTable because EXISTS basically returns True or False depending on whether any records are returned in the subquery.
Go to the top of the page
 
MadPiet
post Jun 11 2019, 09:32 PM
Post#3



Posts: 3,171
Joined: 27-February 09



An INSERT... right!

CODE
INSERT INTO DestinationTable (field1, field2....)
SELECT {field1, field2....}
FROM MyQuery q
WHERE NOT EXISTS (SELECT 1
                               FROM MyTable t
                               WHERE t.CustomerID = q.CustomerID  /* joins MyQuery q and MyTable t */
                               AND …. )


Does that help?
Go to the top of the page
 
SomekindaVB
post Jun 11 2019, 11:59 PM
Post#4



Posts: 300
Joined: 15-December 16



Hey MadPiet.

Thanks for the tips. I'm going to try it right now and I'll let you know as soon as Im done!

Cheers
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 10:20 AM