Re: strategy for data entry in multiple tables

It makes sense because birds are being captured collectively as a group in
nets. If they were being captured individually, the junction would be
unnecessary....a bird would be related to events in a one to many. But
because the birds are captured collectively, the junction is used to resolve
what birds were captured during an event and vice versa.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


"BruceM" <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1F96B97E-6A9E-4A84-B911-C527B1EBD230@xxxxxxxxxxxxxxxx
> 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.