Append Query: How do I Synch Two Fields in Different Tables?

[tatihulot]

Hello, I have a question about append queries.



I’ve been studying how to import data into Access, and also have been studying append queries. I believe I’ve successfully imported an Excel worksheet into Access as a new table.

The Access database is the music collection database I’ve been working with.

The Excel spreadsheet that I imported has approximately 250 records. There are two fields: Artist and Title. I’ve successfully appended the Artist part into the Music table, which you can see if you look at the database I’ve attached to this message.

But…I haven’t figured out how to import the Title field contents, and synch them up with the corresponding Artist contents. The Artist and Title fields are in two different tables, linked together via a junction table.

Is it possible to append the Title field and synch it up with the Artist data I’ve already appended so that they match in the tables and forms? Do I have to create separate append queries? Which field should I append the Title column into, and how do I ensure they’ll synch up?

MusicAppend.zip
Thank you! Jd

[Micron]

I took a look and don't understand what the issue is. The tables already look to be synched. If you are asking how to do this during the import, I would import the entire sheet as a table. Then normalize it if necessary by copying data via queries to normalized Access tables that will also have the correct field data types. I might even do this if the table looks normalized but some fields data types are not imported correctly.

[tatihulot]

Let's take a look at this together. Maybe I didn't explain things well enough.

First take a look at the table named Singles. I imported that table from Excel. There are 237 records in that table. I imported its Artist field into the Music table's Artist field. The records I imported via an Append query are numbered 10279 through 10514 in the Music/Artist field. Everything in that field before 10279 I entered manually, prior to importing/appending the other data.

If you notice, nothing from the TITLE field in the Singles table was imported. Look in the AlbumTitle table's AlbumTitle field. Nothing from Singles/Title is in that field.

All of the records I entered manually, prior to appending the Singles/Artist field, are synched up. But nothing else is, because I haven't appended the album titles yet. I haven't figured out how to do that, and synch those album titles to the artists I did append.

I hope that makes sense. If it does, then I still need to figure out how to enter the titles, so that they synch up properly with Artists 10279 through 10514.

I don't think I can do a better job of explaining it.

I did import the entire Excel worksheet as a table, which you can see. It is the Singles table.

You said some fields' data types are not imported correctly. I don't see that. Can you please explain that? And what is the best solution to getting the titles into the proper field, synched up evenly with Artists 10279-10514? I am really confused about this. Thank you.

[Micron]

No, I meant if that happens. Will take another look later.

if the table looks normalized but some fields data types are not imported correctly

[Welshgasman]

I would prepare the Excel sheet correctly.

1. Copy the artist column to another sheet. Get rid of duplicates.
2. On the Artists sheet add a column that gives them a numeric number 1 to how many artists there are. That sheet is going to be the Artist table.
3. Back to original sheet. Insert a column that does a VLOOKUP() of the artist to the artist sheet and get their numeric number.
4. Link both sheets in Access.
5. Select Artists sheet to populate your Artist table.
6 Select original sheet, omitting the artist column. The numeric column is your ArtistFK. Select that as your Album/Title table.

[Gicu]

You need to create a new query linking the Music and Singles tables on the Artist (name) field. You bring in the MusicPK field from the Music table and the Title field from Singles. Run it as a select query to check you get the right data then change it (on the Design ribbon) to an Append query (to table AlbumTitle) and append MusicPK to ArtistFK and Title to Title.

So to import new ones in the future will be a four step process: empty the Singles table (by running a Delete all query), import the Excel sheet into Singles, run your existing AppendArtist query (you could modify it to exclude the artists already existing, see SQL below) and finally run the new append query I mentioned.

AppendArtist:
INSERT INTO Music ( Artist )
SELECT Singles.Artist
FROM Singles LEFT JOIN Music ON Singles.Artist = Music.Artist
WHERE (((Singles.Artist) Is Not Null) AND ((Music.Artist) Is Null));

Cheers,

[Welshgasman]

Also, I believe properties like USB should be in the album table, not the artist table.

[tatihulot]

Okay, I'll work on this. Thank you, everyone.