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

    Append query won't "run".

    Tfort

      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 Level 2

          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 Level 1

            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 Level 1

              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 Level 2

                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

                1 person found this helpful
                • 5. Re: Append query won't "run".
                  Dan Bracuk Level 5

                  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

                  1 person found this helpful
                  • 6. Re: Append query won't "run".
                    Tfort Level 1

                    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 Level 5

                      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 Level 1

                        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 Level 2

                          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 Level 5

                            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 Level 1

                              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.