Query Multiple Tables in Access 2002

[Josh Davis]

I am and have already set up multiple tables to keep up with my HUGE library
of songs that are on my computer. What I need help with is I know there are
duplicates of some tracks, especially on some of my own created mixed CDs as
well as a few erroneous or tracks that have nothing on them or were just
severely corrupted. I was trying to query all my tables since each table
contains one CD in my collection to identify the duplicates or erroneous
tracks. However, when i tried to do that in Design View there was over ten
tables in the query which didn't seem like it would either work or return the
desired results. Am I trying to set the query up wrong? Is there a faster &
more efficient way to set it all up and eradicate the unnecessary tracks?
Also, I need the database to help me identifying "strays" or songs that seems
to get put in the wrong place and also make it to where I can easily find out
how many songs of a particular artist I have or how many total songs I have?
You know just be able to quickly and effectively find out what i need to know
is the least amount of time. Any light you could shed on this would be
greatly appreciated.

 

[John W. Vinson]

I am and have already set up multiple tables to keep up with my HUGE library
of songs that are on my computer.

What's your definition of "huge"? An Access table with 20,000,000 records is
getting huge. 100,000 is quite modest. If your collection has a hundred
thousand songs I'm impressed.

What I need help with is I know there are
duplicates of some tracks, especially on some of my own created mixed CDs as
well as a few erroneous or tracks that have nothing on them or were just
severely corrupted. I was trying to query all my tables since each table
contains one CD in my collection to identify the duplicates or erroneous
tracks.

Then your database design IS WRONG. To track tracks, you need *two* tables,
not hundreds:

CDs
CDID
<various fields about the CD as a whole, e.g. title, date issued, ...>

Tracks
CDID <link to CDID in CDs>
TrackNo
Title

You'll also want tables for Artists, artist on track, etc. - but you will
certainly NOT have a table for each CD.

However, when i tried to do that in Design View there was over ten
tables in the query which didn't seem like it would either work or return the
desired results. Am I trying to set the query up wrong?

Probably... but when you don't post any details of how you created the query
it's more than a bit hard to speculate in what WAY it's wrong (other than the
concern stated above).

Is there a faster &
more efficient way to set it all up and eradicate the unnecessary tracks?
Also, I need the database to help me identifying "strays" or songs that seems
to get put in the wrong place and also make it to where I can easily find out
how many songs of a particular artist I have or how many total songs I have?
You know just be able to quickly and effectively find out what i need to know
is the least amount of time. Any light you could shed on this would be
greatly appreciated.

A properly normalized design will make these questions much easier to address.

 

[Josh Davis]

Okay, maybe HUGE is the wrong word more like pretty big collection. Like i
would say over 1,000. The tables for each album have only "Track #:",
"Artist:", "Song:", & "Duration" as the fields The name of the Album and the
Album Artist is in the name of the table. What would the name of the two
tables be, the field names of each, and the Primary Key? The query i was
talking about had all the albums in it cause what i was trying to do was put
in say a track number. a artist name, and the song title and then it tell me
how many instances of that same song are there in my collection, in order to
remove the duplicates within it. Any thing else you need to know?

 

[John W. Vinson]

Okay, maybe HUGE is the wrong word more like pretty big collection. Like i
would say over 1,000. The tables for each album have only "Track #:",
"Artist:", "Song:", & "Duration" as the fields The name of the Album and the
Album Artist is in the name of the table. What would the name of the two
tables be, the field names of each, and the Primary Key?

tblAlbums
AlbumID <perhaps an autonumber; I don't know if there's an industry standard
identifier, I couldn't identify one on a couple of CDs I looked at. This would
be the Primary Key of the table>
AlbumTitle
Studio
<perhaps other information about the album as a thing in itself>

tblTracks
TrackID <Autonumber, unique primary key>
AlbumID <link to tblAlbums.AlbumID>
TrackNo <integer, 1 - number of tracks>
< put a unique Index on the combination of AlbumID and TrackNo>
Song <title of the track>
Duration <integer seconds, don't use a Date/Time field>
BandID <I'm assuming that there won't be two bands on one track>

Artists
ArtistID <autonumber primary key>
LastName
FirstName
<other biographical data as needed>

Bands
BandID <autonumber primary key>
BandName
<other info about the band as needed>

Performers
TrackID <link to Tracks>
PerformerNo <integer, default 1, one value for each performer on the track>
ArtistID

This allows for duets, trios, and larger ensembles on a track.

