If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Pulling a particular field from a table based on inputs on a f
I hate to say this on such an interesting discussion (and also admit that I
haven't fully absorbed what's in all 27 postings) but, may I suggest the following 30,000 ft. view idea. But I know both music and databases and math. Chords are basically mathematical formulas: note #1 = ? note #2 = ? note #3 = ? note #4 = ? except that the inputs and ouputs are translated from and to other terminology (Scales, Chord types and notes) And there are a lot of different simple equations. A table can be used to store the equation for each cord And in databases you don't store the results of equations, your execute them at the time that you want to see the data. Sincerely, Fred "Rog" wrote: Understood... I'm doing that now. The problem is that for some reason I cannot get the resulting output to update the subform on the fly - the result initially shows up in the fields in the subform like it's supposed to, but if I change the key for exmple, they will only update when I go out of the form and back in. - not when I make the change. Also the subform continues to look like a spreadsheet format - that is, even if I change the subform so that the fields are pretty and colorful, it still comes out looking like an excel spreadsheet when I execute the query. Why? I'm getting there, but boy is this grueling! Thanks again. "Pat Hartman" wrote: If you have four fields on the form for the chord notes, you're back in your spreadsheet mentality. When working with a relational database, when you have more than one of something (notes in a chord), you have many and "many" is best represented as a list with a subform. You can make the subform invisible so that it blends into the form so the user will never know he's working with a subform. "Rog" wrote in message ... Ditto Jamie! I do not understand the term "make an edit". However, the good news is that I was able to make the queries work with the query as it was shown. I have to run the query directly - not from a form though. The trouble is that it obviously will not appear on the form. I need to create a standard form with the key and chord type ("A" and "MAJ" for example) entered in separate combo boxes and then four fields on the same form would be filled in with the appropriate notes; that's what I'm trying now. There doesn't appear to be an easy way to do this either, but I just started, so we'll see. If you folks have a suggestion, I'm open! Thanks to all who responded. I'm sure glad you're there. I'd like to find a way to learn how to do this stuff without using the school of hard knocks! It's painful. "Jamie Collins" wrote: On Dec 18, 6:54 pm, "Pat Hartman" please no wrote: You have a many-to-many relationship that you are attempting to flatten which will in fact make your work harder rather than easier. tblKey KeyName (pk fld1) OrdinalPosition (pk fld2) NoteName tblChord ChordName (pk fld1) OrdinalPosition (pk fld2) This structure allows you to define as many keys as you want with as many notes as you want. It also allows you to define as many chords as you want with as many notes as you want. Joining the tblChord to tblKey on OrdinalPosition with a variable KeyName - gives you the actual notes for that Chord in whatever key you select. My way results in ONE simple query (which takes KeyName as an argument) and that you can crosstab to show the notes of the chord horizontally. Your way results in learning VBA and writing many more queries. To manage this structure, you will use subforms with lists rather than main forms with hardcoded slots for notes for keys and notes for chords. I am not a musician and we have just exhausted my knowledge of music. In the real world, I'm sure you are looking at fixed instances such as 11 notes in a key and 3 or 4 notes in a chord but even with fixed limits, the "many" aspect of a relational schema will make life easier if you go with it. If the number of notes in a key is fixed, make an edit that limits the value of OrdinalPosition to that maximum. It seems to me that the OP has a used a "spreadsheet" (not my term) approach to be able to enforce business rules such as "a maximum of 4 notes for any one chord Type". This being the 'tables' group, how about suggesting a table constraint to enforce the stated business rules using your suggested structure? Without this it seems to me that you are encouraging the OP to take steps in the wrong direction i.e. removing integrity constraints from the tables. If *I* haven't understood what your instruction "make an edit" means then it could be the OP hasn't either TIA. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|