Append query help

D

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
 
K

KARL DEWEY

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));
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top