I'm a semi-newby and need help with an update query. I've inherited some Access 2002 queries that won't open up and view in Access 2007. However, the author sent me the SQL, but I can't get them to work. The intent of the queries is to selectively take fields from a query (HshldData) and copy them selectively to a table that is ultimately exported to MS Word (ExportData). I've reduced the first query to the simplest form, and it still doesn't work. A few details ....
Both HshldData and ExportData share a field FAMILYID. FAMILYID is a key field in ExportData. HshldData uses an internal key field because FAMILYID is not unique in HshldData (more than one person in a family. MS Word mailmerge needs a single [ugly] record, one per family times lots of families. I know what I'm doing is ugly, but that is part of the requirement - MS Word is the report writer.)
The first thing I do is populate ExportData with the basic family fields (FAMILYID, address, phone number, etc.) using an append query. This works fine. The next thing I need to do is populate the details for the first individual in the family (MemberID, Name, email address, cell phone, etc. Note MemberID is unique to each family member, but that is not apparently an issue. It is just data now.)
So, before I start the update query, the ExportData table is sitting there with 79 records, one per family. There are fields available of the right type for the data I'm about to copy. HshldData has lots of records. Here is the query to populate the only the member id first individual:
UPDATE ExportData RIGHT JOIN HshldData ON ExportData.FAMLYID = HshldData.FAMLYID SET ExportData.M1ID = [HshldData].[Mbrid]
It doesn't matter whether I use [HshldData].[Mbrid] or [HshldData]![Mbrid]
When I execute the query I receive an error: "Operation must use an updatable query".
Do you have any idea why the query is failing in Access 2007 but worked in Access 2000? Can the query be re-written to work in Access 2007? Any help is much appreciated. Thank you in advance.
It sounds like you're opening a query in A2007 based on tables that are A200X (not 7)-based. If you update the database to A2007 (you may want to make a copy of the db first), and then try your query again, it might work.