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

[Rog]

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.

 

[KARL DEWEY]

I thought that you went through this a couple of days ago.
You have "MAJ 1 5 8" but do not tell how many fields
and the names of those fields to store the data. What are the field names?
Are they number or text fields?

How are the records in table one related to table two?
What are they supposed to correspond with what?

 

[Dale Fye]

Rog,

I am by no means a musician. However, I can tell you that you have your
data structures setup incorrectly. You are setting it up like spreadsheets,
and doing what you want to do will be extremely difficult with this
structure. Recommend you convert your data to:

tbl_Type (fields, Type, NoteVal)
Type NoteVal
MAJ 1
MAJ 5
MAJ 8
MIN 1
MIN 4
MIN 8
7th 1
7th 5
7th 8
7th 11

tbl_Notes (fields Key, NoteVal, Note)
Key NoteVal Note
A 1 A
A 2 A#
A 3 B
A 4 C
A 5 C#
....

Once you have this struture, you can add two combo boxes to a form, one to
select the Type, the other to select the Key. Then, to get the notes that
belong to that Type/Key combination, you would write a query that looks like:

SELECT tbl_Notes.NoteVal, tbl_Notes.Note
FROM tbl_Type INNER JOIN tbl_Notes
ON tbl_Type.NoteVal = tbl_Notes.NoteVal
WHERE tbl_Type.Type = Forms!yourFormName.cbo_Type
AND tbl_Notes.Key = Forms!yourFormName.cbo_Key

Hope this helps
Dale

 

[Pat Hartman]

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]

I had been working with one person and it came down to "good luck" so I
thought I'd try again.

I'm not sure how to explain this any better, but I will try.
There can only be a maximum of 4 notes for any one chord Type; most will
only have 3 notes per chord type. So a MAJOR chord will have 3 notes in any
KEY and a 7th will have 4 in any KEY. So the TYPE plus the number of notes
would make a total number of 5 fields for Table 1.

The two tables are set up as I envisioned them. The only difference is the
total number of records. I only gave 3 to show what I need. The names of the
fields in Table 1 can be "TYPE" , "FIRST", "SECOND" etc. See below - I show
it there.
The names of the fields in Table 2 are "1", "2", "3" etc. Is this the best
way, I dunno... yet.
The numbers are number fields and the text are text fields.

"How are the records in table one related to table two?"
Table 1 contains the note patterns that would be used to pull the notes from
Table 2. That is, selectiing a TYPE in Table 1 pulls the numeric pattern for
that chord type (MAJ, MINOR etc.). Selecting MAJ selects pattern 1,5,8.

Selecting a letter from the first column in Table 2 corresponds to a key -
C, D etc. The idea is to pull the letters (notes) from Table 2 that
correspond to the numeric pattern created by table 1. So for "C" 1,5,8 would
result in C,E,G.

"What are they supposed to correspond with what?"
When the user wants to know "What notes are in a C MAJ chord?", the result
will give him C,E,G from the numeric pattern 1,5,8 when looking at the Key of
C row because he selected (or typed) MAJ and C into fields on a form. In this
case, the KEY is actually the letter in the first column of Table 2. The
letters under 1,5, and 8 (Obtained from selecting MAJ out of Table 1)
correspond to the letters C,E,G in table 2.

I hope that helps. I appreciate your help!

I thought that you went through this a couple of days ago.
You have "MAJ 1 5 8" but do not tell how many fields
and the names of those fields to store the data. What are the field names?
Are they number or text fields?

How are the records in table one related to table two?
What are they supposed to correspond with what?

TYPE__FIRST SECOND THIRD FORTH
MAJ 1 5 8

 

[Rog]

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.

 

[Dale Fye]

No, we are not recommending that you create a structure that requires you to
define every combination of key/chord. You define the Chord by the Name
(MAJ, MIN, ...) and the OrdinalPosition of the notes in the cord. You define
the keys by listing the ordinal positions and the appropriate notes.

I used the "tables" you originally gave us, but normalized them (lots of
rows, fewer columns, instead of the few rows, many columns format that you
had). In Pat's example, he uses the field OrdinalPosition, I used NoteVal.

Actually, doing it this way, you could very easily create the table of every
combination of key and chord, by just leaving out the where clause that I
have in the SELECT statement in my original post.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[KARL DEWEY]

I concur with all Pat and Dale said.Just use append queries from what you already have.

Append TYPE & FIRST, TYPE & SECOND, TYPE & THIRD, etc

 

[Pat Hartman]

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

 

[KARL DEWEY]

12 - G#
--
KARL DEWEY
Build a little - Test a little

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

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.

 

[Jamie Collins]

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 Hartman]

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.

12 - G#
--
KARL DEWEY
Build a little - Test a little

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

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.

:

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.


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.

 

[Rog]

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.

 

[Pat Hartman]

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]

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]

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

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.

 

[Rog]

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]

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]

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

Thanks again.

 

[Jamie Collins]

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.

--