Skip navigation
Tfort
Currently Being Moderated

Append query won't "run".

Jun 19, 2009 9:20 AM

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.

 
Replies
  • Currently Being Moderated
    Jun 19, 2009 9:39 AM   in reply to Tfort

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 19, 2009 11:03 AM   in reply to Tfort

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 19, 2009 11:19 AM   in reply to Tfort

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 19, 2009 12:26 PM   in reply to Tfort

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 19, 2009 2:25 PM   in reply to Tfort

    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)

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 19, 2009 7:16 PM   in reply to Tfort

    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?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points