Where to start

[linronamy]

I have no idea how to design a database to provide the information I need to
provide directions on a monthly interval from data accumulated over 6 years.

• The data includes 165 unique seasonal EVENT descriptions.

o The events occur approximately 12 times a year.
o These 12 events repeat but are different the 2nd and 3rd year and then
repeat for the next 3 years, and so on.

• For each event there are 11 ACTIONS, each having a descriptor NAME and
ID NUMBER.

Process:
• The goal is to pick an event.
• View each action from that unique event reviewing all the name descriptors
that occurred for each action over the previous 6 years.
• Select one name and its corresponding ID number for each action.
• Print the results in a report.

The data input form seems easy enough but viewing each NAME per ACTION and
selecting one of many to print in a report is where I fall apart. Any
suggestions on how to start will be greatly appreciated.

 

[Steve Schapel]

Linronamy

The information you have provided so far is excellent. It would make it
a lot easier for anyone to fully grasp your meaning if you could provide
some examples of your events, actions, and other data. Thanks.

 

[Tom Wickerath]

Hi linronamy,

I have no idea how to design a database to provide the information I need...

You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Here are some links to get you started. Don't
underestimate the importance of gaining a good understanding of database
design. Brew a good pot of tea or coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

Also, head on over to Jeff Conrad's site. He has lots of links for database
design
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

For each event there are 11 ACTIONS, each having a descriptor NAME and
ID NUMBER.

Okay, it sounds like you have a one-to-many (1:M) relationship between
Events and Actions, ie. one event may have many actions. This data should be
stored in two tables; one for events and one for actions.

The question that comes up is do you have a many-to-many relationship
between these two entities? In other words, is the following statement true
or false?

An action can apply to many events

If you answered yes, then you have a M:N relationship, which requires the
use of a third join or linking table.

By the way, NAME is a reserved word in Access. You should not use any
reserved words or special characters for things that you assign a name to in
Access. By avoiding reserved words and special characters (for example
spaces), you will automatically avoid many problems routinely encountered by
others. Here are some KB articles that should be helpful to you:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Process:
• The goal is to pick an event.
• View each action from that unique event reviewing all the name descriptors

that occurred for each action over the previous 6 years.

You should be able to display the event data in a form, with the
corresponding actions in a subform.

• Select one name and its corresponding ID number for each action.
• Print the results in a report.

Here's where it will get a bit trickier, because if you select one or more
records in a subform, and then click on a print button on the main form, the
selected records in the subform will have been lost. Try this KB article out:

How to enumerate selected form records in Access 2002
http://support.microsoft.com/?id=294202

You should be able to use VBA code to create an appropriate WhereCondition
argument for the DoCmd.OpenReport method. Try the above article out first, to
gain some practice with identifying the selected records.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have no idea how to design a database to provide the information I need to
provide directions on a monthly interval from data accumulated over 6 years.

• The data includes 165 unique seasonal EVENT descriptions.

o The events occur approximately 12 times a year.
o These 12 events repeat but are different the 2nd and 3rd year and then
repeat for the next 3 years, and so on.

• For each event there are 11 ACTIONS, each having a descriptor NAME and
ID NUMBER.

Process:
• The goal is to pick an event.
• View each action from that unique event reviewing all the name descriptors
that occurred for each action over the previous 6 years.
• Select one name and its corresponding ID number for each action.
• Print the results in a report.

The data input form seems easy enough but viewing each NAME per ACTION and
selecting one of many to print in a report is where I fall apart. Any
suggestions on how to start will be greatly appreciated.

 

[linronamy]

MUSIC SELECTION PLANNER FOR CHURCH LITURGIES USING PREVIOUS 6 YEARS OF DATA.

EXAMPLES:
Events - lit1 cycle a, lit1 cycle b, lit1 cycle c (lit1 - lit12, cyca,b,c
for each)
Actions - opening, responsorial, gospel accl., etc.,, (parts of liturgy)
Name - song title
Id - song number reference

DATA INPUT: using defined/restricted input form.

USERS (NOT COMPUTER LITERATE) NEED TO BE ABLE TO(from one screen):
• Select a liturgy,
• Review each song previously used for each part of the liturgy (with drop
down),
• Select a song for each part they will use for the upcoming liturgy (from
drop down),
• Print the results of all selections on a report to be used as a liturgy
order
for musicians and vocalists.

THANX for your interest and help

 

[Steve Schapel]

Linronamy,

It seems to me that from the point of view of table design, you will
need this structure:

