Copy link to clipboard
Copied
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.
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
(de
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
And I just notcied it doesnt even need all those Nulls.. As long as PWS_ID is null it should run it.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.