Where to start

[Steve Schapel]

Linronamy,

It would be possible to use one form for the data entry of all the hymn
assignments. This would involve making a query based on the LitEvents
and Assignments tables, and basing the form on this query. This has 2
major disadvantages over the model I suggested. First, the data '2nd
Sunday of Advent' and 'Cycle B' would have to be entered 11 times
instead of 1. Secondly, there will be 2000 records all on the same form
and very hard to see your way around them.

The ability to use a form/subform structure for data management is one
of the key benefits to using a database like Access.

As regards usability, well, that is another topic. Ultimately you will
need to provide a means of selecting the required tasks and navigating
around the database. Once again, a good book, and a look at some other
database applications (for example the Northwind database that comes
with Access), will help give an overview of what you are trying to
achieve here.

 

[linronamy]

Update:
Upgraded from Access 200 to Access 2003. My tables are still as we last
discussed.
Am spending the last 1 1/2 sstudying sample exercise (NorthWind) to
facilitate my DB education. Am having difficulty getting my arms around the
multiple form for data input rather than single form concept. Trying to
visualize but can't seem to figure how to select/configure fields that make
sense. Any inputs would be greatly appreciated as I want to be correct
following your suggestions.

THANX

 

[Steve Schapel]

Linronamy,

Well, you are certainly on track to become an expert. In the sense that
what you are doing is the only way that I know of to learn this stuff.
It is great. Do it a few more times and it will be second nature .

In the end, there is more that one way to skin the cat, so my version is
not necessarily the way it will end up I suppose. Not only that, but
once your data is in place, you may want to have a number of different
forms &/or reports in order to look at it from a number of angles. But
here's how I see it, as a baseline... You need 2 forms:
1. Single view form, based on the LitEvents table. This will use
comboboxes whose Row Sources are the Liturgies table and the Cycles
table for data entry. So each record on this form will define a Liturgy
for a given Cycle.
2. A continuous view form based on the Assignments table. This form
will be placed on the LitEvents form as a subform, with its
LinkMasterFields and LinkChildFields properties set to LitEventID.
Comboboxes, with their Row Sources being the LitPart and Hymns tables,
will be used for data entry into the LitPart and HymnID fields.

Part of the "secret" here will be using the Properties of the comboboxes
to ensure that the "human-friendly" data is displayed. So, for example,
your main form, based on the LitEvents table, has a combobox bound to
the LitNumber field. The Row Source of this combobox is based on the
Liturgies table, which is basically a lookup table, and already has the
65 data records in it. The Combobox has these property settings:
Bound Column: 1
Column Count: 2
Column Widths: 0;3
The effect of this is that the combobox will list Advent 1st Sunday etc,
and when your selection is made, it will show Advent 1st Sunday or
whatever, but the correct data (i.e. Lit Number) will be in the form's
underlying table. And so on.

So here we have it... Your main form will contain your 200 or whatever
it is records, and you will see each of these records one by one, for
example:
Liturgy - Advent 1st Sunday Cycle - B
And then the subform will list, in continuous view, all the hymn
assignments for that liturgy/cycle event, for example:
Prelude G854 Morning Has Broken

So, see if you can get anywhere with these ideas.

 

[linronamy]

Steve,

I suspect I may be suffering from brain saturation at this point. I felt the
need to review my current tables and relationships with you to verify that I
haven’t gone off track. I realize that I have reverted to continuing to use
the lookup tables (crutch) as I still am not grasping using the original
table to both input data and select from the lookup info as well. Also since
I established the relationships between tables the form design screen/process
has forced subforms that don’t appear to reflect my vision of data entry.
Perhaps these few inputs will provide some awareness of level of confusion.

TABLE DESIGNS:

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

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

TABLE: Hymns
HymnID HymnName HymnNumber
(Pkey, AutoN) (Hymn Name) (Hymn#)
1 Morning Has Broken G748
2 Sing to the Mountains G452
3 We Remember G578

TABLE: Cycles
CycleID CycleName
(Pkey, AutoN) (Cycle designation)
1 A
2 B

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

TABLE: LitPart
LitPartID LitPart
(Pkey, AutoNumber) (Describes the Part of the Liturgy where hymn
is being played/sung.)
1 Prelude
2 Instrumental Processional
3 Gathering

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

TABLE: LitEvents
LitEventID LitNumber CycleID
(Pkey, AutoNum.) (Ref. Liturgies Table) (Ref. Cycles Table)
1 1 1
2 1 2
3 1 3
4 2 4

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

RELATIONSHIPS
(Links are shown using numbers with the ∞ symbol for the MANY side)


LITEVENTS ASSIGNMENTS LITPART
LITURGIES LitEventID4 ASSignID 5LitPartID LITPARTLU
LITUGIESLU LitNumber2 2∞LitNumber 4LitEventID LitPart8 8LitPart
LitName1 1LitName CycleID∞3 LitPartID∞5 HymnID∞6
HYMNS CYCLES
6HymnID 3CycleID HymnName
CycleName7 HymnNumber
CYCLELU 7CycleName

 

[John Vinson]

I still am not grasping using the original
table to both input data and select from the lookup info as well

PMFJI but...

The reason it's hard to grasp using the table to input data is that

you should NEVER use a Table to input data.

Tables are data repositories. They are NOT designed or intended as
user interfaces to the data.

Use a Form based on your table instead. The only time you need to open
a table datasheet is for debugging.

John W. Vinson[MVP]

 

[linronamy]

That was a bit of gobble-d-gook.

"...as I still am not grasping using the original
table to both input data and select from the lookup info as well"

What I meant to say was, "as I still am not grasping REFERENCING the original
table on the FORM I was designing to both input data and select from the
lookup info as well. Sorry, not very clear. I was referring to a previous
response from Steve on 1/17/06.

 

[John Vinson]

What I meant to say was, "as I still am not grasping REFERENCING the original
table on the FORM I was designing to both input data and select from the
lookup info as well. Sorry, not very clear. I was referring to a previous
response from Steve on 1/17/06.

My fault for jumping into the middle of the thread - sorry!

A Form has a Recordsource property; you can view this (and the other)
properties by right-clicking the little square at the upper left of
the form. Select Properties from the dropdown menu.

The Recordsource property might be a table or (more often) a query
based on the table. If your Form's Recordsource is tblMyTable, then
all fields in tblMyTable are available for use on the form.

John W. Vinson[MVP]

 

[linronamy]

Steve,

Okay, a light bulb maybe!!

1st I'm not sure of difference between single and continuous forms (how to
select and configure in design?)

I see a form with LitName and CycleName fields at top of detail followed by
eleven HymnName and HymnNumber field sets with each having a label
identifying it's Liturgy Part. Each label has a control button linked to a
query to view all the hymns associated with the Liturgy and cycle at top for
that LitPart. It would be nice if operator could choose the hymn by clicking
on it to enter it into the next record.

What do you think?