Append Query

[Build or Die]

I have a many to many relationship that I'm trying to use an append query on:

tblContacts - ContactID (primary key), name, etc...
tblMailingList - ListID (primary key), list name
tblMailingListLINK - ContactID & TableID (one to many relationship from
above tables) and a yes/no field called 'Included'

On my form I'd like to use an append query when a list is selected from a
combo box to add any contacts that may have been added since the last time
the list was worked on.

I've created the append query, but its not ignoring duplicates. Other posts
say that it should do this automatically... where did I go wrong?

here's the query code:

INSERT INTO tblMailingListLink ( ContactID, ListID )
SELECT tblContacts.ContactID, forms!frmmailinglist.listselect AS ListID
FROM tblContacts
WHERE (((tblContacts.RemoveFromList)=No));

Thanks for reading!

 

[Ofer]

What is the primary key in tblMailingListLink , if you didnt define any, this
is why its appending duplicates

 

[Build or Die]

I just added a primary key 'ListingID' (autonumber) to the table
tblMailingListLink. I sampled the append query and it still wanted to add
another 700 records. I also tried to add the new primary key to the query
and that didn't help either. Is it the autonumber thats messing this up? Or
do my relationships need to be modified from the default?

Thanks.

 

[Ofer]

The new field, the auto number, can't be use to remove the duplicates.
You have to decide which fields you don't want to repeat, and declare the
combination of fields as your key

 

[Build or Die]

Thanks for helping me out on this.... Something must be wrong with me
today... I can't get this to work correctly.

I've removed the 'autonumber' field and set a primary key on the two fields
that will append in the tblMailingListLink - ContactID and ListID. ListID is
a constant? Could that be an issue? The relationships and query have not
changed. What else could be left?

 

[Ofer]

In the append query, change the syntax to

Insert Into TableName (Field1, Field2)
Select Distinct Field1, Field2 From TableName

In the select part add distinct

Or, open the query in design view, open the properties, and change the
Unique values property to Yes, it will add the distict for you

 

[Build or Die]

Just when I thought that was going to do it - not there yet.
My new syntax:

INSERT INTO tblMailingListLink ( ContactID, ListID )
SELECT DISTINCT tblContacts.ContactID, forms!frmmailinglist.listselect AS
ListID
FROM tblContacts
WHERE (((tblContacts.RemoveFromList)=No));

It seems like this should resolve any query problems. Would having 'enforce
referential integrity' set on in the relationships be a problem?

Got anymore tips for the guy who can't get an append query to work on a many
to many relationship?

Thanks for all your help.