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.

[paross1]

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

[Tfort]

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.

[Tfort]

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

[paross1]

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

[Tfort]

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.

[paross1 · Accepted solution]

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)

[Dan_Bracuk]

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

[Tfort]

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.

[Dan_Bracuk]

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

[Dan_Bracuk]

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?

[Tfort]

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.