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

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.

--

That reminds me of my husband yelling from the other room as I was trying to
learn the piano - sharp, sharp, flat, flat I never got past Liberace's
Big Note Song Book and I needed the cheater note names pasted to the keys.


"KARL DEWEY" wrote in message
...
12 - G#
--
KARL DEWEY
Build a little - Test a little


"Pat Hartman" wrote:

You need to define the Types - you would with your method also. You can
create the base type and then using append queries, copy the base and
shift
it one to the right by decrementing the OrdinalPosition. You need to
adjust
for wraparounds though since you want 1 to become 11 for each new set if
11
is the maximum number of notes.

INSERT INTO tblKey ( KeyName, OrdinalPosition, NoteName )
SELECT [Enter New Key Name] AS Expr1,
IIf([OrdinalPosition]=1,11,[OrdinalPosition]-1) AS Expr2, tblKey.NoteName
FROM tblKey;


tblKey KeyName OrdinalPosition NoteName
MAJ 1 A
MAJ 2 A#
MAJ 3 B
MAJ 4 C
MAJ 5 C#
MAJ 6 D
MAJ 7 D#
MAJ 8 E
MAJ 9 F
MAJ 10 F#
MAJ 11 G
MIN 1 A#
MIN 2 B
MIN 3 C
MIN 4 C#
MIN 5 D
MIN 6 D#
MIN 7 E
MIN 8 F
MIN 9 F#
MIN 10 G
MIN 11 A


"Rog" wrote in message
...
Pat:
If I read your solution correctly, it sounds like I will have to enter
every
key and every note for every key and every note patterrn for every
chord
for
all those keys. That's a lot of entering. I wanted the program to
select
the
correct pattern of notes based on the Type (MAJ, Minor, Diminished,
7th,
6th,
Maj 7th etc) for any chord. The pattern applies to any key; one just
has
to
be able to select the right notes for a given key.
If I have to enter all that information and the program just gives me a
nice
way to display it, it's not worth the time. Is this what you're
suggesting?
Help me out here, please.
Thanks.

"Pat Hartman" 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.


"Rog" wrote in message
...
I need to be able to pull specific fields from a table based on
inputs
in a
form. I have two Tables set up as follows - the first is called TYPE
and
the
second is NOTES:

There are only three records in this example, but there will be many
more
records for each when done. I want to be able to type MAJ and find
the
right
pattern in TYPE for example. (MAJ - 1 - 5 - 8) and then get the
corresponding
notes to that pattern from NOTES for a given key. See below.

TABLE 1: (TYPE) (Contains the numeric note patterns for each chord
type)
MAJ 1 5 8
MIN 1 4 8
7th 1 5 8 11
etc

TABLE 2: (NOTES)
1 2 3 4 5 6 7 8 9 10
11
12
A A# B C C# D D# E F F# G
G#
A# B C C# D D# E F F# G
G#
A

B C C# D D# E F F# G G# A
A#

Looking at the first row above with the letter "A" for the key of
"A":
If I want the notes for "A MAJ", the 1,5,8 pattern from Table 1,
would
yield
A,C#,E
If I want the notes for "A MIN", the 1,4,8, pattern would yield
A,C,E
etc.

Key ("A" in this case) and theTYPE (MAJ for example) would be
selected
on
a
form.
The resulting notes should also be in fields on the form.
Can anyone help with this one? It seems pretty straight forward,
but...
Thanks.

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.

--

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.

--

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.

--

Change the subform to continuous view rather than datasheet view. You can
adjust the height and background and turn off all the outlines so that it
just blends in. You can get rid of the scroll bars because you have a very
limited list. Just make the control large enough to show four rows of the
subform.

You may need to requery the subform after you change the key. Try -
Me.yoursubformcontrolname.Requery

"Rog" wrote in message
...
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.

--

