Jazz CD database search help

[pringe]

Hi,

First post here, and first up I will admit to being very much a first time user, although with *some* experience of access and VB coding in Excel (from back at school!!!) and a fairly technical mind.

I am a jazz musician, and have tried to create a database of all the CDs / records that I own so that i can search for either a player or a song title and can see details of all albums that feature my search criteria. I thought this would be fairly easy, and although I have been able to create a query (and then a report to show this clearly) when searching for a player (i.e. search for "Bill Evans" under the field "Piano", the same can not be easily done for my song search, where I have a different field for each song (with a max of 21 fields available!). This would require me making a query for a song 21 times, with each query searching under a different of the 21 'song' fields.

There must be a way of doing this much more efficiently...I am envisaging having a search box coming up which would search all records for the criteria entered and then create a report showing all records containing matches.

Is this possible? I am fast discovering that there is MUCH more to Access than initially meets the eye, and if the answer would be too long to give then some guidance in the right direction would be great!

Thanks in advance,

Steve

** EDIT ** I tried to upload my project but sadly it was too large. I will try and reduce its size and re-attempt or will upload it to some webspace.

 

[mtairhead]

So, for every record, you have an "album name" and then 21 columns for songs? (Along with some other stuff, I assume.)

You need to find if a title exists in any of the 21 columns of any of your countless records?

If that's correct, then I would go for a union query. These are actually supposed to be used to combine two tables with similar structure. (For example, you can combine a "NYC Bands" table and a "Boston Bands" table to make a single query giving you information on both of those cities.

You can change the use of these queries, however, to combine all 21 columns into one column in a new query...

Here's some off-the-seat-of-my-pants SQL for you:

Select [album name],[song1]
From [table name]
Where [I assume you will have some criteria here from your search box?]


Union Select [album name],[song2]
From [table name]
Where [I assume you will have some criteria here from your search box?]


Union Select [album name],[song3]
From [table name]
Where [I assume you will have some criteria here from your search box?]

Continue this until you've gotten to [song21]. This will select all 21 columns of all your records, and turn it into a two column table, displaying the song and the album.

Now you only have one column to search instead of 21.

Andrew

 

[Ron_dK]

...where I have a different field for each song (with a max of 21 fields available!). This would require me making a query for a song 21 times, with each query searching under a different of the 21 'song' fields.

It sounds to me that your databse is not normalised. Do a search on this forum on normalisation or normalization and see if your dbase is structured to 1NF, 2NF etc.

Why don't you put the song name in one field ( e.g. memo field) . If you want to do a search , you'll only need one query.

btw there is another Bill Evans who is a great sax player

 

[pringe]

thanks for the replies, I will check out both of those suggestions.

I have attached a jpeg so you can see how the database is arranged.

rak.... i know , great player. I only mentioned bill evans as I was sure people would recognise the name! A bit OT but do you play? I saw the 'bebop' reference somewhere in your post!

Oh and RE your question about only having one song field. Hopefully from the picture you will see how I have arranged it but I didnt want it to be untidy and to involve lots of cursor scrolling along to see tracks written. Some of Oscar Peterson's albums have 20 odd tracks!!!!

Steve

 

[mtairhead]

I figured you did as much. Yea, there are probably better ways to organize that database, but if you've already put information in, there's no point in going back now.

A Union query would solve your current problem, Without doubt.

Andrew

 

[Ron_dK]

I have attached a jpeg so you can see how the database is arranged.

rak.... i know , great player. I only mentioned bill evans as I was sure people would recognise the name! A bit OT but do you play? I saw the 'bebop' reference somewhere in your post!

Steve,

I fully support what Pat Hartman is saying. You really need to normalise your dbase.
As Pat highlighted, there are many good examples of music collection databases.
A year ago I came across Wensoftware and downloaded their dbase
(mlsetup.zip). I modified it to my needs, but wanted to say that I found this a pretty good example of a well organised Dbase, especially the Audio track properties.
You can find it here :
http://www.download.com/3000-2141-10218576.html?tag=lst-0-5

...and yes I played a lot of the Real Book stuff, but currently I'm playing guitar in a Latin/Brazilian band. Mood swings ain't it

 

[pringe]

Wow. Downloaded an example from the Office Template website (thanks Pat) and it is amazing!!! I also looked here:

http://www.microsoft-accesssolutions.co.uk/database_normalization_basics.htm

and found lots of information on normalisation. Now I can see what you guys were talking about from following the example given, and this requires a LOT more thinking!

From my initial thoughts, you dont want a 'one to many' relationship in a table (i.e my having 21 track fields). So am I right in thinking I need a seperate record for each track (i.e. only one track field per record), which would allow me to also list different personnel. Then when searching I would be able to just search the 'track' field. This would require a LOT of typing!!!!!!

However, when searching for a particular player I would only want one record per album to be shown so that I dont see a result for every track in that album (I only want this as a reference so I can discover if I have any albums containing players that I wasn't aware of). I will investigate the template further but would this involve some VB coding or maybe somehow create another table that will just contain every different line-up for each album?

Thanks for your time guys,

Steve

PS Rak - will download your link when on a faster connection, thanks. Oh and nowt wrong with the Latin stuff...you into contemporary Jazz too? Kenny Garrett / EST / Jason Moran etc?

 

[Ron_dK]

PS Rak - will download your link when on a faster connection, thanks. Oh and nowt wrong with the Latin stuff...you into contemporary Jazz too? Kenny Garrett / EST / Jason Moran etc?

Yeah, a couple of years ago, we did some of the Steps Ahead/Brecker Brothers, Pat Metheny, Herbie Hancock and even Zappa stuff.
Not sure if that can be categorized as contemporary but nevertheless....