Relationships set up

The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11 parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many, (T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID Assign2ID
LitEventID() ()LitEventID() ()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one record.

My problem is when I enter data into the 3 forms they are not linked as one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

i'm not sure what the Assign tables are doing, but let's put that aside for
a moment. i'm more concerned about "There are 11 tables containing data each
unique to one of 11 parts, all having the same type link to the Assign
tables"

that sounds suspiciously like a non-normalized design. please post the table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name, please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11 parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many, (T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one record.

My problem is when I enter data into the 3 forms they are not linked as
one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with AutoNum types
and are the (1) side of the relationship to the Assign1 or Assign2 Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put that aside for
a moment. i'm more concerned about "There are 11 tables containing data each
unique to one of 11 parts, all having the same type link to the Assign
tables"

that sounds suspiciously like a non-normalized design. please post the table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name, please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11 parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many, (T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one record.

My problem is when I enter data into the 3 forms they are not linked as
one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

okay, as i thought, the section of your structure made up of those 11 tables
is non-normalized; any time you put data (preparation, precomminstr) into
table or field names, you're violating data normalization principles.
suggest that you put all the hymns into one table, with a field to designate
what category each one belongs to (preparation, precomminstr, etc), as

tblHymns
HymnNumber (recommend you do *not* use the # sign in a field name)
HymnName
Category

now, can you explain to me what the "ASSIGN1" and "ASSIGN2" data is about,
and how it relates to LITEVENTS? don't use database terms; explain it in
plain English as though you were telling a new church member how things are
done at your church. to get started: "we have x liturgies, and each liturgy
has x cycles. each cycle of each liturgy has x events, and..."

hth


"linronamy" wrote in message
...
Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with AutoNum types
and are the (1) side of the relationship to the Assign1 or Assign2 Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put that aside
for
a moment. i'm more concerned about "There are 11 tables containing data
each
unique to one of 11 parts, all having the same type link to the Assign
tables"

that sounds suspiciously like a non-normalized design. please post the
table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name,
please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11 parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many,
(T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one
record.

My problem is when I enter data into the 3 forms they are not linked
as
one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

Explanation:
There are three Liturgical Cycles(A, B, C) each lasting 1 year with the
complete duration being 3 years.

There are 52 litugies each year or Cycle. The readings and hymns are
different for each liturgy of each Cycle (week 1 of cycle A is different from
week 1 of Cycles B and C).

The liturgy and its Cycle is an event (in my database).

There are 11 parts for each Liturgy where there is a Hymn played in each
part and they are titled by the following Liturgy order:

TABLE ASSIGN1 (Liturgy of the Word)
Prelude (Prl)
Instrumental Procession (InProc)
Gathering (Gath)
Responsorial Psalm (RPs)
Gospel Acclamation (GA)

TABLE ASSIGN2 (Liturgy of the Eucharist)
Preparation
Pre Communion Instrumental (PrCom)
Communion1 (Com1)
Communion2 (Com2)
Closing (Cl)
Instrumental Postlude (InPost)

I separated the Parts into two tables (Assign1 and Assign2) because Access
2003 wouldn't allow more than 8 fields with referential integrity links in a
table(??? I think!!! - read it somewhere)

A complete record in the database would contain a Liturgy Name and its
corresponding Cycle along with 11 Hymns and Hymn numbers.

I have 6 years of history, one liturgy record per sheet.

The goal is to review the history to assemble a new liturgy record for an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn Numbers
should show all Preparation hymns played during that Liturgy/Cycle to be used
to select from, to enter data for the new record. Sorry, I hope that is clear.

A Question in All Caps:

"tina" wrote:

okay, as i thought, the section of your structure made up of those 11 tables
is non-normalized; any time you put data (preparation, precomminstr) into
table or field names, you're violating data normalization principles.
I DON'T UNDERSTAND. I THOUGHT SEGREGATING HYMNS AND THEIR HYMN NUMBER INTO A
TABLE UNIQUE TO THE PART OF THE LITURGY WOULD MAKE IT EASIER TO QUERY LATER.
WHERE AM I GOING ASTRAY? WILL FOLLOW YOUR LEAD WITH ENTHUSIASM.
suggest that you put all the hymns into one table, with a field to designate
what category each one belongs to (preparation, precomminstr, etc), as

tblHymns
HymnNumber (recommend you do *not* use the # sign in a field name)
HymnName
Category

now, can you explain to me what the "ASSIGN1" and "ASSIGN2" data is about,
and how it relates to LITEVENTS? don't use database terms; explain it in
plain English as though you were telling a new church member how things are
done at your church. to get started: "we have x liturgies, and each liturgy
has x cycles. each cycle of each liturgy has x events, and..."

hth


"linronamy" wrote in message
...
Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with AutoNum types
and are the (1) side of the relationship to the Assign1 or Assign2 Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put that aside
for
a moment. i'm more concerned about "There are 11 tables containing data
each
unique to one of 11 parts, all having the same type link to the Assign
tables"

that sounds suspiciously like a non-normalized design. please post the
table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name,
please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11 parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many,
(T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one
record.

My problem is when I enter data into the 3 forms they are not linked
as
one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

overall, your explanation was good, and helped me understand what you're
doing. there is one part that i'm not sure of:

The goal is to review the history to assemble a new liturgy record for an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn Numbers
should show all Preparation hymns played during that Liturgy/Cycle to be
used
to select from, to enter data for the new record. Sorry, I hope that is
clear.

from this, it sounds like the liturgy is a 3-year repeating cycle, but each
time a specific liturgy (church service?) in a specific cycle is
"delivered", different hymns may be used in each of the 11 parts of the
liturgy. in other words, you don't have an iron-clad schedule of "liturgy
43, cycle B, preparation, 'How Great Thou Art' hymn #117".

so over a six year period, liturgy 43 of cycle B would have been "delivered"
twice, with possibly a different preparation hymn sung on each occasion.

assuming that the above is correct, here are the tables i suggest. (a few
are the same as the tables you posted; but, as with the 11 "parts" tables
setup, you were again violating normalization principles by putting data
into field names, in your ASSIGN1 and ASSIGN2 tables.)

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)
you *can* use an Autonumber for the primary key if you prefer, but since
you only have 52 liturgies, Byte is plenty big enough - since it accepts
numeric values from 0-255

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)
ditto the above per the primary key field's data type, since you only have
3 cycles.

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)
ditto the above per the primary key field's data type, since you only have
11 parts.

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 would be
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

note that "1:n" is a common way of indicating "one-to-many".

hth


"linronamy" wrote in message
...
Explanation:
There are three Liturgical Cycles(A, B, C) each lasting 1 year with the
complete duration being 3 years.

There are 52 litugies each year or Cycle. The readings and hymns are
different for each liturgy of each Cycle (week 1 of cycle A is different
from
week 1 of Cycles B and C).

The liturgy and its Cycle is an event (in my database).

There are 11 parts for each Liturgy where there is a Hymn played in each
part and they are titled by the following Liturgy order:

TABLE ASSIGN1 (Liturgy of the Word)
Prelude (Prl)
Instrumental Procession (InProc)
Gathering (Gath)
Responsorial Psalm (RPs)
Gospel Acclamation (GA)

TABLE ASSIGN2 (Liturgy of the Eucharist)
Preparation
Pre Communion Instrumental (PrCom)
Communion1 (Com1)
Communion2 (Com2)
Closing (Cl)
Instrumental Postlude (InPost)

I separated the Parts into two tables (Assign1 and Assign2) because Access
2003 wouldn't allow more than 8 fields with referential integrity links in
a
table(??? I think!!! - read it somewhere)

A complete record in the database would contain a Liturgy Name and its
corresponding Cycle along with 11 Hymns and Hymn numbers.

I have 6 years of history, one liturgy record per sheet.

The goal is to review the history to assemble a new liturgy record for an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn Numbers
should show all Preparation hymns played during that Liturgy/Cycle to be
used
to select from, to enter data for the new record. Sorry, I hope that is
clear.

A Question in All Caps:

"tina" wrote:

okay, as i thought, the section of your structure made up of those 11
tables
is non-normalized; any time you put data (preparation, precomminstr)
into
table or field names, you're violating data normalization principles.
I DON'T UNDERSTAND. I THOUGHT SEGREGATING HYMNS AND THEIR HYMN NUMBER INTO
A
TABLE UNIQUE TO THE PART OF THE LITURGY WOULD MAKE IT EASIER TO QUERY
LATER.
WHERE AM I GOING ASTRAY? WILL FOLLOW YOUR LEAD WITH ENTHUSIASM.
suggest that you put all the hymns into one table, with a field to
designate
what category each one belongs to (preparation, precomminstr, etc), as

tblHymns
HymnNumber (recommend you do *not* use the # sign in a field name)
HymnName
Category

now, can you explain to me what the "ASSIGN1" and "ASSIGN2" data is
about,
and how it relates to LITEVENTS? don't use database terms; explain it in
plain English as though you were telling a new church member how things
are
done at your church. to get started: "we have x liturgies, and each
liturgy
has x cycles. each cycle of each liturgy has x events, and..."

hth


"linronamy" wrote in message
...
Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with AutoNum
types
and are the (1) side of the relationship to the Assign1 or Assign2
Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put that
aside
for
a moment. i'm more concerned about "There are 11 tables containing
data
each
unique to one of 11 parts, all having the same type link to the
Assign
tables"

that sounds suspiciously like a non-normalized design. please post
the
table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name,
please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11
parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many,
(T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its
Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one
record.

My problem is when I enter data into the 3 forms they are not
linked
as
one
record.
I got here thru posting advice and am now very confused.
Any suggestions or questions appreciated.

Awesome. I've recreated all tables per your post, and it appears to make
sense to me, i guess what i am saying is that for the first time i feel good
that i don't have to wonder about the Table structures. I would now like to
work on data entry forms. The key word is I. I really appreciate your help
but I want to try to learn this stuff. i feel comfortable with design layout
and using a switchboard menu to make the forms user friendly. I'm not sure
about selecting the tables/fields main forms vs. subforms and how that all
works. Any short tips on they fit with each other would be greatly appreciated

THANX Tina

"tina" wrote:

overall, your explanation was good, and helped me understand what you're
doing. there is one part that i'm not sure of:

The goal is to review the history to assemble a new liturgy record for an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn Numbers
should show all Preparation hymns played during that Liturgy/Cycle to be
used
to select from, to enter data for the new record. Sorry, I hope that is
clear.

from this, it sounds like the liturgy is a 3-year repeating cycle, but each
time a specific liturgy (church service?) in a specific cycle is
"delivered", different hymns may be used in each of the 11 parts of the
liturgy. in other words, you don't have an iron-clad schedule of "liturgy
43, cycle B, preparation, 'How Great Thou Art' hymn #117".

so over a six year period, liturgy 43 of cycle B would have been "delivered"
twice, with possibly a different preparation hymn sung on each occasion.

assuming that the above is correct, here are the tables i suggest. (a few
are the same as the tables you posted; but, as with the 11 "parts" tables
setup, you were again violating normalization principles by putting data
into field names, in your ASSIGN1 and ASSIGN2 tables.)

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)
you *can* use an Autonumber for the primary key if you prefer, but since
you only have 52 liturgies, Byte is plenty big enough - since it accepts
numeric values from 0-255

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)
ditto the above per the primary key field's data type, since you only have
3 cycles.

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)
ditto the above per the primary key field's data type, since you only have
11 parts.

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 would be
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

note that "1:n" is a common way of indicating "one-to-many".

hth


"linronamy" wrote in message
...
Explanation:
There are three Liturgical Cycles(A, B, C) each lasting 1 year with the
complete duration being 3 years.

There are 52 litugies each year or Cycle. The readings and hymns are
different for each liturgy of each Cycle (week 1 of cycle A is different
from
week 1 of Cycles B and C).

The liturgy and its Cycle is an event (in my database).

There are 11 parts for each Liturgy where there is a Hymn played in each
part and they are titled by the following Liturgy order:

TABLE ASSIGN1 (Liturgy of the Word)
Prelude (Prl)
Instrumental Procession (InProc)
Gathering (Gath)
Responsorial Psalm (RPs)
Gospel Acclamation (GA)

TABLE ASSIGN2 (Liturgy of the Eucharist)
Preparation
Pre Communion Instrumental (PrCom)
Communion1 (Com1)
Communion2 (Com2)
Closing (Cl)
Instrumental Postlude (InPost)

I separated the Parts into two tables (Assign1 and Assign2) because Access
2003 wouldn't allow more than 8 fields with referential integrity links in
a
table(??? I think!!! - read it somewhere)

A complete record in the database would contain a Liturgy Name and its
corresponding Cycle along with 11 Hymns and Hymn numbers.

I have 6 years of history, one liturgy record per sheet.

The goal is to review the history to assemble a new liturgy record for an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn Numbers
should show all Preparation hymns played during that Liturgy/Cycle to be
used
to select from, to enter data for the new record. Sorry, I hope that is
clear.

A Question in All Caps:

"tina" wrote:

okay, as i thought, the section of your structure made up of those 11
tables
is non-normalized; any time you put data (preparation, precomminstr)
into
table or field names, you're violating data normalization principles.
I DON'T UNDERSTAND. I THOUGHT SEGREGATING HYMNS AND THEIR HYMN NUMBER INTO
A
TABLE UNIQUE TO THE PART OF THE LITURGY WOULD MAKE IT EASIER TO QUERY
LATER.
WHERE AM I GOING ASTRAY? WILL FOLLOW YOUR LEAD WITH ENTHUSIASM.
suggest that you put all the hymns into one table, with a field to
designate
what category each one belongs to (preparation, precomminstr, etc), as

tblHymns
HymnNumber (recommend you do *not* use the # sign in a field name)
HymnName
Category

now, can you explain to me what the "ASSIGN1" and "ASSIGN2" data is
about,
and how it relates to LITEVENTS? don't use database terms; explain it in
plain English as though you were telling a new church member how things
are
done at your church. to get started: "we have x liturgies, and each
liturgy
has x cycles. each cycle of each liturgy has x events, and..."

hth


"linronamy" wrote in message
...
Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with AutoNum
types
and are the (1) side of the relationship to the Assign1 or Assign2
Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put that
aside
for
a moment. i'm more concerned about "There are 11 tables containing
data
each
unique to one of 11 parts, all having the same type link to the
Assign
tables"

that sounds suspiciously like a non-normalized design. please post
the
table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its' name,
please
explain it.

hth


"linronamy" wrote in message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11
parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many,
(T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its
Cycle.
The two subforms are data entry for the 11 parts(Hymn and Humn#).
Together the Main and both Subforms datas combine to define one
record.

My problem is when I enter data into the 3 forms they are not
linked

your main data tables are tblLiturgyEvents and tblEventDetails. suggest you
use a standard mainform/subform setup for data entry. the other tables are
what i call "supporting" tables; they basically provide the "choices" that
the user will select from when entering data in the main data tables. within
the mainform/subform setup, use combobox controls, bound to the foreign key
fields, with RowSources based on the tables that the foreign key fields are
linked to. that way the user can choose from "droplists" to fill in the
those fields in the main data tables. this is also a standard setup.

your original post was about problems with a data entry form, i know. but
when those problems are caused by poor table design, the best thing to do is
to fix that, and then start fresh on the forms; usually form design flows
pretty naturally from a proper table structure. the alternative to
suggesting a specific table structure was to simply recommend that you learn
the basic principles of data modeling, so you'll know how to do it yourself.
actually, i do recommend that you learn those principles anyway; you'll need
to understand *why* the suggested table structure is correct, so you can set
up your next database correctly or expand this one in the future. data
modeling, or normalization, is not a trivial subject, so be prepared to put
some elbow grease into it. see
http://home.att.net/~california.db/tips.html#aTip1 for more information
(suggest you review the rest of the tips too, to avoid some common
pitfalls).

hth


"linronamy" wrote in message
...
Awesome. I've recreated all tables per your post, and it appears to make
sense to me, i guess what i am saying is that for the first time i feel
good
that i don't have to wonder about the Table structures. I would now like
to
work on data entry forms. The key word is I. I really appreciate your help
but I want to try to learn this stuff. i feel comfortable with design
layout
and using a switchboard menu to make the forms user friendly. I'm not sure
about selecting the tables/fields main forms vs. subforms and how that all
works. Any short tips on they fit with each other would be greatly
appreciated

THANX Tina

"tina" wrote:

overall, your explanation was good, and helped me understand what you're
doing. there is one part that i'm not sure of:

The goal is to review the history to assemble a new liturgy record for
an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn
Numbers
should show all Preparation hymns played during that Liturgy/Cycle to
be
used
to select from, to enter data for the new record. Sorry, I hope that
is
clear.

from this, it sounds like the liturgy is a 3-year repeating cycle, but
each
time a specific liturgy (church service?) in a specific cycle is
"delivered", different hymns may be used in each of the 11 parts of the
liturgy. in other words, you don't have an iron-clad schedule of
"liturgy
43, cycle B, preparation, 'How Great Thou Art' hymn #117".

so over a six year period, liturgy 43 of cycle B would have been
"delivered"
twice, with possibly a different preparation hymn sung on each occasion.

assuming that the above is correct, here are the tables i suggest. (a
few
are the same as the tables you posted; but, as with the 11 "parts"
tables
setup, you were again violating normalization principles by putting data
into field names, in your ASSIGN1 and ASSIGN2 tables.)

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)
you *can* use an Autonumber for the primary key if you prefer, but
since
you only have 52 liturgies, Byte is plenty big enough - since it accepts
numeric values from 0-255

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)
ditto the above per the primary key field's data type, since you only
have
3 cycles.

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)
ditto the above per the primary key field's data type, since you only
have
11 parts.

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 would be
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

note that "1:n" is a common way of indicating "one-to-many".

hth


"linronamy" wrote in message
...
Explanation:
There are three Liturgical Cycles(A, B, C) each lasting 1 year with
the
complete duration being 3 years.

There are 52 litugies each year or Cycle. The readings and hymns are
different for each liturgy of each Cycle (week 1 of cycle A is
different
from
week 1 of Cycles B and C).

The liturgy and its Cycle is an event (in my database).

There are 11 parts for each Liturgy where there is a Hymn played in
each
part and they are titled by the following Liturgy order:

TABLE ASSIGN1 (Liturgy of the Word)
Prelude (Prl)
Instrumental Procession (InProc)
Gathering (Gath)
Responsorial Psalm (RPs)
Gospel Acclamation (GA)

TABLE ASSIGN2 (Liturgy of the Eucharist)
Preparation
Pre Communion Instrumental (PrCom)
Communion1 (Com1)
Communion2 (Com2)
Closing (Cl)
Instrumental Postlude (InPost)

I separated the Parts into two tables (Assign1 and Assign2) because
Access
2003 wouldn't allow more than 8 fields with referential integrity
links in
a
table(??? I think!!! - read it somewhere)

A complete record in the database would contain a Liturgy Name and its
corresponding Cycle along with 11 Hymns and Hymn numbers.

I have 6 years of history, one liturgy record per sheet.

The goal is to review the history to assemble a new liturgy record for
an
upcoming Liturgy by selecting each Hymn/Hymn Number from past records.

(Sample Query: Liturgy 43, Cycle B, Preparation - Hymns and Hymn
Numbers
should show all Preparation hymns played during that Liturgy/Cycle to
be
used
to select from, to enter data for the new record. Sorry, I hope that
is
clear.

A Question in All Caps:

"tina" wrote:

okay, as i thought, the section of your structure made up of those
11
tables
is non-normalized; any time you put data (preparation, precomminstr)
into
table or field names, you're violating data normalization
principles.
I DON'T UNDERSTAND. I THOUGHT SEGREGATING HYMNS AND THEIR HYMN NUMBER
INTO
A
TABLE UNIQUE TO THE PART OF THE LITURGY WOULD MAKE IT EASIER TO QUERY
LATER.
WHERE AM I GOING ASTRAY? WILL FOLLOW YOUR LEAD WITH ENTHUSIASM.
suggest that you put all the hymns into one table, with a field to
designate
what category each one belongs to (preparation, precomminstr, etc),
as

tblHymns
HymnNumber (recommend you do *not* use the # sign in a field name)
HymnName
Category

now, can you explain to me what the "ASSIGN1" and "ASSIGN2" data is
about,
and how it relates to LITEVENTS? don't use database terms; explain
it in
plain English as though you were telling a new church member how
things
are
done at your church. to get started: "we have x liturgies, and each
liturgy
has x cycles. each cycle of each liturgy has x events, and..."

hth


"linronamy" wrote in message
...
Legend: ()=Link w/o Referential Integrity,
(1)=One, (M)=Many

LITURGIES
LitID(PK, AutoNum)(1)
LitName(Text)

CYCLES
CycleID(PK, AutoNum)(1)
CycleName(Text)

LITEVENTS
LitEventID(PK, AutoNum)
LitID(Number)(M)
CycleID(Number)(M)

ASSIGN1
Assign1ID(PK, AutoNum)
LitEvent1ID(Number)()
PrepID(Number)(M)
PrComID(Number)(M)
Com1ID(Number)(M)
Com2ID(Number)(M)
ClID(Number)(M)
InPostID(Number)(M)

ASSIGN2
Assign2ID(PK, AutoNum)
LitEvent2ID(Number)()
PrlID(Number)(M)
InProcID(Number)(M)
GathID(Number)(M)
RPsID(Number)(M)
GAlID(Number)(M)

Each of 11 Tables that follow, link to corresponding fields(names)
in Assign1 or Assign2 tables. Each xxxxID field is a PK with
AutoNum
types
and are the (1) side of the relationship to the Assign1 or Assign2
Tables.
Data is input to these tables.

PREPARATION
PrepID(PK, AutoNum)(1)
PrepHymn(Text)
PrepHymn#(Text)

PRECOMMINSTR
PreComID(PK,AutoNum)(1)
PreComHymn(Text)
PreComHymn#(Text

Etc., Etc. thru 11 tables


"tina" wrote:

i'm not sure what the Assign tables are doing, but let's put
that
aside
for
a moment. i'm more concerned about "There are 11 tables
containing
data
each
unique to one of 11 parts, all having the same type link to the
Assign
tables"

that sounds suspiciously like a non-normalized design. please
post
the
table
and field names of these tables in the following format:

Table1Name
FieldName (primary key, data type)
NextFieldName (data type)
NextFieldName (data type)
(etc)

if the purpose of the field is not readily apparent from its'
name,
please
explain it.

hth


"linronamy" wrote in
message
...
The following is a view of my database:

Table Relationships (which are questionably setup)

Liturgies(1) to LitEvents(many) and Cycles(1) to
LitEvents(many)
Assign1() to LitEvents() and Assign2() to LitEvents()

There are 11 tables containing data each unique to one of 11
parts,
all having the same type link to the Assign tables,
5 to Assign1 and 6 to Assign2, each being (1) to the
Assign(many)

Looks Like:
Legend: ()=Link w/o Referential Integrity, (1)=One, (M)=Many,
(T)=Table

Litugies(T) LitEvent(T)
LitName
LitID(1) (M)LitID

Cycles(T)
CycleName
CyclesID(1) (M)CycleID


Assign1(T)
Assign2(T)
Assign1ID
Assign2ID
LitEventID() ()LitEventID()
()LitEventID

PrepID(M) (1)PrepID

PrepHymn PrepHymn

PrepHymn# PrepHymn#

(above PREPs is example of

11 PARTs tables)

I have a main form with two subforms.
Main form linked to LitEvents Table
Subform linked to Assign1
Subform linked to Assign2

The Main form is data entry for defining the Liturgy and its
Cycle.
The two subforms are data entry for the 11 parts(Hymn and
Humn#).
Together the Main and both Subforms datas combine to define
one
record.

My problem is when I enter data into the 3 forms they are not
linked