11 Replies Latest reply: Jun 22, 2009 8:02 AM by Tfort RSS

    Append query won't "run".

    Tfort Community Member

      I made an append query that compares two tables and if they match and the data is not in a 3rd table it appends that data.

       

      This query runs perfectly within access and locally. But as soon as I upload it onto our server it doesnt seem to run. Is there any way to refer to a query that is saved inside of access so that coldfusion will run it?

       

      I can post the code if that may help.

        • 1. Re: Append query won't "run".
          paross1 Community Member

          You are probably not going to get a whole lot of suggestions unless you do post your code, since nobody has any idea what you are trying to do.

           

          Phil

          • 2. Re: Append query won't "run".
            Tfort Community Member

            INSERT INTO de3

            ( PWS_ID, CCRYear, DateCCRDelivered, DateCCRReceived, DateCertLetterReceived, TNRCC_CCR, URL )

            SELECT de2.PWS_ID, de2.CCRYear, de2.DateCCRDelivered, de2.DateCCRReceived, de2.DateCertLetterReceived, de2.TNRCC_CCR, de2.URL

            FROM

            (de1 INNER JOIN de2 ON (de1.URL = de2.URL) AND

            (de1.TNRCC_CCR = de2.TNRCC_CCR) AND

            (de1.DateCertLetterReceived = de2.DateCertLetterReceived) AND

            (de1.DateCCRReceived = de2.DateCCRReceived) AND

            (de1.DateCCRDelivered = de2.DateCCRDelivered) AND

            (de1.PWS_ID = de2.PWS_ID) AND

            (de1.CCRYear = de2.CCRYear))

            LEFT JOIN de3 ON

            (de2.TNRCC_CCR = de3.TNRCC_CCR) AND

            (de2.URL = de3.URL) AND

            (de2.DateCertLetterReceived = de3.DateCertLetterReceived) AND

            (de2.DateCCRReceived = de3.DateCCRReceived) AND

            (de2.DateCCRDelivered = de3.DateCCRDelivered) AND

            (de2.PWS_ID = de3.PWS_ID) AND

            (de2.CCRYear = de3.CCRYear)

            WHERE

            (((de3.PWS_ID) Is Null) AND

            ((de3.DateCCRDelivered) Is Null) AND

            ((de3.DateCCRReceived) Is Null) AND

            ((de3.DateCertLetterReceived) Is Null) AND

            ((de3.TNRCC_CCR) Is Null) AND

            ((de3.URL) Is Null) AND

            ((de3.CCRYear) Is Null));

             

            I hope that helps.

            • 3. Re: Append query won't "run".
              Tfort Community Member

              And I just notcied it doesnt even need all those Nulls.. As long as PWS_ID is null it should run it.

              • 4. Re: Append query won't "run".
                paross1 Community Member

                Not sure I "get" your select statement...

                 

                How can de2.DateCCRDelivered = de3.DateCCRDelivered when de3.DateCCRReceived Is Null?
                How can de2.DateCCRReceived = de3.DateCCRReceived when de3.DateCCRReceived Is Null?
                How can de2.DateCertLetterReceived = de3.DateCertLetterReceived when de3.DateCertLetterReceived Is Null?
                How can de2.TNRCC_CCR = de3.TNRCC_CCR when de3.TNRCC_CCR Is Null
                How can de2.URL = de3.URL when de3.URL Is Null?
                How can de2.CCRYear = de3.CCRYear when de3.CCRYear Is Null?
                How can de2.PWS_ID = de3.PWS_ID when de3.PWS_ID Is Null?

                 

                Phil

                • 5. Re: Append query won't "run".
                  Dan Bracuk Community Member

                  Make the left join part a subquery, and it should work.

                   

                  insert into de3

                  (f1, f2, etc)

                  select value1, value2, etc

                  from de1 join de2 on something

                  left join (select f1, f2, etc

                  from de3

                  where whatever) x on something

                   

                  where x.f1 is null

                  and x.f2 is null

                  etc

                  • 6. Re: Append query won't "run".
                    Tfort Community Member

                    Well when I ran the code in Access it pulled 36 records and appended them to de3. But it should have pulled more like 300+.

                     

                    @Dan Bracuk Ive never used a sub query so im not quite sure how that would work. I'm not sure how to follow the code example you posted.

                    • 7. Re: Append query won't "run".
                      Dan Bracuk Community Member

                      Subqueries in the where clause have other names such as "derived tables", "creating tables on the fly", and such.  I don't know if access supports it, but start with something simple like

                       

                      select thecount from

                      (select count(*) as thecount

                      from de1) YouNeedAnAliasForTheSubqueryAndDoNotUseTheKeywordAs

                      • 8. Re: Append query won't "run".
                        Tfort Community Member

                        Maybe my logic is wrong. I was trying to state that IF de1 and de2 are equal append those values to de3 if the PWS_id doe nto exist in that table.

                        • 9. Re: Append query won't "run".
                          paross1 Community Member

                          Is this more like it?

                           

                          INSERT INTO de3
                          ( PWS_ID, CCRYear, DateCCRDelivered, DateCCRReceived, DateCertLetterReceived, TNRCC_CCR, URL )
                          SELECT de2.PWS_ID, de2.CCRYear, de2.DateCCRDelivered, de2.DateCCRReceived, de2.DateCertLetterReceived, de2.TNRCC_CCR, de2.URL
                          FROM
                          (de1 INNER JOIN de2 ON (de1.URL = de2.URL) AND
                          (de1.TNRCC_CCR = de2.TNRCC_CCR) AND
                          (de1.DateCertLetterReceived = de2.DateCertLetterReceived) AND
                          (de1.DateCCRReceived = de2.DateCCRReceived) AND
                          (de1.DateCCRDelivered = de2.DateCCRDelivered) AND
                          (de1.PWS_ID = de2.PWS_ID) AND
                          (de1.CCRYear = de2.CCRYear))
                          WHERE NOT EXISTS(SELECT 1
                          FROM de3
                          WHERE  de2.PWS_ID = de3.PWS_ID)

                          • 10. Re: Append query won't "run".
                            Dan Bracuk Community Member

                            Now that you have some suggestions on how to do it, why bother?  You already have the data in tables de1 and 2.  What do you hope to accomplish with a 3rd table?

                            • 11. Re: Append query won't "run".
                              Tfort Community Member

                              The de3 table is a billing table. The de1 / de2 are both data entrys for quality checks. The code that was posted a few post back seems to do what i want! Thanks guys for all the help.