Form design difficulties

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):
Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It does
not vary from year to year. I understand that the hymns change, but let's
leave them out for now.
Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that, or are Cycle A, B, and C
scattered amongst each other? Of what does a liturgy part consist other
than the hymn? For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what?
Liturgy Events are the records that will be created as you go, as I
understand it. For each liturgy event you will select from a list of
existing liturgies. Am I correct so far? If I am, you can still create a
new liturgy, but you would do that separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same very
time (every three years, that is), or can the event details change three
years later?
In general, I see it something like this. You will create a liturgy event,
and select a liturgy. Since each liturgy may be associated with several
events, but each event contains just one liturgy, there is a one-to-many
relationship between liturgy events and liturgies (one liturgy, many
events). tblLiturgyEvents therefore needs LitID as a foreign key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used, or are details associated
with the event rather than the liturgy, or both? A simplified example may
help my understanding of this.
One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts?

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future liturgies.

I have a lot of questions about how my current table/relationship setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails, but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other. I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database;
I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I
am
mistaken to use it. These are my current questions relating to creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying
To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record
in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and I
was
able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
. I expected only the Main form controls hoping to enter data into
each
field and moving to the next record.
. I am confused by my need to enter 11 unique parts with
corresponding
Hymns and Hymn numbers into each record but there is only the
ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it
happen. I
have spent the last three weeks researching using the HELP feature
of
ACCESS
2003, recommended tutorials (DataPig Technologies were excellent)
(DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN's
Database
Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc.,
HOW
TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of
"Brain
dead" with form design but wish to go forward. Any suggestions on
how
to
proceed are most welcome. THANX

My response is all uppercase.

"BruceM" wrote:

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):

Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It does
not vary from year to year. I understand that the hymns change, but let's
leave them out for now. CORRECT

Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that, CYCLE B STARTED IN DEC OF 2005 (ADVENT) AND WILL RE-OCCUR IN DEC OF 2008. IN DEC OF 2006 WE WILL START CYCLE C AND CYCLE A IN DEC 2007. or are Cycle A, B, and C
scattered amongst each other? Of what does a liturgy part consist other
than the hymn? THERE ARE ELEVEN DISTINCT PARTS OF EACH LITURGY. THEIR NAMES DO NOT CHANGE, ONLY THE HYMNS PLAYED DEPENDENT ON THE READINGS FOR THAT LITURGY AND CYCLE. For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what? UPON ENTERING A LITURGY NAME AND THE CYCLE NAME, THEY WILL SEARCH FOR THE HYMNS AND HYMN NUMBERS FOR EACH OF THE ELEVEN PARTS OF THAT LITURGY/CYCLE (NO NEED TO SEARCH FOR READINGS AS THAT HAS ALREADY BEEN DONE IN THE PAST).
Liturgy Events (ARE A COMBINATION OF A LITURGY NAME, ITS CYCLE AND A PARTICULAR PART, PAST OR FUTURE) are the records that will be created as you go, as I
understand it. THE VALUE OF A RECORD IS - "WHICH HYMNS WERE PLAYED FOR EACH OF THE ELEVEN PARTS OF A PARTICULAR LITURGY AND CYCLE IN THE PAST" - ALLOWING THE OPPORTUNITY TO CHOOSE A HYMN FOR EACH OF THE ELEVEN PARTS THAT IN EFFECT HAVE ALREADY BEEN RESEARCHED TO VALIDATE ITS APPROPRIATENESS FOR THAT READING - "FOR THE FUTURE LITURGY". For each liturgy event you will select from a list of
existing liturgies. Am I correct so far? THE LITURGY EVENTS ARE ALL CREATED AND FIXED, MEANING THE LITURGY TABLE DOES NOT CHANGE, NOR DOES THE CYCLE TABLE, OR THE PARTS TABLE. THEY ARE THE SUPPORT TABLES USED TO DEFINE AN EVENT THAT WE WILL BE CHOOSING A HYMN FOR A FUTURE LITURGY USING DATA FROM THE PAST. If I am, you can still create a
new liturgy, but you would do that separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same every
time (every three years, that is), ABSOLUTELY - IT REPEATS EVERY THREE YEARS. or can the event details change three
years later?
In general, I see it something like this. You will create a liturgy event,
and (BY) selectING a liturgy AND CYCLE AND PART. Since each liturgy may be associated with several events (CYCLES AND PARTS COMBINATIONS), but each event contains just one liturgy, there is a one-to-many relationship between liturgy events and liturgies (one liturgy, many events I BELIEVE THAT IS CORRECT). tblLiturgyEvents therefore needs LitID as a foreign key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used (YES), or are details associated
with the event rather than the liturgy, or both? A simplified example may
help my understanding of this.
EXAMPLE:
1ST WEEK OF DECEMBER STARTS A NEW CYCLE.

