Returning primary key values to a parent table

[Hank in KC]

I recently discovered a flat file from a 20 year-old program written for LP
albums, song titles and singers. The file is essentially a spreadsheet with
three fields: AlbumTitle, SongTitle and ArtistName. Obviously, there is an
unacceptable quantity of duplicated data. (I was able to get the file into
Excel format then import it into Access.)

I constructed three tables: tblAlbums, tblSongs, and tblArtists. Each table
has two fields: AlbumID, AlbumTitle, SongID, SongTitle, and ArtistID,
ArtistName. I next used three separate append queries with autonumber
primary keys to populate the tables.

So far I have been unable to bring the primary key values back into the
initial table tblAlbums-Songs-Artist. I have added the foreign keys (type
number) to this table, but am unable to get these fields populated
correctly.

 

[John Vinson]

I recently discovered a flat file from a 20 year-old program written for LP
albums, song titles and singers. The file is essentially a spreadsheet with
three fields: AlbumTitle, SongTitle and ArtistName. Obviously, there is an
unacceptable quantity of duplicated data. (I was able to get the file into
Excel format then import it into Access.)

I constructed three tables: tblAlbums, tblSongs, and tblArtists. Each table
has two fields: AlbumID, AlbumTitle, SongID, SongTitle, and ArtistID,
ArtistName. I next used three separate append queries with autonumber
primary keys to populate the tables.

Using SELECT DISTINCT to get only one instance of each AlbumTitle,
SongTitle and ArtistName? That may not be a safe assumption: you may
in fact have more than one album, with different (or even overlapping)
songs and artists but which happen to have the same AlbumTitle!

So far I have been unable to bring the primary key values back into the
initial table tblAlbums-Songs-Artist. I have added the foreign keys (type
number) to this table, but am unable to get these fields populated
correctly.

Try updating AlbumID to

=(SELECT AlbumID FROM tblAlbums WHERE tblAlbums.AlbumTitle =
flattable.AlbumTitle)

and similarly for the other two ID fields. But once you have the three
normalized tables, I'd say that you don't need this table at all -
what you need is a many to many resolver table with only the three ID
fields!

 

[Bogdan Zamfir]

Hi,

You need to write a VBA proc, which will scan the original file (Excel, or
access, the un-normalized table), then for each record, look in tblAlbum,
tblArtist and tblSong to find those ID's and add a record to the table you
created with the foreign keys.

However, the solution (with a single file with all three foreign keys) might
not be the best.
You might want to model the database a little different.

There could be the following cases:

1. an album have an artist, and there are 10 songs on it. In this case,
there will be still 10 records in that table, all having the album ID and
artistID identically. This is still duplicate data.

2. an album can have two or more different artists (like albums recorded
after some live concerts)

3. an album can belong to a main Artist, but there can be some songs which
was singed with another artist (like #Lionel Richie feat Enrique Iglesias #
To Love A Woman)

So it depends on how deep you want to go with data modeling, and what kind
of reports you'd like to get (for example, all albums with single artist, or
only songs of ABBA in collaboration with others, etc)

HTH,
Bogdan Zamfir
_______________________

Independent consultant

 

[Hank in KC]

Hi John,

Thanks for the help and suggestions.

The original flat file had 1438 unique records.

The new tables have 113 albums, 94 artists, and 1148 songs, all unique.
(After inspecting manually and fixing typos.)

I realize the original table is not very good, but wanted to maintain the
relationships between the field entries, to avoid setting them manually .
I'll next build resolver tables.

An anticipated problem will be adding new records using lookup lists and
dealing with the NotInList event.

I'm a novice, trying to learn Access from several books and helpful folks
like yourself.

Thanks,

Hank

 

[Hank in KC]

Hi Bogdan,

Thanks for the helpful suggestions. They are appreciated.

Since this is a hobby/learning project I won't be seeking services from a
consultant, but do thank you once again.

Hank

 

[John Vinson]

Hi John,

Thanks for the help and suggestions.

The original flat file had 1438 unique records.

