Re: strategy for data entry in multiple tables

Interesting discussion, but it leaves me wondering how tblEvents and tblBirds
are related many-to-many unless an individual bird could be captured several
times. I see how each event could involve many birds, but not the other way
around. What relationship is the junction table resolving? I would have
imagined based on my understanding of the question that it would be Events >
Birds > Bird Details in a cascading one-to-many relationship, with nested
subforms. I don't really need an answer, but I am a bit curious as to how
this discussion will unfold.

"Paul Overway" wrote:

> Ah...ok. I see what you're trying to do now.
>
> I'd have a single unbound entry form for this, with 2 listboxs...one for
> Birds, and another for Events. I'd have an Add button that would add a
> record to a subform for the Junction data. I'd use the selections from the
> 2 listboxes in the link master/link child property for the subform, which
> would allow you to see how the 2 master tables are related and the junction
> data for that combination.
>
> --
> Paul Overway
> Logico Solutions
> http://www.logico-solutions.com
>
>
> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:B666C61F-16CB-4053-A0F4-4D3A63A51E24@xxxxxxxxxxxxxxxx
> > Hello All,
> >
> > Thanks for the stimulating discussion. Let me go back to the original
> > problem. The data entry form is not to look at records, it is for
> > entering
> > (and validating) data. I don't see how a subform for the junction table
> > can
> > be used appropriately with a form for just one of the master tables. The
> > foreign key for the other master table will be present, but the data entry
> > needs to go into the primary key of the other master table.
> >
> > By the way, the database I am developing is for capturing birds in mist
> > nets. We put numbered aluminum bands on the legs of birds we capture.
> > tblBirds lists each individual by band number (=BirdID) as the primary
> > key,
> > with other fields such as species, leftleg, rightleg (for those birds on
> > which we place color bands as well). tblEvents lists each capture event
> > by
> > site, date, and time. The EventID is an autonumbered field that is based
> > on
> > unique combinations of site, date, and time. The junction table deals
> > with
> > the many to many relationship between BirdID and EventID. The junction
> > table
> > has many fields that deal with data collected on birds captured in nets,
> > and
> > is in one to one relationship with tables with specialized data on
> > particular
> > birds. This is where I see a subform for data entry being useful.
> >
> > If both master tables could be used in the same data entry form, then
> > subforms would be feasible for the junction table. Is it possible for two
> > tables to be in the same form, each with their own primary keys, and then
> > to
> > use the junction table as a subform?
> >
> > Thanks,
> >
> > LAF
> >
> > "Paul Overway" wrote:
> >
> >> I think you're misusing or misinstanding keys. You should have a PK in
> >> Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the
> >> junction. You shouldn't be putting an FK in either of the master tables.
> >> Example
> >>
> >> Event
> >> 1 Gala
> >> 2 Dinner
> >>
> >> Subject
> >> 1 Funding
> >> 2 Purpose
> >>
> >> Junction
> >> Event Subject
> >> 1 1
> >> 1 2
> >> 2 2
> >>
> >> In the example above, I've omitted one of the possible combinations.
> >> Nevertheless, we can see for the Gala the subject will be Funding and
> >> Purpose, whereas for the Dinner the only subject will be purpose. If you
> >> have a subform for Junction on the Event form and on the Subject form,
> >> you
> >> can see what subjects will be discussed at the event or at what event the
> >> subject will be discussed.
> >>
> >> --
> >> Paul Overway
> >> Logico Solutions
> >> http://www.logico-solutions.com
> >>
> >>
> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:E136D420-8C1D-4B6E-AEC4-68EA6E0C1D97@xxxxxxxxxxxxxxxx
> >> > Hi Paul,
> >> >
> >> > Thanks for the advice. Let us assume that there is one to many
> >> > relationship
> >> > between a master table and the junction table, for each master table.
> >> > There
> >> > is not necessarily one to one between the two master tables.
> >> >
> >> > In principle then, it appears that the best strategy is to combine data
> >> > entry and validation for each master table separately, and then to copy
> >> > the
> >> > primary field to the data entry form for the junction table. I assume
> >> > that
> >> > there would be no problem with referential integrity to have a new
> >> > record
> >> > in
> >> > a master field with no foreign key, yet, put in a new record in the
> >> > junction
> >> > table. Is this correct?
> >> >
> >> > Thanks,
> >> >
> >> > LAF
> >> >
> >> > "Paul Overway" wrote:
> >> >
> >> >> Comments in-line
> >> >>
> >> >> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:62C2C75F-0B93-47EE-8130-2728711D1134@xxxxxxxxxxxxxxxx
> >> >> >A great strength of Access is the ability to base a form on a
> >> >> >multi-table
> >> >> > query and use the form for data entry. However, there are some
> >> >> > important
> >> >> > issues. Let us assume a simple database with two master tables and
> >> >> > a
> >> >> > junction table. One master table (subjects) has a primary key that
> >> >> > is
> >> >> > a
> >> >> > text
> >> >> > variable. The other master table is an event table with an
> >> >> > autonumbered
> >> >> > EventID as primary key. Assume for this example that tblEventID has
> >> >> > fields
> >> >> > EventID, Site, Date, Time.
> >> >> >
> >> >> > Issue 1. Should there be a single bound data entry form that has
> >> >> > all
> >> >> > fields
> >> >> > from all 3 tables? For this to occur, code or macro must be written
> >> >> > to
> >> >> > run
> >> >> > after the SubjectID is entered in the SubjectID field from
> >> >> > tblSubjecst,
> >> >> > to
> >> >> > determine if a new record should be written in the subjects table.
> >> >> > If
> >> >> > a
> >> >> > new
> >> >> > record does not need to be made, then the SubjectsID field should be
> >> >> > cleared,
> >> >> > but the SubjectsID value placed in the SubjectsID field from
> >> >> > tblJunction.
> >> >>
> >> >> Maybe? What is the relationship between Subject and Event? If 1 to
> >> >> 1,
> >> >> ok.
> >> >> But otherwise, it seems to me subject and event should be separate
> >> >> forms
> >> >> with data from the junction table in a subform (if it makes sense to
> >> >> show
> >> >> the related data in both places). You seem to be concerned about
> >> >> duplicate
> >> >> data in subject. Ok, so, do some validation before update/insert for
> >> >> subject records....and if there is a duplicate, cancel the update and
> >> >> go
> >> >> to
> >> >> the preexisting record or prompt the user to do so.
> >> >>
> >> >> >
> >> >> > Issue 2. A bigger problem occurs when data are entered for tblEvent
> >> >> > on
> >> >> > the
> >> >> > single bound entry form. Code or macro must be written to determine
> >> >> > if
> >> >> > the
> >> >> > non-primary key fields are already present in tblEvents. If they
> >> >> > are,
> >> >> > then
> >> >> > the EventID from the matching record should be copied into EventID
> >> >> > field
> >> >> > on
> >> >> > the data entry form for tblEvents. If not, then a new record must
> >> >> > be
> >> >> > written
> >> >> > into tblEvent and the autonumber copied into the data transfer form.
> >> >> >
> >> >>
> >> >> This seems very similar to issue 1, and the solution is as well.
> >> >>
> >> >> > The big question is: should there be separate data entry forms for
> >> >> > the
> >> >> > two
> >> >> > master tables, possibly on unbound forms that pass appropriate
> >> >> > values
> >> >> > to
> >> >> > the
> >> >> > relevant data entry forms, or to an error event? The tblJunction
> >> >> > data
> >> >> > entry
> >> >> > form would start out with the passed values in the foreign key
> >> >> > fields.
> >> >> > Is
> >> >> > there a better strategy for dealing data entry with referential
> >> >> > integrity
> >> >> > and
> >> >> > cascade updating.
> >> >>
> >> >> Yes 2 forms....with a subform for the junction data. Whether bound or
> >> >> unbound, you're going to need to do validation to ensure that any new
> >> >> record
> >> >> is not a duplicate of an existing record. I'm not sure why you'd have
> >> >> the
> >> >> junction data on yet another form, if it is related to the master
> >> >> tables....just use a subform if possible....then the foreign key for
> >> >> either
> >> >> subject or event would be passed automatically.
> >> >>
> >> >> >
> >> >> > LAF
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.