Update a table with select query

[dbfool]

I've got several select queries that work well. What I would like to do is update the table's data where the query gets it's info with the results of the select query. Generally with update queries, it is required to type in some text in the Update To row. I want to have that row get it's data from the Select query not something I have to type in. How do I do that?

 

[dbfool]

Pat,
Just a bit tough for me to follow/create tables that match your SQL. Here is one of the Select queries that I'm working with. Names and Roles are tables. I would like to update the date field AnnouncmentsDate in the Roles table to the first day of the next month using an update query.

SELECT TOP 1 [FirstName] & " " & [LastName] AS Announcements, Roles.AnnouncementsDate, Names.Phone_Number, Names.LastName, Names.FirstName, Roles.NameID
FROM [Names] INNER JOIN Roles ON Names.NameID = Roles.NameID
WHERE (((Roles.Announcements)=Yes) AND ((Names.Active)=Yes))
ORDER BY Roles.AnnouncementsDate;

 

[dbfool]

If I don't use the Names table I don't know if the date field will be updated on only the one selected record. Will it update all records? When I run the update query it pulls four records, not the single record I want to update.

I can move the "Active" field to the Roles table if necessary, and then I may not have to use the Names table in the update query. I only want to update the record selected by the Select query in the AnnouncementsDate field so the next month when I run this again, someone else will be chosen. This is so no one will be chosen for each task too often and the duties will be spread out evenly.

Here is the SQL that pulls the four records:

UPDATE [Names] INNER JOIN Roles ON Names.NameID = Roles.NameID SET Roles.AnnouncementsDate = CDate(Month(DateAdd("m",1,Date()) & "/1/" & Year(DateAdd("m",1,Date()))))
WHERE (((Roles.Announcements)=Yes) AND ((Names.Active)=Yes));

 

[dbfool]

So, is it not possible to update a single record on my table with the results of a Select query?

 

[dbfool]

The original Select query selects the person who has not had that duty the longest time ago. However, when the Update query is created on the Roles table it has no provision to select a single record and so returns more records than is desired.
If I build the Update query on the original Select query then add the Roles table and it's AnnouncementsDate field, and insert the expression cDate(Month(DateAdd("m",1,Date()) & "/1/" & Year(DateAdd("m",1,Date())))) in the update to cell, when run I get the error "Data Mismatch in Criteria Expression". My data type for that field is Date.
I would really like to include some small screen shots of these queries, to make this plain, but this online site is restricing me due to the low number of posts I've made.

 

[dbfool]

When I create an update query on my select query then add the Roles table to it along with the field I want to update everything updates just fine but only when I type in the data (02/01/2013) for example in the update to cell.
When I try the same thing with the date expression - cDate(Month(DateAdd("m",1,Date()) & "/1/" & Year(DateAdd("m",1,Date())))) I get the data mismatch screen and nothing gets updated.

 

[dbfool]

Yes Pat I picked up on that right away and fixed it. From what I could see it needed 5 closing parentheses at the end of the expression. With that in place I get the data mismatch error.

 

[dbfool]

Yes it is a a date field.

 

[dbfool]

Take the query apart?

To test this, I made one simple table with two fields and a few records, NameID and a date field. When I try to update that table's date field with an update query I get the same error message. Isn't cDate used to convert field values? I don't need to convert this field, but just change it's value mm/dd/yyyy to the first day of the next month.