Append Query With Different Field Names

[crhodus]

I'm having problems creating an append query. I have 3 tables:
P_Table --> (fields) PNum, Pname, Pcity
IP_Table --> (fields) IPNum, IPname, IPCity
M_Table --> (fields) MNum, UserName, UserInfo

I want to append all the data from IP_Table to P_Table only if the value of IP_Table.IPNum is in M_Table.MNum and the value of IP_Table.IPNum is not in P_Table.

IP_Table is a duplicate table of P_Table. The only difference is that the field names are different.

Can anyone help me? Thanks!

 

[Nouba]

you could try this Append Query. I would think about whether it is meaningful to duplicate the complete data.

INSERT INTO P_Table ( Pnum, Pname, Pcity )
SELECT
  IP.IPnum
, IP.IPname
, IP.IPcity
FROM
  (SELECT
    IP.IPnum
  , IP.IPname
  , IP.IPcity
  FROM IP_Table AS IP
    INNER JOIN M_table AS M ON
      IP.IPnum = M.Mnum) AS IP LEFT JOIN
  P_Table AS P ON
    IP.IPnum = P.Pnum
WHERE P.Pnum Is Null

 

[RV]

INSERT INTO P_Table (PNum, Pname, Pcity)
SELECT IPNum, IPname, IPCity
FROM IP_Table
WHERE EXISTS
(SELECT MNum
FROM M_table
WHERE MNum = IPNum)
AND NOT EXISTS
(SELECT PNum
FROM P_Table
WHERE PNum = IpNum):

RV

 

[crhodus]

Thanks for for your help. I was using NOT EQUAL TO instead of NOT EXISTS in my query.