Problem retrieving data using queries in a simple (one table) data

[Zeenabu66]

I've been asked to create queries (for example all songs by an artist) in a
CD Collection database. The query can only retrieve some songs for some
artists and no songs at all for other artists. I've checked the query which
seems ok. I think the problem could be because it is a simple database (just
one table with about 50 columns and about 100,000 records. I tried to use
"Analyse --- Table" Tool but have not been successful. To avoid corrupting
the database, I've decided to get advice from you wonderful people out there
before doing anything else. Can I change this table into a relational table
for better analysis? There are many duplications, ie songs, artists, numbers,
year, etc. I think perhaps this is causing the problem.
Is there a way in which I can retrieve the info required through another
tool? Also, any advice on best practices in Normalisation would be
appreciated Deadlines are approaching fast so I'll be most grateful for
urgent advice.
Thanks

 

[Beetle]

I've been asked to create queries (for example all songs by an artist) in a
CD Collection database. The query can only retrieve some songs for some
artists and no songs at all for other artists. I've checked the query which
seems ok. I think the problem could be because it is a simple database (just
one table with about 50 columns and about 100,000 records. I tried to use
"Analyse --- Table" Tool but have not been successful. To avoid corrupting
the database, I've decided to get advice from you wonderful people out there
before doing anything else. Can I change this table into a relational table
for better analysis? There are many duplications, ie songs, artists, numbers,
year, etc. I think perhaps this is causing the problem.
Is there a way in which I can retrieve the info required through another
tool? Also, any advice on best practices in Normalisation would be
appreciated Deadlines are approaching fast so I'll be most grateful for
urgent advice.
Thanks

Sounds like your DB was not created properly in the first place.
Unfortunately, there's no magic button to convert your one large table
into a proper set of relational tables. You can do some research on
the subject at the following link;

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In the meantime, it's going to be difficult to get useful information
out of it the way it is now. There are many problems with table design
like this. One is that users won't always spell things the same way so
you might have one artists name with ten different spellings depending
on who input the data at the time, not to mention all the albums,
songs, etc. You can check your access help file for info on using the
LIKE operator. That way you could search for things without having to
have the exact spelling, but your still going to be in for some work
on this one.

HTH

 

[Zeenabu66]

Beetle,
Thank you for your valuable answer. I'll do my research on the site you've
provided. Considering the size (50 columns, 10,000 entries) of the database,
would you suggest creating new relational tables and copy/paste entries into
it a way forward?

 

[Beetle]

Something along those lines, but first you might want to narrow down the list
by doing a little spell checking first. For example, I might start by
creating a query that looks for all names like *Stones*. This would probably
give you a pretty complete list of all the occurences of The Rolling Stones
in the DB, accounting for different spellings (The Rolling Stones, Rolling
Sones, The Rollin Stones, The Stones, Stones, etc.). Then, depending on how
long of a list was returned, you could do some spelling correction, delete
duplicate records, etc. By duplicate records, I mean where ALL of the fields
are the same. You said you have 50 fields, so you'll need to check this
carefully. Try to clean it up as much as you can before you start to copy and
paste records to the new tables.

I would definitely recommend starting on a proper table design as soon as
possible. The longer you wait, the more records will be added to your current
DB, and the more difficult it will be to do. I could give you some
suggestions on your table design if you like but I would need to know a
little about the different types of *categories* you have. For example, maybe
your main categories are just Artist, Album, and Song. Or maybe you also have
Genres as well (Rock, Jazz, Classical, etc.). If you do have Genres, then can
an Artist belong to more than one Genre? This will make a difference in the
types of relationships that you will be using (One-to-Many, Many-to-Many).

 

[Zeenabu66]

Thank you Sean for your response. It is very much appreciated. I'll discuss
with my boss and come back to you with the info required for you to assist me
further.

 

[Zeenabu66]

Hi Bettle,
First of all, I must say how much I appreciate your help in this matter. It
has just given me faith in the human race again. Please find below
fieldnames=data type.

{ID=text, Access Nos=auto-number, Catalogue No=text, Song Title/Track
Name=text, Performer/Artist=text, Band/Group=text, Label=text, Master
Held On-text, Composer=text, Record Company=text, Genre=text,
Sub-Genre=text, Sub-Genre2=text, Related T=text, Writer/Publisher=text,
Length/Track Time=text, Exclusions=text, Territories For Digital Rights
World=yes/no, Location=text, Owned With=text, Matrix/Record Company
Number=text, Year=number, Cleaned=yes/no, Vocalist=text, Band Member
1=text, Band Member 2=text, Band Member 3=text, Band Member 4=text, Band
Member 5=text, Band Member 6=text, Band Member 7=text, Band Member 8=text,
Format=text, Note=memo, Note=memo2, Number of Tracks=number, Tracks
Number=number, Copyright Line=text, Scheduled Release Date=date/time,
Original Year Of Release=number, ISRC=text, P Line=text, Artwork
Available=yes/no, Full Artist Description=memo, Full Album
Description=memo}.

There is not much selection in the field property details. Mmost of them are
set to default, eg. no validation rule; required=no; zero length allowed; not
indexed, etc and no Primary Key. Also, the entries are, eg a Peggie Lee CD
with 20 songs will have her name entered 20 times in the Artist field; and a
CD reference eg CD0001 in Master field 20x along each song. At present, most
of the data are in about the first 10 fields but it is hoped that the other
fields would be completed. For instance, John Coltrane, a giant in the jazz
world played with everyone and everyone played with him. So he might turn up
under Performer on a CD and Band Member 6 on another.

I hope that the above is clear and that you will be able to assist.
AggieZ

 

[Peter Hibbs]

AggieZ,

As Beetle has said, you first need to tidy up your data (if necessary)
to make sure that any duplicate names and numbers in different records
are identical, SELECT DISTINCT queries can be useful to check this.

Then you need to decide how many tables you need (3 or 4 I would
guess from your description) and then which fields belong in which
tables. Then you need to copy the relevant data from your main table
into the various new tables which is tricky. However, if you are using
Access 2003 you can download the Excel to Access Converter Utility
program from this address :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

which should be able to do it for you in a few seconds. This program
cannot handle Memo type fields, however, so if those fields have more
than 255 characters in them you will need to find some other way of
importing that data. If you are using some other version of Access
post back and I may be able to help.

Peter Hibbs.

 

[Zeenabu66]

Thank you very much Peter, your advice is much appreciated . I'll check my
DB and use the links given to see a way forward with this project.

 

[Beetle]

Hello again AggieZ

I have some thoughts that might be helpful if you're going to take on the
task o redisigning your DB, however, I don't know if you're still checking
this tread. If you are, post back and I'll answer

 

[Zeenabu66]

Hi Beetle

Yes, yes, yes please. If it's helpful, I could post my email add so that
you can contact me (if permitted by rules of this on-line community,
obivously). Looking forward to hearing from you.

 

[Beetle]

Well, first things first I guess.

If this is something that needs to be completed in a short time frame, then
you might want to consider searching to see if someone has a pre-existing
music DB template that might work for you (perhaps with some slight
modification) or if there is an Access consultant in your area that may be
able to help.

On the other hand, if you have some time to develop it and you are the type
of person
(like myself) that enjoys a challenge, then welcome to the world of Access.
Be forewarned that Access has a steep learning curve. I first got into it a
few years ago when a relative was looking to create an inventory database for
their business. I suggested that Access might work well. They said "can you
do it?", to which I responded "Yea, I think so". Little did I know what I was
getting myself into. Luckily, they weren't in a big hurry.

You will probably want to get a book (maybe more than one) to help learn the
basics. Jeff Conrad has a list of good Access books on his website at;

http://www.accessmvp.com/JConrad/accessjunkie/resources.html

There are also plenty of experts out there who are willing to share their
knowledge with the rest of us mere mortals on their websites. In addition to
the afformentioned Jeff Conrad, here are some other links that you may find
very helpful;

http://allenbrowne.com/tips.html

http://www.mvps.org/access/

http://www.databasedev.co.uk/

Then there are the discussion groups like this one. There are plenty of
people who are willing to spend some time to help you solve a problem (WHY we
do this, however, is somewhat of a mystery). I have probably learned as much
from this group as I have form any other source.

As far as your DB goes, from the list of fields you posted, it looks like
you will need several tables. Some of them will be relatively small, with a
limited amount of records, that will just be used to look up a value (a
*lookup* table). You also appear to have some One-to-Many and some
Many-to-Many relationships. I can't tell you *exactly* how to set them up
but here are my initial thoughts. You'll need to tweak things and use your
own field names, etc. (table names are preceded with tbl, field names are
listed under the asterisks).

tblAlbums
*******
AlbumID (Primary Key)
GenreID (Foreign Key to tblGenres)
ArtistID (FK to tblArtists)
RecordCompanyID (FK to tbl RecordCompanies)
AlbumTitle
ReleaseDate
(other fields related to the Album)

tblSongs
*******
SongID (PK)
SongTitle
DateRecorded
TrackLength
(other fields rlated to the song)

tblAlbumSongs
***********
AlbumSongID (PK)
AlbumID (FK to tblAlbums)
SongID (FK to tblSongs)

tblGenres
*******
GenreID (PK)
GenreName
(other fields related to Genre)

tblSubGenre
*********
SubGenreID (PK)
GenreID (FK to tblGenres)
(other fields related to SubGenre)

tblRecordCompanies
***************
RecordCompanyID (PK)
RecordCompanyName
StreetAddress
City
State
ZipCode
(other fields related to record company)

tblArtists
*******
ArtistID (PK)
ArtistName
(Etc.)

tblMusicians
*********
MusicianID (PK)
LastName
FirstName
(Etc.)

tblBandMembers
************
BandMemberID (PK)
ArtistID (FK to tblArtists)
MusicianID (FK to tblMusicians)

tblBandPositions
************
PositionID (PK)
PositionName


Let's analyze some aspects of this table structure. A band can have many
musicians. Likewise a musician can be in more than one band (ie John
Coltrane), so you have a Many-to-Many relationship between Bands and
Musicians. In this example tblArtists holds information about the Album
Artist, wether it's a band or a solo artist. tblMusicians holds information
about individual musicians and tblBandMembers is used to define the
relationship. Getting back to your example about John Coltrane. You're right
in that he can be an Album Artist or a band member, or he could be both at
the same time. For one of his solo albums, his name would appear in
tblArtists as the Album Artist. In this case his name is more of a *title* so
it would appear in a single field as "John Coltrane". Then, when selecting
the band members from tblMusicians he would also appear there (after all, he
is a member of his own band). In this case his name is a *name* so it is
divided into LastName and FirstName

Likewise with Albums/Songs. An album has many songs (obviously), but a song
can also be on more than one album (original album, greatest hits album,
etc.) so that
MtoM relationship is defined by tblAlbumSongs.

The last table (tblBandPositions) might be an example of a *lookup* table.
It would hold a limited number of records like "Lead Singer", "Drummer",
"Bass Player", etc. and you would just select values from this table when
needed.

I would strongly suggest that you lay this out on paper before you start
actually creating any new tables. This will help you decide which fields
belong where and the types of relationships you will have. Also, when you
think you are ready to start creating tables, I would start with a new blank
DB. Create some tables the way you think they should be, put in a little bit
of *test data*, then create a few forms and queries to test if you are able
to extract and display data the way you want to.

This post is getting a little long winded I suppose, but here is some final
food for thought.

In most cases you will probably want to use AutoNumber as your Primary Key.
Don't get hung up (as many people seem to) on wether the PK increments or
sorts the way you think it should. PK's are generally "not for human
consumption", as the experts like to say. They don't need to make sense to
you, they only need to make sense to Access.

When you are designing your tables, watch for repeating fields. For example,
in your current table you have Band Member 1, Band Member 2, Band Member 3,
etc. If you catch yourself doing this when you are designing your new tables
- STOP. That is a definite clue that you need to re-think it.

Also, don't include fields that would store calculated values. For example,
in your current table you have a field for Number Of Tracks. This is a value
that could easily be calculated by having Access count the number of songs on
a particular album. When dealing with values like this, ask yourself if it is
something that can be calculated. If it is, then it should not be a field in
your table.

Don't work directly in the tables, except maybe to put in some test data at
the very beginning of the design process. Data input should be done with
forms. If you have experience with Excel (or another spreadsheet program),
don't make the mistake of thinking that Access is like a spreadsheet. While a
table may look similar to a spreadsheet at first, Access absolutely does NOT
work the way a spreadsheet does.

This is just my opinion, but when it comes to creating forms, I don't use
tables as the recordsource. I create a query based on the table, then use the
query as the recordsource of the form. I think queries are more flexible than
tables. It's easier to add criteria, sort records the way you want to, etc.

When you do get around to creating forms, don't worry too much about how the
form looks at first. Get all the necessary controls on the form and make sure
everything works the way you want it to, then start making it look how you
want. Trust me, it sucks to spend a lot of time making a form *look* just
right, only to find out that it doesn't *work* right.

Finally, if you get stuck on something specific, post a question in this
group and there should be someone who is willing to help. Just try to make
sure your questions are specific, clearly worded and you include as much info
as you can about your situation. You may notice, looking through some of the
posts, that alot of the questions that don't get answered are ones that don't
make any sense or ones that just say "my database isn't working, what's
wrong?" (or something similar).

Anyway, I hope this is helpful and good luck if you decide to jump into it.

 

[Zeenabu66]

Beetle,
Thank you so much for your detailed advice. I've so much from this e-mail
and that has given me the confidence to tackle this work.
Keep up the good work!