IN 2006 IT WILL BE "1ST WEEK OF ADVENT, CYCLE C"

THERE WILL BE ELEVEN PARTS AS IN ALL LITURGIES

WE SELECT A HYMN AND HYMN NUMBER FOR EACH PART

WITH OUR DATABASE WE CAN REVIEW ALL THE HYMNS 'PLAYED FOR EACH PART' AT EACH
"1ST WEEK OF ADVENT, CYCLE C" IN THE PAST

WE THEN SELECT A HYMN FROM THE DATA 'FOR EACH PART' AND USE THOSE CHOICES TO
CREATE A NEW RECORD FOR THE "1ST WEEK OF ADVENT, CYCLE C".

WE THEN CREATE A REPORT TO PUBLISH TO ALL MUSICIANS TO ALLOW THEM TO
PRACTICE THE HYMNS IN ADVANCE OF THE LITURGY IN THE 1ST WEEK OF DEC.

One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts? THEY ARE THE SAME FOR EVERY LITURGY.

THANX FOR YOUR INTEREST, THE MENTAL JUICES ARE FLOWING AGAIN

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future liturgies.

I have a lot of questions about how my current table/relationship setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails, but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other. I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database;
I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I
am
mistaken to use it. These are my current questions relating to creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying
To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record
in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and I

My response is all uppercase.

"BruceM" wrote:

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):

Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It does
not vary from year to year. I understand that the hymns change, but let's
leave them out for now. CORRECT

Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that, CYCLE B STARTED IN DEC OF 2005 (ADVENT) AND WILL RE-OCCUR IN DEC OF 2008. IN DEC OF 2006 WE WILL START CYCLE C AND CYCLE A IN DEC 2007. or are Cycle A, B, and C
scattered amongst each other? Of what does a liturgy part consist other
than the hymn? THERE ARE ELEVEN DISTINCT PARTS OF EACH LITURGY. THEIR NAMES DO NOT CHANGE, ONLY THE HYMNS PLAYED DEPENDENT ON THE READINGS FOR THAT LITURGY AND CYCLE. For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what? UPON ENTERING A LITURGY NAME AND THE CYCLE NAME, THEY WILL SEARCH FOR THE HYMNS AND HYMN NUMBERS FOR EACH OF THE ELEVEN PARTS OF THAT LITURGY/CYCLE (NO NEED TO SEARCH FOR READINGS AS THAT HAS ALREADY BEEN DONE IN THE PAST).
Liturgy Events (ARE A COMBINATION OF A LITURGY NAME, ITS CYCLE AND A PARTICULAR PART, PAST OR FUTURE) are the records that will be created as you go, as I
understand it. THE VALUE OF A RECORD IS - "WHICH HYMNS WERE PLAYED FOR EACH OF THE ELEVEN PARTS OF A PARTICULAR LITURGY AND CYCLE IN THE PAST" - ALLOWING THE OPPORTUNITY TO CHOOSE A HYMN FOR EACH OF THE ELEVEN PARTS THAT IN EFFECT HAVE ALREADY BEEN RESEARCHED TO VALIDATE ITS APPROPRIATENESS FOR THAT READING - "FOR THE FUTURE LITURGY". For each liturgy event you will select from a list of
existing liturgies. Am I correct so far? THE LITURGY EVENTS ARE ALL CREATED AND FIXED, MEANING THE LITURGY TABLE DOES NOT CHANGE, NOR DOES THE CYCLE TABLE, OR THE PARTS TABLE. THEY ARE THE SUPPORT TABLES USED TO DEFINE AN EVENT THAT WE WILL BE CHOOSING A HYMN FOR A FUTURE LITURGY USING DATA FROM THE PAST. If I am, you can still create a
new liturgy, but you would do that separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same every
time (every three years, that is), ABSOLUTELY - IT REPEATS EVERY THREE YEARS. or can the event details change three
years later?
In general, I see it something like this. You will create a liturgy event,
and (BY) selectING a liturgy AND CYCLE AND PART. Since each liturgy may be associated with several events (CYCLES AND PARTS COMBINATIONS), but each event contains just one liturgy, there is a one-to-many relationship between liturgy events and liturgies (one liturgy, many events I BELIEVE THAT IS CORRECT). tblLiturgyEvents therefore needs LitID as a foreign key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used (YES), or are details associated
with the event rather than the liturgy, or both? A simplified example may
help my understanding of this.
EXAMPLE:
1ST WEEK OF DECEMBER STARTS A NEW CYCLE.