The query i was
talking about had all the albums in it cause what i was trying to do was put
in say a track number. a artist name, and the song title and then it tell me
how many instances of that same song are there in my collection, in order to
remove the duplicates within it. Any thing else you need to know?

With your "spreadsheet" structure you would need a MASSIVE (probably too big
to handle) UNION query. A Join query would certainly not work. You'll probably
need the big UNION query - or several of them - to migrate your multiple
individual-album tables into the normalized structure.

 

[Josh Davis]

How do i set up that UNION query?

tblAlbums
AlbumID <perhaps an autonumber; I don't know if there's an industry standard
identifier, I couldn't identify one on a couple of CDs I looked at. This would
be the Primary Key of the table>
AlbumTitle
Studio
<perhaps other information about the album as a thing in itself>

tblTracks
TrackID <Autonumber, unique primary key>
AlbumID <link to tblAlbums.AlbumID>
TrackNo <integer, 1 - number of tracks>
< put a unique Index on the combination of AlbumID and TrackNo>
Song <title of the track>
Duration <integer seconds, don't use a Date/Time field>
BandID <I'm assuming that there won't be two bands on one track>

Artists
ArtistID <autonumber primary key>
LastName
FirstName
<other biographical data as needed>

Bands
BandID <autonumber primary key>
BandName
<other info about the band as needed>

Performers
TrackID <link to Tracks>
PerformerNo <integer, default 1, one value for each performer on the track>
ArtistID

This allows for duets, trios, and larger ensembles on a track.


With your "spreadsheet" structure you would need a MASSIVE (probably too big
to handle) UNION query. A Join query would certainly not work. You'll probably
need the big UNION query - or several of them - to migrate your multiple
individual-album tables into the normalized structure.

 

[pietlinden]

How do i set up that UNION query?

I'll give you a hint...

open a query in design view. Hit the button on the top left of the
grid, the one that looks like a triangle. See the option that says
SQL on it? Choose that one.

Basic syntax for a union query:

SELECT field1, field2, field3
FROM table1
UNION [ALL]
SELECT field1, field2, field3
FROM table2;

 

[John W. Vinson]

How do i set up that UNION query?

Press F1. Select Search. Type "UNION" in the box.

The help topic is pretty good.

 

[Josh Davis]

Okay, a few more clarification questions:

1. The table names are as follows : "Albums", "Tracks", "Artists", "Bands",
"Performers" correct?

2. If the names are correct, is it necessary to have: "Bands", "Performers",
as well as "Artists"?

3. Can the tables' field names have spaces or no?

4. How do I link tables together?

5. What does the "tblAlbums.AlbumID" mean? Is that part of the code used in
SQL?

6. On "TrackNo <integer, 1 - number of tracks>" is that claiming that i
will only have 1 track number?

 

[John W. Vinson]

Okay, a few more clarification questions:

1. The table names are as follows : "Albums", "Tracks", "Artists", "Bands",
"Performers" correct?

That's my suggestion; your table names can be anything you choose. I'd
recommend using only letters, digits and underscores, no punctuation and no
spaces, but that's just a recommendation, not essential. See below.

2. If the names are correct, is it necessary to have: "Bands", "Performers",
as well as "Artists"?

You could lump Bands in with Artists if you don't want to track (say) John,
Paul, George and Ringo individually as well as tracking The Beatles (damn, I'm
showing my age here...)

3. Can the tables' field names have spaces or no?

They can, but it's not necessarily a good idea. Doing so requires that you
always reference the name in [square brackets] so the space isn't seen as a
division between two names, and using spaces can make it harder to upsize to
SQL/Server or other platforms.

4. How do I link tables together?

Using Queries joining on key fields.

5. What does the "tblAlbums.AlbumID" mean? Is that part of the code used in
SQL?

tblAlbums is the name of the table (replace with Albums or with the actual
name of your table); AlbumID is the name of the field in tblAlbums which is
the primary key of that table. Again... you can name the fiedl whatever you
like.

6. On "TrackNo <integer, 1 - number of tracks>" is that claiming that i
will only have 1 track number?

Of course not. Only one track number *FIELD*, yes. But it is saying that each
track *must be in a separate record* in the Tracks table. The album Blonde On
Blonde by Bob Dylan has 14 tracks; there'd be one record in Albums (let's say
that's AlbumID 214), and fourteen records in Tracks, all with AlbumID 214,
Trackno 1 for "Rainy Day Women", 2 for "Pledging my Time" and so on. You're
using a relational database, not a spreadsheet! DON'T fall into the assumption
that everything must be jammed into a single record in a table. Ordinarily it
*won't* - information will be stored in multiple tables, as many as are
logically needed to contain it.