The new tables have 113 albums, 94 artists, and 1148 songs, all unique.
(After inspecting manually and fixing typos.)

Sounds like quite a bit of work, and quite a collection!

I realize the original table is not very good, but wanted to maintain the
relationships between the field entries, to avoid setting them manually .
I'll next build resolver tables.

If the titles are unique you should be able to do this pretty readily.
It might indeed be best to do as you propose and add the three ID
fields to the "everything" table.

An anticipated problem will be adding new records using lookup lists and
dealing with the NotInList event.

Well... don't use Lookup fields in your tables; do it on Forms
instead. If you haven't investigated http://www.mvps.org/access check
it out - there's plenty of sample code there, including NotInList, and
links to other sites.

I'm a novice, trying to learn Access from several books and helpful folks
like yourself.

Glad to be of help, and good luck!

 

[Hank in KC]

John,

Thanks again for your kindness and support.

My thinking is to make two resolver tables - modified copies of the
"everything" table. After getting the primary keys back into the original
table delete all fields except AlbumID and SongID from one copy, renamed
tblAlbumSong, and all fields except SongID and ArtistID from the second,
renamed tblSongArtist.

I'm hoping these two resolver tables can be used with a query to display the
same info as the original flat file. At this point the flat file is
discarded.

Assuming the above to be true the next challenge comes with entering new
albums.

I envision using a form to enter a new album (CD now) title (primary key
autonumber), then a lookup table to a tblSong entry and a second lookup to
tblArtist. This is where I foresee the NotInList error: how to deal with
missing entries in the lookup table.

I'll dig into the link you suggested and see what comes from it.

Once again, thanks for your kindness and consideration.

Hank

 

[John Vinson]

Assuming the above to be true the next challenge comes with entering new
albums.

I envision using a form to enter a new album (CD now) title (primary key
autonumber), then a lookup table to a tblSong entry and a second lookup to
tblArtist. This is where I foresee the NotInList error: how to deal with
missing entries in the lookup table.

I would NOT use "lookup fields" - you can and should use lookup tables
in a Form Combo box. Just hammering the same concept again!

There will obviously be new songs on some new albums, probably more
often than reprises. But opening a popup form in the NotInList event
is really pretty simple, and the Access Web code will point you the
right direction.

I'll dig into the link you suggested and see what comes from it.

Once again, thanks for your kindness and consideration.

You're most welcome!

 

[Hank in KC]

Hi Again, John,

Have been unable to get the primary key values back into the flat table. I
constructed an update query for tblFlatTable.AlbumID

=(SELECT AlbumID FROM tblAlbums WHERE tblAlbums.AlbumTitle = FlatTable.Album
Title.

After attempting to run the query the update text became.

(SELECT AlbumID FROM tblAlbums WHERE
tblAlbums.AlbumTitle=FlatTable.AlbumTitle)

SQL view of the query is:

UPDATE tblFlatTable SET tblFlatTable.AlbumID = (SELECT AlbumID FROM
tblAlbums
WHERE tblAlbums.AlbumTitle=FlatTable.AlbumTitle);

I am being prompted for a parameter when attempting to run the query.

Any suggestions?

About the lookup fields ... I think I was referring to the lookup tables as
lookup fields. I got your message loud and clear.

Thanks again,

Hand

 

[John Vinson]

UPDATE tblFlatTable SET tblFlatTable.AlbumID = (SELECT AlbumID FROM
tblAlbums
WHERE tblAlbums.AlbumTitle=FlatTable.AlbumTitle);

I am being prompted for a parameter when attempting to run the query.

It's probably just my typo - FlatTable should be tblFlatTable in the
last line. What parameter are you being prompted for?

Of course you need to change all the table and field names to match
your own database (I don't know what those names are so just put in
something plausible).

Another syntax which should work if you have a unique Index on
AlbumTitle is

UPDATE tblFlatTable
INNER JOIN tblAlbums
ON tblAlbums.AlbumTitle = tblFlatTable.AlbumTitle
SET tblFlatTable.AlbumID = tblAlbums.AlbumID;