Table: Liturgies
LiturgyNumber
LiturgyName

Table: LiturgyEvents
LiturgyEventID
LiturgyNumber
LiturgyCycle

Table: Actions
Action

Table: Songs
SongID
SongName

Table: SongAssignments
AssignmentID
LiturgyEventID
Action
SongID

 

[Jerry Whittle]

Before ever considering building anything but a simple relational database,
you need to understand the basics. For that I highly recommend Database
Design for Mere Mortals by Hernandez. Amazon carries the latest version:
http://www.amazon.com/gp/product/0201752840

 

[linronamy]

I have created the following suggested (modified) tables with actual labels
in the second column:

Table: Liturgies Table: Liturgies
LiturgyNumber LitNumber (Index Number) Primary key R3
LiturgyName LitName (Liturgical day)
LiturgyCycle LitCycle (Cycle–A, All, B, C, SP or M) R1

Table: LiturgyEvents Table: LitFunctions
LiturgyEventID LitPart (Part of liturgy where hymn is played) R2 R4
LiturgyNumber LitNumber (Index Number) Primary key R3

Table: Actions Not required
Action Not required

Table: Songs Table: Hymns
SongID HymnID (Index Number) Primary key R5
SongNumber HymnNumber (Hymn Number)
SongName HymnName (Hymn Title)

Table: SongAssgnmt Table: HymnAssignments
AssignmentID AssignmentID (Index Number) Primary key
LiturgyEventID LitPart (Part of liturgy where hymn is played) R4
Action Not required
SongID HymnID (Index Number) R5

I also created two Lookup Tables:
LitPart R2
Cycle R1

I created relationships with different fields as indicated by Rnumbers (R1
relates to R1, etc.).

Should I create one form to input all data?

The worth of this effort is to allow a non-computer person to review which
hymns were used for each part of the liturgy for a specific Liturgical Day
from the past selections. This is where I am lost; How do I design the query
and report?

Thanx for any suggestions

 

[Steve Schapel]

Linronamy,

As regards data entry, no you would not use one form for management of
all the data. Normally you would use a number of forms, and for the key
data (i.e. which hymns are assigned on which action (litpart)), you
would have a subform on a main form.

But anyway, that's jumping ahead, as is any talk about queries and
reports. None of this is pertinent until the table design is right, and
I'm afraid the schema you suggested is not workable. I would strongly
suggest you revert to something more similar to the design that I
suggested before.

 

[linronamy]

I thought I structured the tables per your suggestion.
Please bear with me. I want to understand how I blew it.
I suspect my first mistake was in how I didn’t define it properly.
There is a liturgy with 11 parts, a song and song number for each part.

Project Definition:
1. For each Liturgy (there are 165 unique)
2. there will be eleven parts (in each liturgy),
3. and each part will have one song with it’s song number.

Data Input would consist of typing:
1. One Liturgy name
2. Part 1 and its song and song number, Part 2 and its song and song
number and so on thru Part 11.

