Can I do something like this with an Update Query?

[cherold]

Hi, I did an Update Query a couple of years ago for the first and (until now) last time, and while I've forgotten quite how it works, I recall Update Queries were pretty powerful. So I was wondering if it is possible to use one to export a fairly simple but clunky database I created decades ago into a much better one I just designed. I actually created the new tables in a copy of the old database, so I don't have to deal with multiple databases.

Some fields are a simple matter of copying data from one table to another, while others require string processing and populating multiple tables. So in a simplified version:

Old Table
LPs
performer - Text


album - Text
style - Text

New Tables
Albums
ID - autonumber
performer - number that points to an ID in Performers table
album - text


Performers
ID - autonumber
first_name - text
last_name - text
band_name - text


So the album field is simply a matter of copying over text from one table to another. So that's pretty straightforward.

For performers, on the other hand, I'm copying a line of text in the format "Petty, Tom--and the Heartbreakers" into three fields (not all three fields would always be used - for example, with "Joplin, Janis" or "Madonna"). So I would need to split the string into three pieces using the comma and double-hyphen delimiters and move them into the appropriate fields in the Performers table, and then set Albums->performer to the proper ID in Performers. If a name already exists in the Performers database then of course I wouldn't copy it in again, I would just find the ID for that performer.

Most of the fields are straight imports like album but a few involve fancy processing like performer. Can something like this be done with an update query?

[CJ_London]

What you need is an append query, not an update query.

Append data to the performers table first which will set your id's - tip. don't just use the name id, use performerid

then create a temporary query on your performers table which produces the same result as in your old table and includes the id - something like

SELECT PerformerID, last_name & ", " & first_name & "--" & band_name as performer
FROM performers

now create a new query

INSERT INTO Albums (Performer, Album)
SELECT PerformerID, album
FROM LPs INNER JOIN tmpQuery ON LPs.Perfomer=tmpQuery.performer