Pulling a particular field from a table based on inputs on a form.

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.

--