Re: Query Multiple Tables in Access 2002

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

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.
--

John W. Vinson [MVP]
.

Relevant Pages

  • RE: Filter Sum Nightmare
    ... Here's a copy of the final query. ... FROM (Songs AS S1) ... over to qualify. ... This returns all rows from Songs by a contestant where at least one row ...
    (microsoft.public.access.queries)
  • RE: Filter Sum Nightmare
    ... I think you can probably do that with a single query: ... FROM Songs AS S1 ... This returns all rows from Songs by a contestant where at least one row ... received a qualifying score or not. ...
    (microsoft.public.access.queries)
  • Re: [OT] large db question about no joins
    ... So I need to store the data in such a way that it is accessible before ... the query with the minimal amount of processing. ... In an relational database setting you would have a table for artists, ... people can comment on songs. ...
    (comp.lang.python)
  • RE: Filter Sum Nightmare
    ... FROM Songs AS S1 ... over to qualify. ... they've qualified twice by suming that calculated field in the query. ... This returns all rows from Songs by a contestant where at least one row ...
    (microsoft.public.access.queries)
  • Re: Queries not retrieving the required data
    ... The query can only retrieve some songs ... for some artists and no songs at all for other artists. ... Is there a way in which I can retrieve the info required? ...
    (microsoft.public.access.queries)