Pat,
I ended up using this line to update on the fly ON EXIT. It works great...
finally!
DoCmd.Requery "QRY_CHORDSEL SUBFORM3"
I apologize to all for making this so painful. I learned a lot and I
appreciate everything EVERYONE did to get me to where I am with this.
Roger

"Pat Hartman" wrote:

Change the subform to continuous view rather than datasheet view. You can
adjust the height and background and turn off all the outlines so that it
just blends in. You can get rid of the scroll bars because you have a very
limited list. Just make the control large enough to show four rows of the
subform.

You may need to requery the subform after you change the key. Try -
Me.yoursubformcontrolname.Requery

"Rog" wrote in message
...
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.

--

Although the code works for you, it is not in the correct event. Each event
runs when a particular thing happens and so is best used for particular
things. The AfterUpdate event would be a better choice since it runs only
if the control/form is changed whereas the exit event will run any time you
tab into and then out of the control whether or not the value was changed.
This is a small thing but it is best to start learning correct techniques
early rather than having to undo years of bad habits.


"Rog" wrote in message
...
Pat,
I ended up using this line to update on the fly ON EXIT. It works great...
finally!
DoCmd.Requery "QRY_CHORDSEL SUBFORM3"
I apologize to all for making this so painful. I learned a lot and I
appreciate everything EVERYONE did to get me to where I am with this.
Roger

"Pat Hartman" wrote:

Change the subform to continuous view rather than datasheet view. You
can
adjust the height and background and turn off all the outlines so that it
just blends in. You can get rid of the scroll bars because you have a
very
limited list. Just make the control large enough to show four rows of
the
subform.

You may need to requery the subform after you change the key. Try -
Me.yoursubformcontrolname.Requery

"Rog" wrote in message
...
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.

--

You sound like a good teacher... Thank you for the advice and I will change
it. Your point is well taken.

Thanks again.

"Pat Hartman" wrote:

Although the code works for you, it is not in the correct event. Each event
runs when a particular thing happens and so is best used for particular
things. The AfterUpdate event would be a better choice since it runs only
if the control/form is changed whereas the exit event will run any time you
tab into and then out of the control whether or not the value was changed.
This is a small thing but it is best to start learning correct techniques
early rather than having to undo years of bad habits.


"Rog" wrote in message
...
Pat,
I ended up using this line to update on the fly ON EXIT. It works great...
finally!
DoCmd.Requery "QRY_CHORDSEL SUBFORM3"
I apologize to all for making this so painful. I learned a lot and I
appreciate everything EVERYONE did to get me to where I am with this.
Roger

"Pat Hartman" wrote:

Change the subform to continuous view rather than datasheet view. You
can
adjust the height and background and turn off all the outlines so that it
just blends in. You can get rid of the scroll bars because you have a
very
limited list. Just make the control large enough to show four rows of
the
subform.

You may need to requery the subform after you change the key. Try -
Me.yoursubformcontrolname.Requery

"Rog" wrote in message
...
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.

--

On Dec 19, 4:32 pm, "Pat Hartman" please no wrote:
If you have four fields on the form for the chord notes, you're back in your
spreadsheet mentality.

Not necessarily. I might have a table with elements address_line_1,
address_line_2 and address_line_3. I would say this is acceptable
because the data element is the mailing address and the address lines
are subatomic elements; spreadsheets are far from my mind.

Is a note in a chord a subatomic element? I'm not a domain expert
here.

At the very least, I think note_1, note_2 etc design could be
justified as 'denormalization to be able to formulate effective table
constraints'. You seem to be saying that a normalized structure
without the '4 notes etc' database constraints is an improvement but
I'm not so sure. I think we should be aiming to give the OP a
normalized structure without losing the constraints.

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.

In a relational database (arguably OT for the Access groups) the
'many' is always represented as a relvar. Subforms have nothing to do
with relational databases g! Database constraints do, so why not
suggest a constraint to limit OrdinalPosition to 4 or 11 or whatever?

Jamie.

--