Newbie v2 - I want to do it right instead of always fixing it after

[Daryl]

Hi All!.....Here I go Again.

I'm currently using Access 2013 on a Windows 10 OS. I've used Access before for a few simple things but have been out of the designing aspect for a number of years now...although I remember some things, there are new concepts (to me) that I have been made aware of that has made me second guess everything I've done in the past in terms of designing a database.

First, you should know I design for the user experience, not for the background programming aspect. I use(d) form design extensively with limited "programming" skills and terminology knowledge.

That being said, I would like to make sure that this new database I'm creating is more functional and PROPERLY designed than my past endeavours. So.....

I am creating a database for a community choir to maintain the records of the songs, the singers, the music copies (who is assigned them, and their status), and the performances. At the current state (subject to redesign), I have run across a dilemma of when to use a table (lookup field in s parent table linking to child table) or if to simply add a drop-down combo or list box on a form.

For example,

Currently, the members table (tbMembers) has the following information:

MbrID (pk, autonumbered
FName (text)


LName (text)
Status (lookup field from tbMbrStatus table which includes the fields: StatusID, Singing, Non-Singing, On-Leave, Resigned and Administrative)
Section (lookup field from tbSection table which includes the fields:SectionID, Soprano, Alto, Tenor, Bass.
Notes (text)

So, the collection of member info is accumulated from 3 different (and linked) tables.

The Member Information form (fmMembers) contains elements for all the fields in the main tbMembers table. The Status and Section controls are currently Lists boxes associated with their respective fields.

My question is (in this example) is there a different between having a "table" for Status and Section, or removing those fields from the Members table (if that's how its done) and simply including a ListBox on the form design for each, and adding those selectable options on the form only. When that is done, where does Access keep that information for use later in pulling queries or reports?

I know this is a very basic (remedial) question for most of you, but I want to do it right.

This same scenario also appears in our tblSongList table (type of arrangement, type of copy, type of voicing (SATB, SSA, etc.), our tblCopyStatus table (what is filed, what is out, what is lost/missing), and our Performances (Venue, type of performance).

As this develops, there are certainly many more things I will have questions about.

So... if there are any mentors out there willing to help me think through this project, I am all ears.

Thanks in advance.

Daryl

[June7]

My advice (as many here will reinforce) is to not build lookup in table - build combobox or listbox on form http://access.mvps.org/Access/lookupfields.htm.

Fields would still be in Members table to receive foreign key value of Status and Section tables, just not via table entry. Users should not interact directly with tables and queries, just forms and reports.

An alternative to saving a foreign key value when descriptive data is so brief, is to save the actual text. The advantage to saving numeric foreign key would be faster sorting and filtering as well as consuming less storage.

[Daryl]

Thanks June7,

That is what I thought may be the recommendation. I have been doing a Test dB to confirm this will work for my purposes, and it seems to be great... but I do want to confirm something.

The tbMembers table will STILL require fields to accept the data input on the form, correct?

I would still need to define (in the table) something like fkSection, and fkStatus text fields (fk = foreign key), just with no link to another table.

I would just assign the list/combo boxes to those fields when adding them to the form. Am I understanding this correctly?

-----
An additional thing that I noticed when opening the existing dB and forgot to question in my last post I will include here (to keep things together).

Each member is assigned a "Music Copy Number" - actually a set of 4 different music numbers depending on the arrangement, and number of participants.

These "types" of numbers are:
Full Choir Number
SA Number (soprano, alto only arrangements)
TB Number (tenor, bass only arrangements)
Small Group Number (SATB arrangement with fewer participants)

These numbers vary in quantity, but the largest group (full Choir) goes to 50 copies. A single member does not necessarily have the same number for each of the types. For example, "Joe" may have copy number 16 for the full choir pieces, no copy for the SA numbers, copy number 3 for the TB songs, and copy 13 for the small group song.

None of these numbers can be duplicated - no two members can have the same copy number for the same "copy type"

If a member resigns or leaves the choir, their copy numbers become available to assign to somebody else. With that in mind, I also need to pull queries/reports on numbers that are available to use (ie, unassigned) for each of the copy type numbers.

"Currently", there is a table set up for each of the copy types, with fields for both the copy number (no duplicates) and if the number is assigned or not (yes/no). These tables are linked back to the parent "tbMembers" table.

Would it be more "prudent" to change these to a similar list/combo box style (on form), allowing the typing of the needed number with limitations of no duplicates and the values must be greater than zero, but less than 51? If that makes sense to anyone...

How then would I query "available" (unassigned) numbers?

???
Daryl

[orange]

Daryl,

I agree with your "design for the user experience" and that really is the user interface. The user interface uses forms and controls. But those forms are really windows onto the underlying tables where the data is stored. So getting the tables and relationships designed to support your application is critical also.
It would be useful to you and readers if you could tell us about the things involved(your subject matter and eventual tables) and what relates these things.
There are several articles in this link related to Database Planning and Design that may be helpful. However, I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link. You can work through a tutorial in about 30-45 minutes and what you learn/experience/refresh can be used with any database.
Good luck with your project.

[June7]

That is almost correct. If you are saving autonumber into foreign key field, that FK field must be number type (Long Integer).

Possibly use compound index in table to prevent duplicate combinations of member/number/type.

Query 'unassigned' numbers with a Find Unmatched query (there is a query wizard for that). Would have to review db structure to advise further.