Query:
1. Select a Liturgy
2. View each part of that Liturgy (1 thru 11- one at a time)
3. View each song and song number for each part (can be 4 or 5)
4. Select one song and song number from each part
5. Eleven (11) selected results will be used in a report to be printed
(one song and it's song number from each part)

Report:
1. Print Liturgy name
2. Print each part with the query selected song and song number

Trying to do as you suggested helped me recognize I didn’t give you the
necessary description to understand what I was trying to do. My apology.
Thanks again.

 

[Steve Schapel]

Linronamy,

To be honest, I thought your original descriptions were pretty good,
apart from the confusion caused by changing terminology on me (events,
actions, and songs, in your first description, became something else later).

Can you give some specific examples of the data you are using? For
example, some sample data for 'Liturgies'? In the meantime, here is my
(still incomplete) take on it... If you have 165 Liturgies, and each
Liturgy can be assigned to one of several Cycles, then the Cycle field
does not belong in the Liturgies table. There needs to be a separate
table, related many-to-one with the Liturgies table, to create this
entity. In my suggested table structure, this is exactly what I did...
a Liturgies table and a LiturgyEvents table, but you modified this idea
by collapsing these two tables into one. Then, you named a table in
your schema as LiturgyEvents, which is actually a different concept, and
renamed the Action field (which we are now calling LitPart) in the
HymnAssignment table but in so doing will lose the ability to relate the
hymn assignment to the Cycle. Mind you, when I look again at your last
post about 'Data Input', we have lost the aspect about Cycles now, so
where does that fit in?

 

[linronamy]

Steve,

Sorry for the confusion. I will try to clear it up. My previous reply is
accurate. Here are the examples in lowercase alpha’s.

Project Definition:
1. For each Liturgy (there are 165 fixed and unique names with cycle
designation of A, B, or C). TABLE: LITURGIES
a. Table – LitNumber(PriKey) LitName
1 Advent 1st Sunday A
2 Advent 1st Sunday B
3 Advent 1st Sunday C
4 Advent 2nd Sunday A
5 Advent 2nd Sunday B
6 Advent 2nd Sunday C (Thru 4 Sundays)
7 Easter 2nd Sunday A
8 Easter 2nd Sunday B
9 Easter 2nd Sunday C (etc. thru 165)

2. there will be eleven parts (in each liturgy) TABLE: LITFUNCTIONS
a. Table - LitNumber(PriKey) LitPart
1 Prelude
2 Instrumental Processional
3 Gathering
4 Responsorial Psalm
5 Gospel Acclamation
6 Preparation
7 Pre-Communion Instrumental
8 Communion 1
9 Communion 2
10 Closing
11 Instrumental Postlude

3. and each part will have one song with it’s song number. TABLE: HYMNS
a. Table – HymnID(PriKey) HymnName Hymnumber
1 Morning Has Broken G748
2 Sing to the Mountains G452
3 We Remember G578

Data Input would consist of typing:
1. Liturgy name, eleven parts and the hymns and hymn numbers for each.
a. This would be an example of one data entry sheet

Easter 2nd Sunday A
Prelude Healer Of Our Every Ill G854
Instrumental Processional Eye Has Not Seen G616
Gathering Eye Has Not Seen G616
Responsorial Psalm This Is The Day G64
Gospel Acclamation Alleluia, Alleluia, Alleluia G137
Preparation Lord, I Believe G535
Pre-Communion Instrumental Behold The Lamb G824
Communion 1 Behold The Lamb G824
Communion 2 We Remember G578
Closing Glory And Praise To Our God G537
Instrumental Postlude Eye Has Not Seen G616

Query:
1. Select a Liturgy (Liturgies drop down list)
2. View each part of that Liturgy (1 thru 11- one at a time)
3. View each song and song number for each part (can be 4 or 5)
4. Select one song and song number from each part
5. Eleven (11) selected results will be used in a report to be printed
(one song and it's song number from each part)

Report:
1. Print Liturgy name
2. Print each part with the query selected song and song number

I hope this is helpful to fully understand what I hope to accomplish. I
really appreciate your patience with my inexperience. Thanx Steve

 

[Steve Schapel]

Linronamy,

Thanks for the further explanation, the examples help a lot in making
clear what you are doing, and I see (of course) that I have
misinterpreted some of what you previously wrote.

At the same time, it confirms for me at least one of the confusions
here. You have made great progress. Here are my suggested changes to
what you've got now...

1. You are storing 2 pieces of information (liturgy, and cycle) in one
field. You will have much greater power and flexibility in your
database if you "atomise" it. That means putting the Cycle information
into a separate table, similar to my earlier recommendation. Set up the
Liturgies table like this...

a. Table – LitNumber(PriKey) LitName
1 Advent 1st Sunday
2 Advent 2nd Sunday (Thru 4 Sundays)
3 Easter 2nd Sunday (etc. thru 55?)


2. As a relatively minor point, there is no need at all for a HymnID
(presumably AutoNumber) field in the Hymns table. The only purpose this
could posibly serve is unique identification of each Hymn, and you
already have a real-life HymnNumber that serves that purpose, so table:
Hymns

a. Table – HymnNumber(PriKey) HymnName
G748 Morning Has Broken
G452 Sing to the Mountains
G578 We Remember


3. Similarly, the names of the 11 Parts are unique, so I wouldn't
bother with a LitNumber field in the LitFunctions table either. So...

a. Table - LitPart(PriKey)
Prelude
Instrumental Processional
Gathering
Responsorial Psalm
Gospel Acclamation
Preparation
Pre-Communion Instrumental
Communion 1
Communion 2
Closing
Instrumental Postlude


4. Change your LiturgyEvents table so that is results in a combination
of the Liturgy and Cycle - this is where your 165 unique liturgies comes
in. The purpose of this may not be immediately apparent to you, but I
promise the extra effort will be worth it. So...

a. LitEventId(PriKey) LitNumber Cycle
1 1 A
2 1 B
3 1 C
4 2 A
5 2 B (etc)


5. The table that we earlier designated as HymnAssignments is the place
where the main data entry you mentioned will be saved. The table needs
to be like this...

a. AssignmentID(PriKey) LitEventID LitPart HymnNumber

1 2 Prelude G854
2 2 Gathering G616
etc


Ofv course, some of the data as it ends up in the table is difficult for
the avrerage person to interpret with the naked eye. But then tables
are not meant for human consumption anyway. Correct design of your
forms will make this user friendly... but that's for another day.

 

[Steve Schapel]

Linronamy,

As regards my point 3 below concerning the ID field in the LitFunctions
table, I have revised my thinking here! If you want to determine
the order in which the LitParts are displayed in comboboxes, or in your
reports or whatever, then it will be a good idea to add a numeric field
for this purpose.

 

[linronamy]

Steve,

These are my current TABLE designs:

TABLE: Liturgies
LitNumber LitName
(Pkey, AutoNum) (Liturgy Names using Look-Up Table)
1 Advent 1st Sunday
2 Advent 2nd Sunday
3 Easter 2nd Sunday

TABLE: LiturgiesLU (Look up table possible choices)
LitNameLU
(Unique names)
Advent 1st Sunday
Advent 2nd Sunday
Easter 2nd Sunday
(There are actually 65 unique names w/o Cycles)

TABLE: Hymns
HymnName HymnNumber
(Pkey, Hymn Title) (Hymn#)
Morning Has Broken G748
Sing to the Mountains G452
We Remember G578
** Must use Pkey with HymnName as HymnNumbers
may not be unique where the names will be.

TABLE: Cycles
CycleName
(Pkey, Cycle designation)
A
B

TABLE: CyclesLU (Look up table possible choices)
CycleName
(Pkey, Cycle designation)
A, B, C, Special, Memorial, Wedding

TABLE: LitPart
LitPart
(Describes the Part of the Liturgy where hymn
is being played/sung.)
Prelude
Instrumental Processional
Gathering

TABLE: LitPartLU
LitPartID(Number) LitPart (Look up table choices)
1 Prelude
2 Instrumental Processional
3 Gathering

TABLE: LitEvents
LitEventID LitNumber Cycle
(Pkey, AutoNum.) (Ref. Liturgies Table) (Ref. Cycles Table)
1 1 A
2 1 B
3 1 C
4 2 A

TABLE: Assignments
AssignID LitEventID LitPart HymnNumber
(Pkey, AutoNum.) (Ref. LitEvents Table) (Ref. LitPart Table) (Ref. Hymns
Table)
1 2 Prelude G854
2 2 Gathering G616
etc

I am comfortable with most of the tables, but not sure about redundancies.
How does it look?
Am I ready for Data input yet?

Eagerly awaiting your reply, THAX again.

 

[Steve Schapel]

Linronamy,

Well, a few comments...

1. I can't see any purpose for the LiturgiesLU table. It holds exactly
the same data as the Liturgies table, except for the LitNumber id field.
The Liturgies table *is* your lookup table for liturgies, you don't
need another one. Just remove the LiturgiesLU table from the design
altogether.

2. Do you mean to say that you could have more than one Hymn with the
same HymnNumber? Like two hymns, both G578? This is very strange, but
if so, I would not use the HymnName as the Primary Key field in the
Hymns table. If you really can have more than one Hymn with the same
HymnNumber, you should put back a HymnID Autonumber field in this table
as the PK. And, whatever is the PK, HumnNumber or HymnID, has to be the
field that links to the hymn entry in the Assignments table.

3. Similar to point 1, the Cycles and CyclesLU tables duplicate each
other. There is no need for the CyclesLU table, so you might as well
trash it.

4. As I indicated in an earlier reply, the LitPart table is fine like
it is, from a functional point of view. But any combobox for data entry
of the Part, or reports, will be sorted alphabetically, which may not be
what you want. Gathering will come before Prelude, for example. If you
want to have them sorted in order or how they occur, you will need to
add another field to the table to control this. Probably a Number data
type is easiest.

 

[linronamy]

Steve,

Perhaps I misinterpretted the lookup application. I was using the
LiturgiesLU and CyclesLU tables to facilitate data entry on a form. Allows
less keystrokes and eliminates typo errors. The Liturgy list is fixed as is
the Cycles list and I thought including them in their tables as lookups
before any other data was input would be helpful at data entry time on a
form. I'm not sure I understand the big picture as to how data relates to
each other to facilitata DB efficiency.

Hymns Table:
** HymnNumber Examples: G748 (G for Hymnal description - #748) unique
B147 (B for Hymnal description - #147) unique
S (Song Sheet where not in hymnal) repetitive
Kevin (Author’s name) Repetitive
The HymnName would be unique where the HymnNumber may not be. Adding a
HymnID is not a problem. Is sort order an issue (for query purposes)?

Thanx for help

 

[Steve Schapel]

Linronamy,

See comments inline...

Perhaps I misinterpretted the lookup application. I was using the
LiturgiesLU and CyclesLU tables to facilitate data entry on a form. Allows
less keystrokes and eliminates typo errors. The Liturgy list is fixed as is
the Cycles list and I thought including them in their tables as lookups
before any other data was input would be helpful at data entry time on a
form.

You are absolutely correct in your thinking here. However, the
Liturgies and Cycles tables, as you described them, will serve this
exact purpose.

Hymns Table:
** HymnNumber Examples: G748 (G for Hymnal description - #748) unique
B147 (B for Hymnal description - #147) unique
S (Song Sheet where not in hymnal) repetitive
Kevin (Author’s name) Repetitive
The HymnName would be unique where the HymnNumber may not be.

Ok, thanks for the further explanation. Unless you want to institute a
system of S1, S2, etc, Kevin1, Kevin2, etc, then the Autonumber ID field
is the way to go.

Adding a
HymnID is not a problem. Is sort order an issue (for query purposes)?
No.

Thanx for help

You're welcome.

 

[linronamy]

Steve,

Based on your last reply I think my table designs are set except for
relationships between tables. I'm not sure how to approach which tables must
have a link between them. Do I simply link all fields with similar field
names between tables?

Can I proceed to Form design?
I'm not sure how to approach the design using more than one form for data
input.

Thanx

 

[Steve Schapel]

Linronamy,

The purpose of defining Relationships is so you can set up Referential
Integrity. And what Referential Integrity does is ensure that you can't
have a record on the "many" side of a one-to-many relationship without a
corresponding record on the "one" side. So, for example, you could set
up a Relationship between the HymnID field in the Hymns table and the
HymnID in the Assignments table, so this will mean you couldn't delete a
hymn from the Hymns table if that hymn was allocated to a liturgy
somewhere in the system.

So, that's the tables, which is by far the most important aspect of the
design of your database.

As regards forms, I think I would have a simple form, continuous view,
based on the Hymns table. Similarly, I think I would have a similar
form based on the Liturgies table, for ease of reference to this
relatively stable lookup data. I don't think I would bother with a form
for management of the Cycles table or the LitPart table... these are
very simple, and I imagine the data is very unlikely to change much.
There aren't really any right or wrongs about this, but I think that's
how I would probably go about it.

But here's the general concept with the main data forms...
You need 2 forms:
1. Single view form, based on the LitEvents table. This will use
comboboxes whose Row Sources are the Liturgies table and the Cycles
table for data entry. So each record on this form will define a Liturgy
for a given Cycle.
2. A continuous view form based on the Assignments table. This form
will be places on the LitEvents form as a subform, with its
LinkMasterFields and LinkChildFields properties set to LitEventID.
Comboboxes, with their Row Sources being the LitPart and Hymns tables,
will be used for data entry into the LitPart and HymnID fields.

You may need to reference a good book or some other resource in getting
onto a mastery of subforms and comboboxes.

 

[linronamy]

Steve,

I see that I have a lot of reading to do and I thank you for pointing me in
a focused direction to the specifics of my next step. Can you clarify for me
the advantages of using more than one form for data entry over a single form
or will this all become obvious after studying your recommendations in your
last reply? A sample of my existing hard copy data that will be used when
entering data:

2nd Sunday of Advent Cycle B

Prelude RISE UP JERUSALEM G305
Instrumental Processional CHRIST BE OUR LIGHT Song Sheet
Gathering LIKE A SHEPHERD G332
Responsorial Psalm LORD, LET US SEE YOUR KINDNESS G48
Gospel Acclamation ALLELUIA, ALLELUIA, ALLELUIA Advent GA
Preparation PROMISE Song Sheet
Pre-Communion Instr. TURN TO ME G278
Communion 1 A VOICE CRIES OUT G343
Communion 2 SHEPHERD ME, O GOD G23
Closing CITY OF GOD G663
Instrumental Postlude LET THE VALLEYS BE RAISED B339

With my database naivety in mind, it seems to me there would be rewards in
not requiring the people (not computer literate) doing the data entry to
select different forms for different data.

Thanx again for your patience and help