IN 2006 IT WILL BE "1ST WEEK OF ADVENT, CYCLE C"

THERE WILL BE ELEVEN PARTS AS IN ALL LITURGIES

WE SELECT A HYMN AND HYMN NUMBER FOR EACH PART

WITH OUR DATABASE WE CAN REVIEW ALL THE HYMNS 'PLAYED FOR EACH PART' AT EACH
"1ST WEEK OF ADVENT, CYCLE C" IN THE PAST

WE THEN SELECT A HYMN FROM THE DATA 'FOR EACH PART' AND USE THOSE CHOICES TO
CREATE A NEW RECORD FOR THE "1ST WEEK OF ADVENT, CYCLE C".

WE THEN CREATE A REPORT TO PUBLISH TO ALL MUSICIANS TO ALLOW THEM TO
PRACTICE THE HYMNS IN ADVANCE OF THE LITURGY IN THE 1ST WEEK OF DEC.

One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts? THEY ARE THE SAME FOR EVERY LITURGY.

THANX FOR YOUR INTEREST, THE MENTAL JUICES ARE FLOWING AGAIN

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future liturgies.

I have a lot of questions about how my current table/relationship setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails, but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other. I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database;
I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I
am
mistaken to use it. These are my current questions relating to creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying
To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record
in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and I

Responding inline is fine, but please start a new line with a space above
and below. Note how I have edited what you sent. It was too difficult to
read otherwise.

I realize that you are talking about things other than weekly worship
services, but I'm just going to call them services. I'm getting lost in the
terminology.

Is a service a type of liturgy event? Do you intend to create a record for
each service?

Here's one way to approach this. I will assume that First Week in Advent is
a liturgy name. From what I understand, it is exactly the same from year to
year except for the hymns. Therefore your records in tblLiturgy can consist
of a field for the Liturgy Name plus fields for each of the eleven parts
(Prelude, Gathering, etc.). I have to mention that there will be problems
with the suggestions that follow if you ever change a liturgy, unless you
create a new record for the changed liturgy. There are ways to manage that,
but I won't get into it unless I know it's needed.

tblLiturgy is as I have described. Make a table with fields for just two
parts for now. I will say that the fields are Prelude and Gathering.
tblServiceRecord contains the information that is necessary to create a
record about a particular service on a particular date. It will need a
primary key, foreign key [LitID] to correspond to the primary key in
tblLiturgy, a date field, fields for all of the hymns, Cycle, and maybe a
comments field. Make a form (frmServiceRecord) based on tblServiceRecord.
On it, create a combo box bound to [LitID]. Use a query based on tblLiturgy
as the combo box row source. In the query the first column is the PK, and
the second column is the liturgy name. Set the column count to 2, the bound
column to 1, and the column widths to 0";1".

Add two unbound text boxes to the form. In one, place the following in the
Control Source (watch for line wrapping):
= DLookup("[Prelude]", "tblLiturgy", "LitID =" &
[Forms]![frmServiceRecord]![LitID])

Place the same thing in the other text box control source, except substitute
Gathering for Prelude. I think I have that right. It's getting late on a
Friday.

Now when you select a record from tblLiturgy, the Prelude and Gathering
fields will populate the two unbound text boxes.

In similar fashion to what I outlined for adding Prelude and Gathering, you
can add hymns, selecting the title and displaying the number in an unbound
text box. But I would leave that for now, and concentrate on the two-table
structure I have outlined. You need to see how a single relationship works
in practice before you add other relationships. You may be just getting
started, but this is a long, long way from being a beginner's project.

"linronamy" wrote in message
...
My response is all uppercase.

"BruceM" wrote:

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what
you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):

Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It
does
not vary from year to year. I understand that the hymns change, but
let's
leave them out for now.

CORRECT

Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that

