use query as the criteria for an append query

[tagteam]

I have a table of values in a table called UpdateTable. Before i do the update though i want to copy the entire record for archive purposes. I would like to know how to create an append query that uses another query as the criteria. ie;

UpdateTable - holds current values to be used to update MainTable
MainTable - holds all the data
ArchiveTable- a copy of the entire record that was updated even if only one value was updated

I would like to know how to use a query as the criteria for an update query so I can get a list of all CarIDNumbers in the UpdateTable and use that as the criteria for which records in the MainTable I want to append a copy to in the ArchiveTable.

Basically I need all the CarIDNumbers in the UpdateTable because each time I want to append a copy of all of the records and after each process I will delete all the records in the UpdateTable

Please let me know if further clarification is needed.

[John_G]

Hi -

In SQL, maybe something like this:

Insert into ArchiveTable Select * from MainTable where MainTable.CarIDNumber in (Select UpdateTable.CarIDNumber from UpdateTable)

This gets a list of all the CarID's in the UpdateTable, then copies all records from MainTable to ArchiveTable where the CarID's are in the list.

This will only work if all the fields in MainTable are also in ArchiveTable.

HTH

John

[tagteam]

Thanks for your reply. I have tried that and I get the following Error

"Select * cannot be used in an INSERT INTO query when the source or destination table contains a multivalued field"

However, I have confirmed that both the source and destination are free of multivalued fields because i never add them. Every field in the table that is text says - text box. There are a couple Boolean - yes/no fields. Are these considered multi-valued?

[tagteam]

I forgot to mention that if you use the view button for the query it does show the correct cars that need to be updated. It just fails when you actually try to run the append query

[John_G]

Hi -

I use A2003 still, so I was giving you information based on that. But the error message you are getting indicates that you do have a multivalued field somewhere. Perhaps one of the A2010 gurus here gan tell you how to check if you do.

This sort of difficulty is why many of us here consider multi-valued field to be an abomination, and avoid using them. The reason is that multivalued fields violate many of the principles of proper database design.

I will keep looking for a solution for you.

John

[rpeare]

As far as I know there's no way to use an update query on a multi value field table. Yet another reason NOT to use them, they are a horrible horrible addition to MS Access. There is VBA that you can use perform an update though (link below to a similar thread). But I would really recommend you divorce yourself from using multi value fields.

https://www.accessforums.net/access/...end-42931.html

[tagteam]

Well how can I check for multi-valued fields. I have read for a long time to never use them so I never do. I did have an attachment field early on in the DB development but that was deleted months ago. NO other field should be multi-valued so how can i check?

[rpeare]

I don't know what version of access you're using but for that thread I referred you to I just created a query in access 2013 and brought in the table that had the multi value fields and the table structure in the design view showed the fields within the multi value field. Other than that I don't know as I don't use them, sorry.

[tagteam]

This must be an access bug that gives the wrong error message. I have checked and there are no multi-valued fields, and I never use them. Rather than use just the sql language i used the designer and added the update table to the query and then did a left join and everything works fine.

I think the multi-valued error is just a bug that display's the wrong time sometimes and that threw me off. So basically all I had to do was add the update table to my main table and create the join.