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

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.

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?

--
KARL DEWEY
Build a little - Test a little


"Rog" wrote:

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,

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

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

email address is invalid
Please reply to newsgroup only.



"Rog" wrote:

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.

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.

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!

"KARL DEWEY" wrote:

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?

--
KARL DEWEY
Build a little - Test a little


"Rog" wrote:

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)

TYPE__FIRST SECOND THIRD FORTH
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#
C C# D D# E F F# G G# A A# B

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.

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.

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.



"Rog" wrote:

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.

I concur with all Pat and Dale said.
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.
Just use append queries from what you already have.

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

--
KARL DEWEY
Build a little - Test a little


"Rog" wrote:

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.

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.

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.