CYCLE B STARTED IN DEC OF 2005 (ADVENT) AND WILL RE-OCCUR IN DEC OF 2008.
IN DEC OF 2006 WE WILL START CYCLE C AND CYCLE A IN DEC 2007.

or are Cycle A, B, and C scattered amongst each other? Of what does a
liturgy part consist other
than the hymn?

THERE ARE ELEVEN DISTINCT PARTS OF EACH LITURGY. THEIR NAMES DO NOT
CHANGE, ONLY THE HYMNS PLAYED DEPENDENT ON THE READINGS FOR THAT LITURGY
AND CYCLE.

For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what?

UPON ENTERING A LITURGY NAME AND THE CYCLE NAME, THEY WILL SEARCH FOR THE
HYMNS AND HYMN NUMBERS FOR
EACH OF THE ELEVEN PARTS OF THAT LITURGY/CYCLE (NO NEED TO SEARCH FOR
READINGS AS THAT HAS ALREADY BEEN
DONE IN THE PAST).

By what criteria will they search.

Liturgy Events (ARE A COMBINATION OF A LITURGY NAME, ITS CYCLE AND A
PARTICULAR PART, PAST OR FUTURE) are the records that will be created as
you go, as I
understand it.

THE VALUE OF A RECORD IS - "WHICH HYMNS WERE PLAYED FOR EACH OF THE ELEVEN
PARTS OF A PARTICULAR LITURGY AND
CYCLE IN THE PAST" - ALLOWING THE OPPORTUNITY TO CHOOSE A HYMN FOR EACH OF
THE ELEVEN PARTS THAT IN EFFECT
HAVE ALREADY BEEN RESEARCHED TO VALIDATE ITS APPROPRIATENESS FOR THAT
READING - "FOR THE FUTURE LITURGY".

For each liturgy event you will select from a list of existing liturgies.
Am I correct so far?

THE LITURGY EVENTS ARE ALL CREATED AND FIXED, MEANING THE LITURGY TABLE
DOES NOT CHANGE, NOR DOES THE CYCLE
TABLE, OR THE PARTS TABLE. THEY ARE THE SUPPORT TABLES USED TO DEFINE AN
EVENT THAT WE WILL BE CHOOSING A HYMN
FOR A FUTURE LITURGY USING DATA FROM THE PAST.

If I am, you can still create a new liturgy, but you would do that
separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same
every
time (every three years, that is)

ABSOLUTELY - IT REPEATS EVERY THREE YEARS.

or can the event details change three years later?
In general, I see it something like this. You will create a liturgy
event,
and (BY) selectING a liturgy AND CYCLE AND PART. Since each liturgy may
be associated with several events (CYCLES AND PARTS COMBINATIONS), but
each event contains just one liturgy, there is a one-to-many relationship
between liturgy events and liturgies (one liturgy, many events I BELIEVE
THAT IS CORRECT). tblLiturgyEvents therefore needs LitID as a foreign
key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo
box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used (YES), or are details
associated
with the event rather than the liturgy, or both? A simplified example
may
help my understanding of this.

EXAMPLE:
1ST WEEK OF DECEMBER STARTS A NEW CYCLE.

IN 2006 IT WILL BE "1ST WEEK OF ADVENT, CYCLE C"

THERE WILL BE ELEVEN PARTS AS IN ALL LITURGIES

WE SELECT A HYMN AND HYMN NUMBER FOR EACH PART

WITH OUR DATABASE WE CAN REVIEW ALL THE HYMNS 'PLAYED FOR EACH PART' AT
EACH
"1ST WEEK OF ADVENT, CYCLE C" IN THE PAST

WE THEN SELECT A HYMN FROM THE DATA 'FOR EACH PART' AND USE THOSE CHOICES
TO
CREATE A NEW RECORD FOR THE "1ST WEEK OF ADVENT, CYCLE C".

WE THEN CREATE A REPORT TO PUBLISH TO ALL MUSICIANS TO ALLOW THEM TO
PRACTICE THE HYMNS IN ADVANCE OF THE LITURGY IN THE 1ST WEEK OF DEC.

One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts?

THEY ARE THE SAME FOR EVERY LITURGY.

THANX FOR YOUR INTEREST, THE MENTAL JUICES ARE FLOWING AGAIN

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will
follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history
to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future
liturgies.

