Creating a simple, searchable Database from the Excel files here?

[Fox Brewer]

Composers List to attach .xlsx

Hey guys, I apologise for, I'm sure, posting something really stupid.

I have this list of composers and the pieces they have composed beneath them. All I really want is a brutally simple database: ie. I type in string quartet, and I'm left, just with the name of the composer and the list of results underneath them which match that search.

In an ideal world, I would like to continue being able to add to this list of over time, and have it as something simple I can run while I'm doing my reasearch for these projects

Thanks in Advance for any help

F

[MrShorty]

When I have done this, I have transposed the data (so the list of composers is in column A and their pieces will be in columns B through ___. Then I use the autofilter feature (http://office.microsoft.com/en-us/ma...678.aspx?CTT=1) to bring up the desired composer.

Another way to arrange the data would be to have the data in two columns: composer name in column A and opus in column B. In this way, column A will contain multiple entries for each composer's name, but it may make the filtered list easier to read upon filtering.

In any case, for a "brutally simple database", I would almost certainly figure out how to do it using the autofilter feature.

[Fox Brewer]

First off,

Thank you for your reply.

I am however, a little confused... I understand and have tried experimenting with the autofiller feature, but this seems time consuming and seems
like I can only do one row at a time?

I just want to make it so that I can use a simple search function to bring up all the pieces related to what I have searched.

Am I being stupid (almost certainly) or have I missed something?

F

[MrShorty]

In sheet3 of the attached, I have shown a small subset of your data in a filterable list (Knussen and Anderson's works). Currently filtered by composer=Knussen and opus contains "orchestra". This list was created by manually copying and pasting, so creating the list like this should be easy (if tedious).

With a list like this, you can search the list by composer's name or names, and/or by something in the opus description. If you were to break the opus description into additional fields (maybe a field for date or voice/instrument or opus # or whatever information you wanted to include), you could further filter the list using those criteria. For example, you could search for "concertos" between "1970 and 1980" by composers whose names start with "K"

[Fox Brewer]

Hey,

Thanks against, I appreciate you helping me with this, and tacking the time out.

The problem is that this seems really brutal. Is there any more direct way I could find of searching the database, or do you think I would need a specific program?

Thanks in any case for your help, I appreciate it

[Fox Brewer]

Hey,

Thanks against, I appreciate you helping me with this, and tacking the time out.

The problem is that this seems really brutal. Is there any more direct way I could find of searching the database, or do you think I would need a specific program?

Thanks in any case for your help, I appreciate it

[MrShorty]

The problem is that this seems really brutal. Is there any more direct way I could find of searching the database, or do you think I would need a specific program?

Well, you did ask for "brutally simple" at the outset.

I'm not any kind of database guy. In my limited use, this is about the most direct way I know of searching a database. I would not be at all surprised if there are other database programs out there that may be able to work with your existing data and layout better -- but I do not have any experience with them.

Perhaps to help us understand exactly what you want, what do you find in this approach that is "too brutal" or undesirable? In what ways does it fail to do what you want?

[Fox Brewer]

I'm really sorry, I hope that didn't sounds unpleasant...

I would just like to have a composer, underneath it the list of works, and search bar that would search the works of all the composers listed, and then to be able to view them, under the heading of the composers catalogue...

Maybe this is impossible!

[MrShorty]

You're fine. I expect what you want is possible -- it is just a matter of figuring out the most brutally simple way to do it.

In my limited database experience, I am used to databases that are structured with records in rows and fields in columns. In your case, you have structured your database just the opposite -- with records going across in columns and fields going down in rows. My limited experience with database functions in Excel, is that Excel, too, prefers databases where records go down in rows and fields go across in columns. It just seems to me that a "brutally simple" database in Excel will follow this same convention, so I suggested that you try to rearrange your database to this convention.

Perhaps someone else with more database management experience will come along to suggest how to use the data tables you have.

[Pete_UK]

I agree with MrShorty that his data layout shown in Sheet3 would give you the simplest way of searching through the database. You could have another sheet on which you can select a composer from a drop-down menu of all composers and get a list of compositions beneath (like filtering onto another sheet).

However, you can achieve the same using the layout that you have, and I show this in the attached file. I have listed all your composers in column A of the ComposersList sheet, and put these in alphabetical order (although you have not been totally consistent with your naming convention), so that it is easier to select a particular composer from the list. This is done using the (yellow) cell B1 in the Selections sheet, and I have put this formula in cell A3:

=IF(INDEX(Sheet1!$A$1:$CH$1000,ROWS($1:3),MATCH($B$1,Sheet1!$A$1:$CH$1,0))="","",INDEX(Sheet1!$A$1:$CH$1000,ROWS($1:3),MATCH($B$1,Sheet1!$A$1:$CH$1,0)))

I've copied this down to row 1000, so that it will list up to that number of compositions (your maximum currently is 580 from Gustav Holst).

Now, just change the composer using the drop-down in B1, and the list will change automatically.

Hope this helps.

Pete

EDIT: sorry, it won't let me attach the file - I'll try again shortly.

EDIT2: still not allowing me to post the attachment

EDIT3: rebooted my PC and this time the attachment got through

[Fox Brewer]

Thanks again for taking the time out, this is great, definitely a good step towards what I wanted.

You guys have been wonderful, I am sorry to be a pain.

Have a great day/week/month/year

[Pete_UK]

Glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of post count), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete