Re: strategy for data entry in multiple tables

Thanks Paul and Bruce,

This is what makes the community group so valuable.

The issue of data entry for master tables and junction tables, with
referential integrity and cascading update, has to be one of the most general
in Access. It is completely neglected in the 5 or so books I have on Access.


The following subissues are involved in every single access data base with
referential integrity.

1. Need to determine if a new record needs to be written in neither, one,
or both master tables.

2. The master table primary key values, whether for newly written record or
previous record, need to be entered into appropriate fields of the junction
table data entry form.

I am currently experimenting with a 3-form model, one form for each master
table and one for the junction table. All 3 will be on screen. The user
will first use one of the master table forms to determine if a new record
needs to be written. If so, the data entered on this form will be used to
create the new record, and the primary key value will be copied to the
foreign key field on the junction data entry form. If a new record does not
need to be written, the primary key value that was inputted will copied to
the foreign key field (through code).

This procedure will occur for each master table in sequence. The user will
simply be left with the larger junction table data entry form with the
foreign keys filled in from the previous master table forms. Subforms can
then be used for data tables that are in one to one correspondence with the
junction table.

This is a little involved, but the code that underlies this serves for data
validation as well as entry. My question thus becomes: is there a simpler
way to get the foreign key values on the junction table data entry form?

All the best,
LAF


"Paul Overway" wrote:

> 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Loading