I have a lot of questions about how my current table/relationship
setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite
figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables
are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may
have
many events, but there is no connection between Liturgy and Cycle.
That
each is related to LiturgyEvents does not establish a connection
between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails,
but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent
and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field
by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for
quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do
not
see how your table design can give you the desired results. A record
in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other.
I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in
the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process.
I
can't
afford to hire someone. I don't expect this forum to build my
database;
I
hoped that a few tips heading me in the right direction to my
specific
questions was reasonable. My questions were related to my
experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those
results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If
this
forum
is only for simple Q/A that are easily found thru Access help (F1),
I
am
mistaken to use it. These are my current questions relating to
creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't
the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete
record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3
forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state
it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need
to
either
put in the effort to learn it or hire someone who can do it for
you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very
laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it
up
into
manageable parts, so I would proceed by building one small piece at
a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at
trying
To
setup
a form design to allow data entry for a set(record) of data, I
have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber
fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next
record
in
that
field NOT THE NEXT FIELD on the form??? Do I need
to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice
with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and
I

Responding inline is fine, but please start a new line with a space above
and below. Note how I have edited what you sent. It was too difficult to
read otherwise.

I realize that you are talking about things other than weekly worship
services, but I'm just going to call them services. I'm getting lost in the
terminology.

Is a service a type of liturgy event? Do you intend to create a record for
each service?

Here's one way to approach this. I will assume that First Week in Advent is
a liturgy name. From what I understand, it is exactly the same from year to
year except for the hymns. Therefore your records in tblLiturgy can consist
of a field for the Liturgy Name plus fields for each of the eleven parts
(Prelude, Gathering, etc.). I have to mention that there will be problems
with the suggestions that follow if you ever change a liturgy, unless you
create a new record for the changed liturgy. There are ways to manage that,
but I won't get into it unless I know it's needed.

tblLiturgy is as I have described. Make a table with fields for just two
parts for now. I will say that the fields are Prelude and Gathering.
tblServiceRecord contains the information that is necessary to create a
record about a particular service on a particular date. It will need a
primary key, foreign key [LitID] to correspond to the primary key in
tblLiturgy, a date field, fields for all of the hymns, Cycle, and maybe a
comments field. Make a form (frmServiceRecord) based on tblServiceRecord.
On it, create a combo box bound to [LitID]. Use a query based on tblLiturgy
as the combo box row source. In the query the first column is the PK, and
the second column is the liturgy name. Set the column count to 2, the bound
column to 1, and the column widths to 0";1".

Add two unbound text boxes to the form. In one, place the following in the
Control Source (watch for line wrapping):
= DLookup("[Prelude]", "tblLiturgy", "LitID =" &
[Forms]![frmServiceRecord]![LitID])

Place the same thing in the other text box control source, except substitute
Gathering for Prelude. I think I have that right. It's getting late on a
Friday.

Now when you select a record from tblLiturgy, the Prelude and Gathering
fields will populate the two unbound text boxes.

In similar fashion to what I outlined for adding Prelude and Gathering, you
can add hymns, selecting the title and displaying the number in an unbound
text box. But I would leave that for now, and concentrate on the two-table
structure I have outlined. You need to see how a single relationship works
in practice before you add other relationships. You may be just getting
started, but this is a long, long way from being a beginner's project.

"linronamy" wrote in message
...
My response is all uppercase.

"BruceM" wrote:

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what
you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):

Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It
does
not vary from year to year. I understand that the hymns change, but
let's
leave them out for now.

CORRECT

Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that

CYCLE B STARTED IN DEC OF 2005 (ADVENT) AND WILL RE-OCCUR IN DEC OF 2008.
IN DEC OF 2006 WE WILL START CYCLE C AND CYCLE A IN DEC 2007.

or are Cycle A, B, and C scattered amongst each other? Of what does a
liturgy part consist other
than the hymn?

THERE ARE ELEVEN DISTINCT PARTS OF EACH LITURGY. THEIR NAMES DO NOT
CHANGE, ONLY THE HYMNS PLAYED DEPENDENT ON THE READINGS FOR THAT LITURGY
AND CYCLE.

For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what?

UPON ENTERING A LITURGY NAME AND THE CYCLE NAME, THEY WILL SEARCH FOR THE
HYMNS AND HYMN NUMBERS FOR
EACH OF THE ELEVEN PARTS OF THAT LITURGY/CYCLE (NO NEED TO SEARCH FOR
READINGS AS THAT HAS ALREADY BEEN
DONE IN THE PAST).

