Pulling particular fields from a record

  • Thread starter Thread starter Rog
  • Start date Start date
R

Rog

I have two DB set up as follows - the first is called TYPE and the second is
NOTES:
There are only two 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. See below.

(TYPE)
MAJ 1 5 8
MIN 1 4 8

(NOTES)
1 2 3 4 5 6 7 8
A A# B C C# D D# E F G
C C# D D# E F F# G G# A

I need to be able to enter "C" in one field and "MAJ" in another on a form
and come up with "C" "E" "G"; A in one field and MIN in the other and
get "A" "C" "E", but I cannot figure out how to grab only certain fields
in a given record. Thoughts?
 
Rog,

I hope you mean tables instead of "DB" in your first line. Here's what I
would suggest. Name the tables as you like.

Table 1: Chords

ChordID: Autonumber
ChordName: Text
ChordType: Combo box - Value List set to 1;"Major";2;"Minor"
set Bound Column = 1; Format = 0; 1"

Fill the table with your chord names from A Major through G# Minor.

Table 2: Notes

NoteID: Autonumber
NoteName: Text

Fill the table with the notes.

Table 3: ChordCombinations
ChordID: Number (NOT autonumber)
NFirst: Number
NThird: Number
NFifth: Number

You may want to add other fields like NSecond, NSeventh in case you expand
your chord offerings to Augment, Diminished, etc. I am using the N in the
field name to denote "Note" as using the ordinal number might cause problems
in other areas of Access (especially using First).

Also, depending on how you want your data displayed and in what medium
(table view or form view), you can set those ordinal fields to either display
the ID or the text description. I usually go with the IDs as my tables are
typically in the background and I let my forms do the work to display text
description.

For each of the ordinal fields, set to combo box, based on Table/Query
Notes, Bound column 1, format 0; .5".

That should get you started!
 
Yes indeed... you are correct. they are tables... slip of the finger. I'll
give it a try and let you know. Thanks for responding...
 
If I understand you correctly, you want me to me to input all the keys with
every chord type, so I would have to input for example, A - MAJ, MIN, AUG,
DIM, 7th, 6th etc, A# - MAJ, MIN, AUG, 7th, 6th etc, B - etc. in the table
called CHORDS? If this is the case, I'm confused. The idea was to use the
common numeric patterns for each key to get the TYPE of chords wanted. Could
you please explain. If my understanding is correct, I don't see the advantage
of doing this. It would seem I should just take the time and enter every key
(A,A#,B,C,C# etc) and input all their corresponding TYPES (MAJ, MIN, AUG,
etc) and make a simple look up. I do appreciate the help, but I think I'm
missing something. Thanks.
 
Before I get into too much detail, do you intend to have more than just the
Major and Minor options for chords? If so, I'll suggest a different set up.
 
Yes, There can be up to about 12 different patterns - DIM, 6th, MAJ 7th, MIN
7th, 13th, etc...
 
Okay, here's what I would suggest to start. You'll have to modify probably
depending on what the overall need is and where you are going from here. I
approached it as the tables would be background and all entry and display
would be performed through forms.

(Tables name st_ are static tables, i.e., they are base tables containing
information which will remain constant for the most part)

Table: st_ChordTypes (enter your chord patterns, as you called them)
ChordTypeID: Autonumber (PK)
ChordTypeDesc: Text (you probably don't need a field size larger than 50)

Table: st_Notes
NoteID: Autonumber (PK)
NoteName: Text (I set field length as 2 but you can set for larger if you
need to deal with things like A-flat flat; my data entry consisted of Ab for
A-flat and A# for A-sharp. You can easily add a third text field so you get
both: one field for note name (Ab) and one field for note description
(A-flat))

Table: Chords
ChordID: Autonumber (PK)
NoteID: Number (set Lookup tab to ComboBox, Table/Query, RowSource=st_notes,
BoundColumn=1, ColumnCount=2, ColWidths = 0;.5;ListRows=25;LimitToList = Yes)
ChordTypeID: Number (set Lookup tab to ComboBox, Table/Query,
RowSource=st_chords, BoundColumn=1, ColumnCount=2, ColWidths =
0;1";ListRows=8;LimitToList = Yes)
ChordName: Text (set Field Size to 25-50 to cover)

I set up Chords in this manner so that you could easily write queries to
gather chords by note or by chord type without have to do a text search on
the chord name. In the table, ChordName is an open text box - I would use a
form to populate this field based on the selection of the Note and Chord Type.

Table: ChordCombinations
There is no primary key for this table.
ChordID: Number (set Lookup tab to ComboBox, Table/Query, RowSource = SELECT
Chords.ChordID, Chords.ChordName FROM Chords; BoundCol=1, ColCount=2,
ColWidths= 0;1, LimitToList=Yes)
NoteID: Number (set Lookup tab to ComboBox, Table/Query, RowSource =
st_notes, BoundCol=1, ColCount=2, ColWidths= 0;.5, LimitToList=Yes)
NoteRank: Number

I used NoteRank so you can order the notes appropriately for a chord
(typical major triad vs inverted) without having to worry about entering in
the notes in the correct order.

I've created this in a database file which I'd be happy to send to you -
just let me know to where. It will give you an idea on basic set up.
Otherwise, try your hand at it and see where it takes you.

Have fun!

Ross
 
Well, I appreciate your time on this... I really do, but I fear there are
some gaps here. I set up all the tables as you described, except I don't
understand two things:
1) Is "st_notes" supposed to have one note (e.g. A) as one record in the
table and "A#" as a second record with each of these records being only one
field?
Like this?

1 A
2 A#
3 B
4 C

Also, what did you intend the form to look like? I have 1 fill-in combobox
for the key (A, A#, B etc.) One fill-in combobox for the Chord Type (MAJ,
MIN, AUG etc) and four fields for the notes to be filled in by the code,
corresponding to the proper key and chord type that I put into those two
fields.

I am having a difficult time understanding where this is going. Perhaps I
could take you up on your offer to send the DB file and see if that helps.
My address is: (e-mail address removed)
No one else has even attempted an answer, so I commend you on even trying!

Thaks again.
 
Back
Top