# Thread: Need: Search That Can Return Inversions of User Input

1. Registered User
Join Date
Sep 2003
Location
New Jersey
Posts
3

## Unanswered: Need: Search That Can Return Inversions of User Input

Example of Inversion of User input: User Input: ABCDG , Results Found: GDCBA, ABCGD

Hi,
I'm not a programmer, but I'm in the process of developing a website that I hope can be programmed from my MS Excel (with VB Macros) worksheets. The website will be for musician's that wish to search for chords and scales. The user inputs a series of notes, and matching chords and/or scales are found that contain those notes.
I have the Main Page with the Search box done, and the table that it will search, also near complete. I have no problem directing a macro to return results as follows:

User Search: A B C D
Results 1: Chord-xxx A B C D
Results 2: Chord-xx A B C D E
Results 3: Scale-x A B C D E F#

But it will not find D C B A (because the chord is inverted from the search). This is the critical issue. Sure, I could put into my table every possible inversion, but with scales that may contain 7 notes, the possible inversions are in the millions.

Ok, maybe I could create a macro that could invert the chords by some logical process. Then I place these inversions in the table on the same row of the chord that I want to be returned:
Chord-xxx DCBA - CDBA, CBDA, CBAD, ABCD, ACBD, and on and on and on (all possible inversions)............
The macro could then find an exact match in the row (in the on and on and on...... part), then be directed to go to the first column of that long, long, long row and return the information contained there. Bingo.
But to do that for searchable strings of 7 notes with all possible inversions I think I would need 7x7x7x7x7x7x7=5,764,801 inversions. Or is my math way off?

QUESTIONS:
1. Is there a logic pattern that I could use to create macros to create all possible inversions, or is there a better way? If there is a better way, please be specific if you could.

2. What programming tools would be best suited to get this thing working on the web, and how would it work, basically (or exactly), with inverted chords?

Basically, I use MS Excel and Photoshop. I want to be able to get as much done myself before calling (paying) for the services of a programmer.

Jim

2. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721

## Re: Need: Search That Can Return Inversions of User Input

Wow.. that's pretty beefy.. I don't know if this is even feasible in Excel... not that you should be using it to run a website anyways...

So my idea doesn't use Excel, but MS SQL Server which allows you to create stored procedures. Personally, I'd probably write a SP that takes an input (the chord being searched for), and creates a temporary table of all the possible inversions by going character by character through the chord, determining the inversions around that character then inserting it into the temp table, and moving to the next character. Then I'd join that temp table to the actual chords table to find matches.

3. Registered User
Join Date
Sep 2003
Location
New Jersey
Posts
3
Would you agree that it's a tradeoff between load time of the website, and the processing time of the searches? If it were a stored database, rather than an on the fly procedure, do you think the load time for the website would be unacceptable? And, if so, could the database still be loading after the webpage is already up? And do you think that allowing 7 note searches is too ambitious for an online website? Keep in mind, that I'm not a programmer, but rather an artist/musician/designer, so my dreams may be a little ahead of my senses.

4. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721
It's not really a stored database.. you would have your primary chords stored in the DB.. then when a search is executed, a stored procedure (SQL code stored server-side to optimize the speed of the query) would create a temporary table, generate the inversions, insert those inversions to the temp table then join it against your primary chords table to find matches.

For a few notes it sounds pretty simple.. but here's the thing.. a 7 note cord converted to all it's potential inversions is about 823,543 possibilities. To many for me to say that a temp table would be ideal.

My question is (not being a musician - and therefore, possibly dumb), if I did a chord "ABCDEF" and created all of it's inversions, would I not cover every possible 6 letter (A-F) chord that could be created? So you'd only have to populate your primary table once...

5. Registered User
Join Date
Sep 2003
Location
New Jersey
Posts
3
Seppuku,
The answer is no because it would not return any that contained #'s(sharps) or b's(flats), and most do. In this case it would return the C Major and A minor scales and any chords that match those notes would be in the key of C Major or A minor too, because only The C Major and A minor keys contain no #'s or b's.

6. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721
Originally posted by JBurke
Seppuku,
The answer is no because it would not return any that contained #'s(sharps) or b's(flats), and most do. In this case it would return the C Major and A minor scales and any chords that match those notes would be in the key of C Major or A minor too, because only The C Major and A minor keys contain no #'s or b's.
It's been so long since I looked at music... after all my years playing the trumpet, and now I can't remember anything from those days..

Without being able to spend more time remembering music, this may be beyond me at this point... sorry!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•