By what criteria will they search.

Liturgy Events (ARE A COMBINATION OF A LITURGY NAME, ITS CYCLE AND A
PARTICULAR PART, PAST OR FUTURE) are the records that will be created as
you go, as I
understand it.

THE VALUE OF A RECORD IS - "WHICH HYMNS WERE PLAYED FOR EACH OF THE ELEVEN
PARTS OF A PARTICULAR LITURGY AND
CYCLE IN THE PAST" - ALLOWING THE OPPORTUNITY TO CHOOSE A HYMN FOR EACH OF
THE ELEVEN PARTS THAT IN EFFECT
HAVE ALREADY BEEN RESEARCHED TO VALIDATE ITS APPROPRIATENESS FOR THAT
READING - "FOR THE FUTURE LITURGY".

For each liturgy event you will select from a list of existing liturgies.
Am I correct so far?

THE LITURGY EVENTS ARE ALL CREATED AND FIXED, MEANING THE LITURGY TABLE
DOES NOT CHANGE, NOR DOES THE CYCLE
TABLE, OR THE PARTS TABLE. THEY ARE THE SUPPORT TABLES USED TO DEFINE AN
EVENT THAT WE WILL BE CHOOSING A HYMN
FOR A FUTURE LITURGY USING DATA FROM THE PAST.

If I am, you can still create a new liturgy, but you would do that
separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same
every
time (every three years, that is)

ABSOLUTELY - IT REPEATS EVERY THREE YEARS.

or can the event details change three years later?
In general, I see it something like this. You will create a liturgy
event,
and (BY) selectING a liturgy AND CYCLE AND PART. Since each liturgy may
be associated with several events (CYCLES AND PARTS COMBINATIONS), but
each event contains just one liturgy, there is a one-to-many relationship
between liturgy events and liturgies (one liturgy, many events I BELIEVE
THAT IS CORRECT). tblLiturgyEvents therefore needs LitID as a foreign
key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo
box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used (YES), or are details
associated
with the event rather than the liturgy, or both? A simplified example
may
help my understanding of this.

EXAMPLE:
1ST WEEK OF DECEMBER STARTS A NEW CYCLE.

IN 2006 IT WILL BE "1ST WEEK OF ADVENT, CYCLE C"

THERE WILL BE ELEVEN PARTS AS IN ALL LITURGIES

WE SELECT A HYMN AND HYMN NUMBER FOR EACH PART

WITH OUR DATABASE WE CAN REVIEW ALL THE HYMNS 'PLAYED FOR EACH PART' AT
EACH
"1ST WEEK OF ADVENT, CYCLE C" IN THE PAST

WE THEN SELECT A HYMN FROM THE DATA 'FOR EACH PART' AND USE THOSE CHOICES
TO
CREATE A NEW RECORD FOR THE "1ST WEEK OF ADVENT, CYCLE C".

WE THEN CREATE A REPORT TO PUBLISH TO ALL MUSICIANS TO ALLOW THEM TO
PRACTICE THE HYMNS IN ADVANCE OF THE LITURGY IN THE 1ST WEEK OF DEC.

One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts?

THEY ARE THE SAME FOR EVERY LITURGY.

THANX FOR YOUR INTEREST, THE MENTAL JUICES ARE FLOWING AGAIN

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will
follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history
to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future
liturgies.

I have a lot of questions about how my current table/relationship
setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite
figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables
are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may
have
many events, but there is no connection between Liturgy and Cycle.
That
each is related to LiturgyEvents does not establish a connection
between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails,
but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent
and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field
by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for
quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do
not
see how your table design can give you the desired results. A record
in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other.
I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in
the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process.
I
can't
afford to hire someone. I don't expect this forum to build my
database;
I
hoped that a few tips heading me in the right direction to my
specific
questions was reasonable. My questions were related to my
experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those
results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If
this
forum
is only for simple Q/A that are easily found thru Access help (F1),
I
am
mistaken to use it. These are my current questions relating to
creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't
the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete
record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3
forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state
it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need
to
either
put in the effort to learn it or hire someone who can do it for
you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very
laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it
up
into
manageable parts, so I would proceed by building one small piece at
a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at
trying
To
setup
a form design to allow data entry for a set(record) of data, I
have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber
fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next
record
in
that
field NOT THE NEXT FIELD on the form??? Do I need
to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice
with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and
I