Append problem

  • Thread starter Thread starter Tcs
  • Start date Start date
T

Tcs

Can I not use the same table to which I am trying to append, to see if a record
already exists? Here's the deal...

I have a table on my AS400 with multiple [utility] service records for each
location. (Services are attached to a location.) Not all locations have ALL
services. Most do have electricity, but since not all do, I want to step thru
all our services, using a separate query for each, to make sure I get ALL
locations. (First query builds the table, subsequent queries append.) But I
only want ONE record per location in my local table. I don't care what the
service(s) is/are. Here's my code:

INSERT INTO
tblPropTaxes_UT220AP (
[Loc ID],
[Cust ID],
Units,
[Init YY],
[Init MM],
[Init DD],
Svc,
Stat,
[TimeStamp],
[Loc ID] )

SELECT
tblCXLIB_UT220AP.UTLCID AS [Loc ID],
tblCXLIB_UT220AP.UTCSID AS [Cust ID],
tblCXLIB_UT220AP.UTAUNT AS Units,
tblCXLIB_UT220AP.UTSSTY AS [Init YY],
tblCXLIB_UT220AP.UTSSTM AS [Init MM],
tblCXLIB_UT220AP.UTSSTD AS [Init DD],
tblCXLIB_UT220AP.UTSVC AS Svc,
tblCXLIB_UT220AP.UTSSTS AS Stat,
Now() AS [TimeStamp],
tblPropTaxes_UT220AP.[Loc ID]

FROM
tblCXLIB_UT220AP RIGHT JOIN tblPropTaxes_UT220AP ON
tblCXLIB_UT220AP.UTLCID = tblPropTaxes_UT220AP.[Loc ID]

WHERE
(((tblCXLIB_UT220AP.UTSVC)="EL") AND ((tblCXLIB_UT220AP.UTSSTS)="A") AND
((tblPropTaxes_UT220AP.[Loc ID]) Is Null))

ORDER BY
tblCXLIB_UT220AP.UTLCID,
tblCXLIB_UT220AP.UTCSID;

tblCXLIB_UT220AP = the tbl on the AS400
tblPropTaxes_UT220AP = my local tbl

The above is not the only variation I've tried, but I'm obviously missing
something. (I know we have some 10k+ water accts, and 15k+ electric accts. If
I get the water first, then try to append locations with electric, I always get
"0" (zero) records.) I've done this before, what I'm trying to do. (Although a
*long*, *long* time ago.)

Might someone help me see the error(s) of my way?

I apprecaite it, thanks in advance,

Tom
 
Can you not to which DO WHAT?!
-G. Bush, Jr. :D

I would just do two queries. One to append the unknown Loc's, the 2nd to
perform the meat of the rest.
 
Check the direction of the JOIN. I think you may want a LEFT JOIN vice a
RIGHT JOIN.

....
FROM
tblCXLIB_UT220AP LEFT JOIN tblPropTaxes_UT220AP ON
tblCXLIB_UT220AP.UTLCID = tblPropTaxes_UT220AP.[Loc ID]
....
 
Back
Top