Append Query

P

PPCO

I am trying to append data from tblitemline to tblitemlist. I only want to
append new records. The unique field is ListID. I want to append 4 fields
total. I can't get it to limit it to new records. Any help would be
appreciated. Thanks!
 
J

Jerry Whittle

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
 
P

PPCO

Here's the SQL:

INSERT INTO tblItemList ( ListID, FullName, IsActive, Type )
SELECT tblItemLine.ListID, tblItemLine.FullName, tblItemLine.IsActive,
tblItemLine.Type
FROM tblItemLine INNER JOIN tblItemList ON tblItemLine.ListID =
tblItemList.ListID
GROUP BY tblItemLine.ListID, tblItemLine.FullName, tblItemLine.IsActive,
tblItemLine.Type
HAVING (((tblItemLine.ListID)<>[tblitemline].[listid]));

Right now it returns no records. I can't set up relationships in the
relationship window with the tblitemline because it is a ODBC table. I did
manually set one up in the append query.
 
J

Jerry Whittle

Is the tblItemList.ListID field the primary key for that table? If so a
simple insert statement should work and Access will prevent duplicate ListIDs
from being entered.

Otherwise try the following:

INSERT INTO tblItemList ( ListID, FullName, IsActive, Type )
SELECT DISTINCT tblItemLine.ListID,
tblItemLine.FullName,
tblItemLine.IsActive,
tblItemLine.Type
FROM tblItemLine
WHERE tblItemLine.ListID NOT IN
(SELECT ListID FROM tblItemList) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


PPCO said:
Here's the SQL:

INSERT INTO tblItemList ( ListID, FullName, IsActive, Type )
SELECT tblItemLine.ListID, tblItemLine.FullName, tblItemLine.IsActive,
tblItemLine.Type
FROM tblItemLine INNER JOIN tblItemList ON tblItemLine.ListID =
tblItemList.ListID
GROUP BY tblItemLine.ListID, tblItemLine.FullName, tblItemLine.IsActive,
tblItemLine.Type
HAVING (((tblItemLine.ListID)<>[tblitemline].[listid]));

Right now it returns no records. I can't set up relationships in the
relationship window with the tblitemline because it is a ODBC table. I did
manually set one up in the append query.


Jerry Whittle said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
Click to expand...
Click to expand...
 
Top