Update query

S

Stephanie

They scare me! I do have a copy of the db.

Here's what I'm trying to say:

Find Contacts who are Board Members and make them Gala Members as well.

Here's my query to find Board Members:
SELECT Contacts.LastName, Contacts.ContactID
FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID =
GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null));

The Groups.GroupID for "Gala" is 12.
GroupMembers contains: GroupMemberID, GroupID, ContactID,GMemberEnd

Here's what I'm thinking so far:
UPDATE GroupMembers SET GroupMembers.GroupID = 12
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null));

But I've seem to have lost the ContactID info. And I want to make sure that
I'm not going to overwrite "Board of Directors" with "Gala"...

I'd appreciate your suggestions.
 
J

John Spencer

Looks to me as if you want to add a new record to GroupMembers (append) and
not UPDATE an existing record.

You should be able to use your first query as the source for the append
query. Something like
INSERT INTO GroupMembers (GroupID, ContactID)
SELECT "12" as GroupID, Contacts.ContactID
FROM Contacts INNER JOIN
(Groups INNER JOIN GroupMembers
ON Groups.GroupID = GroupMembers.GroupID)
ON Contacts.ContactID = GroupMembers.ContactID
WHERE (((Groups.GroupName)="Board of Directors") AND
((GroupMembers.GMemberEnd) Is Null));

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Stephanie

John,

Thanks for the reply and the lesson! I'll give it a try.
My query snip is from a union query that has 3 queries in it. Would I be
better off running the entire union query, or should I separate the queries?
I'm a bit worried that I may append a Contact's group more than once if I
run the queries separately...

Have a great weekend!
 
J

John Spencer

Try it and see if it works. Although with the union query you will
probably need to save the union query and then use the saved query as
the source.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 

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

Similar Threads

Union query- count records 2
Union without duplicates? 1
Table short name 2
selecting union query results 7
Summing a sum 16
Update Query producing "blank" records 1
Need help with a combobox query 12
sql syntax 3

Top