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

 

[Pat Hartman]

Before you go any further - STOP. You really need to normalize the tables. For one thing you haven't taken into consideration the fact that EACH tract could have different artists. There is a very basic template that you can download (http://office.microsoft.com/en-us/templates/TC010186291033.aspx). I don't like this structure because it still isn't normalized properly but it is MUCH better than what you have so far and it will give you something to think about.

 

[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?

 

[Pat Hartman]

When the light goes on, it is a wonderful thing Keep in mind that although the template from Microsoft is light years better than what you currently have, it is still not even close to being properly normalized. You'll have to make lots of changes to it but it is a start and shows you the idea we are driving at. Basically any thing that can be different for each track, MUST be stored in the track table. The typing isn't all that bad because you can create an artists table with the names of the most common artists (you will need to add to this list as you need to add new people to your database) which will allow you to create comboboxes from which you pick the artist.

When you get better at building forms, you can write code to copy data from the previous record. For the time being, cntl-quote will copy field by field from the previous record.

However, when searching for a particular player I would only want one record per album to be shown

this is not a problem even when the play occurs 21 times on the album. You control this in your query.

Believe it or not, if you learn how to build queries with the query builder and learn how to properly control the properties of all objects, you will need very little actual VBA code to create your database and possibly none. Stick with bound forms/Reports (forms/reports that use a query or table as their RecordSource", define your relationships in the relationship window and enforce referential integrity and you're set to go.

 

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