I may have posted this before but check out some of these resources,
especially Crystal's tutorial:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Josh Davis]

Just a few questions on the tables' structure.

1. In the Performers Table you did not mention what the primary key should
be and what Data Type will it be?

2. On the Tracks Table, when you are choosing the Data Type for AlbumID
what would that Data Type be?

3. Again in Tracks Table and all other tables, shouldn't all the ID numbers
be included on all tables?

4. On the Albums Table, shouldn't Studio have it's own table thus
establishing a "StudioID" to be placed on all tables?

5. It just seems that logically, to place the ID numbers for the database's
unique fields in each table would seem to make it easier on the database when
referring to that id on all the tables correct?

Okay, a few more clarification questions:

1. The table names are as follows : "Albums", "Tracks", "Artists", "Bands",
"Performers" correct?

That's my suggestion; your table names can be anything you choose. I'd
recommend using only letters, digits and underscores, no punctuation and no
spaces, but that's just a recommendation, not essential. See below.

2. If the names are correct, is it necessary to have: "Bands", "Performers",
as well as "Artists"?

You could lump Bands in with Artists if you don't want to track (say) John,
Paul, George and Ringo individually as well as tracking The Beatles (damn, I'm
showing my age here...)

3. Can the tables' field names have spaces or no?

They can, but it's not necessarily a good idea. Doing so requires that you
always reference the name in [square brackets] so the space isn't seen as a
division between two names, and using spaces can make it harder to upsize to
SQL/Server or other platforms.

4. How do I link tables together?

Using Queries joining on key fields.

5. What does the "tblAlbums.AlbumID" mean? Is that part of the code used in
SQL?

tblAlbums is the name of the table (replace with Albums or with the actual
name of your table); AlbumID is the name of the field in tblAlbums which is
the primary key of that table. Again... you can name the fiedl whatever you
like.

6. On "TrackNo <integer, 1 - number of tracks>" is that claiming that i
will only have 1 track number?

Of course not. Only one track number *FIELD*, yes. But it is saying that each
track *must be in a separate record* in the Tracks table. The album Blonde On
Blonde by Bob Dylan has 14 tracks; there'd be one record in Albums (let's say
that's AlbumID 214), and fourteen records in Tracks, all with AlbumID 214,
Trackno 1 for "Rainy Day Women", 2 for "Pledging my Time" and so on. You're
using a relational database, not a spreadsheet! DON'T fall into the assumption
that everything must be jammed into a single record in a table. Ordinarily it
*won't* - information will be stored in multiple tables, as many as are
logically needed to contain it.

I may have posted this before but check out some of these resources,
especially Crystal's tutorial:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[John W. Vinson]

Just a few questions on the tables' structure.

1. In the Performers Table you did not mention what the primary key should
be and what Data Type will it be?

If you're not relating it to any further tables, ctrl-click the TrackID and
ArtistID fields and then click the Key icon to make it a joint, two-field
primary key. This will allow an Track to have multiple Artists (duets, trios),
and an Artist to appear on multiple Tracks, but will prevent having duplicates
of the combination.

2. On the Tracks Table, when you are choosing the Data Type for AlbumID
what would that Data Type be?

If AlbumID is an Autonumber, Number... Long Integer.

3. Again in Tracks Table and all other tables, shouldn't all the ID numbers
be included on all tables?

Certainly NOT. Each table should contain only the data pertinent to that
table. An Album will have many tracks, and probably many artists, so there
should not be a TrackID or an ArtistID in the Albums table.

4. On the Albums Table, shouldn't Studio have it's own table thus
establishing a "StudioID" to be placed on all tables?

You can certainly have a table of Studios; I don't know the data well enough
to say whether an Album is always from a single studio, or whether you need to
allow for an Album (perhaps a compilation album, or a home-built item) to have
tracks from several studios. If the former put a StudioID field in the Albums
table; if the latter, in the Tracks table.

5. It just seems that logically, to place the ID numbers for the database's
unique fields in each table would seem to make it easier on the database when
referring to that id on all the tables correct?

No. That is NOT correct. Putting fields into a table which are not relevant to
that table's information content is neither necessary nor beneficial!