Append query help

[Daveo]

Hi there,

I have 2 tables as follows:

tblMembers
------------------
memberID
surname
forename
status
etc....

tblAccounts
------------------
accountID
memberID
etc........

I would like to create an append query that adds a new record in
tblAccounts for each record in tblMembers where tblMembers.status = 1.
I've tried it, but I only get a new record in tblAccounts if there is
already an existing record for a member in tblAccounts. Otherwise, I
get 0 records appended.

My current query is:

NSERT INTO tblAccounts ( memberID )
SELECT DISTINCT tblAccounts.memberID
FROM tblMembers INNER JOIN tblAccounts ON tblMembers.memberID =
tblAccounts.memberID
WHERE (((tblMembers.status)=1));

Can anyone point me in the right direction?

Many thanks - David

 

[Guest]

Try this --
INSERT INTO tblAccounts ( memberID )
SELECT tblMembers.memberID
FROM tblMembers LEFT JOIN tblAccounts ON tblMembers.memberID =
tblAccounts.memberID
WHERE (((tblMembers.status)=1) AND ((tblAccounts.memberID) Is Null));

 

[Daveo]

Hi Karl,

Thanks for replying. Altering your suggestion a bit, I found that this
works:

INSERT INTO tblAccounts ( memberID )
SELECT DISTINCT tblMembers.memberID
FROM tblMembers LEFT JOIN tblAccounts ON tblMembers.memberID =
tblAccounts.memberID
WHERE (((tblMembers.status)=1)) OR (((tblAccounts.memberID) Is Null));

Many thanks for the help,


David