Re: Pulling a particular field from a table based on inputs on a f
- From: "Pat Hartman" <please no email@xxxxxxx>
- Date: Wed, 19 Dec 2007 09:00:20 -0500
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" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E8406B9-E6C2-46E4-B6F0-1C6984E790F9@xxxxxxxxxxxxxxxx
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" <Rog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:529E3D89-A418-4537-848D-51E6F3835B16@xxxxxxxxxxxxxxxx
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" <Rog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F631B9E3-7BC4-43A9-8162-C68A43171B79@xxxxxxxxxxxxxxxx
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.
.
- References:
- Re: Pulling a particular field from a table based on inputs on a form.
- From: Pat Hartman
- Re: Pulling a particular field from a table based on inputs on a f
- From: Rog
- Re: Pulling a particular field from a table based on inputs on a f
- From: Pat Hartman
- Re: Pulling a particular field from a table based on inputs on a f
- From: KARL DEWEY
- Re: Pulling a particular field from a table based on inputs on a form.
- Prev by Date: Re: Increment filed based on a a combo box.
- Next by Date: Re: Pulling a particular field from a table based on inputs on a f
- Previous by thread: Re: Pulling a particular field from a table based on inputs on a f
- Next by thread: Re: Pulling a particular field from a table based on inputs on a form.
- Index(es):
Relevant Pages
|
