Re: Query Multiple Tables in Access 2002


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?

"John W. Vinson" wrote:

On Sat, 7 Feb 2009 23:50:01 -0800, Josh Davis
<JoshDavis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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 spread***! 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 [MVP]

.