Re: strategy for data entry in multiple tables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks again, I need to study creating classes.

I do not think that users will be entering data unnecessarily. The fields
in tblBirds are bandnum, species, leftleg, rightleg. There have been entry
errors in the past for each of these fields, so species, leftleg, and
rightleg will be checked against existing entries for recaptured individuals.
The fields in the tblEvents are EventID (autonumbered), site, date, time.
Site, date, and time need to be entered to check for existing EventID's. So,
what appears to be excess entry is necessary for data validation and for
correct EventID.

I was hoping to use the junction table (with its two foreign keys as a
composite primary key) to deal with other data tables in one to one
relationship with their composite primary keys. Could these other data
tables be opened as subforms within the junction table form? I am assuming
that a one to one relationship is a limiting case of a one to many
relationship.

I won't be able to reply soon to this but I look forward to your response.

All the best,

LAF







"Paul Overway" wrote:

> If you do a wizard, you'll have collected the foreign keys in the early
> steps before you do the junction data. I wouldn't have the user enter ALL
> the bird info...just the info necessary to determine whether the bird exists
> in the table....then if it doesn't, they'd fill in whatever extra info is
> needed (you'd already have the birdnum, and that could be automatically set
> for them), and skip that step or go to a review Bird record step if the bird
> exists. You'll have to write code to collect information entered or
> retrieved during each step in the process/wizard, and then one of the final
> steps would be to collect the info for the junction data. Once you have all
> the data, then you can insert it. You might want to create a class for
> this. The entry of the BirdNum and Event will not be automatic unless
> you're using a subform. So, you need to write code to accomplish the
> task...or collect the BirdNum and Event and then use a subform for the
> junction data whose link master/child properties is set to the BirdNum and
> Event required (after you've collected them).
>
> In respect to an explicit reference concerning referential integrity and
> data entry, none come to mind. However, to have any undertanding of
> referential integrity, one must know that a record in an enforced one to n
> relationship cannot be inserted unless the related record exists. Once you
> undertand and accept this, there is no problem. One must do whatever is
> necessary for the application to ensure that the related records are
> entered.
>
>
> --
> Paul Overway
> Logico Solutions
> http://www.logico-solutions.com
>
>
> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:1FDC9D74-F839-41B3-A61C-E753F97D10D3@xxxxxxxxxxxxxxxx
> > Thanks again,
> >
> > I think that when I use the expression "multiple forms" I am thinking of a
> > wizard, and the logic progression you have specified is correct. The only
> > thing that might be missing is getting the junction table foreign keys
> > from
> > the appropriate record in the bird table and in the event table.
> >
> > Indeed, I envision an unbound form for the initial step that, after
> > checking
> > for error and finding none, will write a record in the bird table, as
> > appropriate, and also copy the appropriate field in the junction table
> > data
> > entry form. The error checking for recaptured birds would include the
> > species, leftleg, and rightleg fields. With this, the user is entering
> > all
> > fields for tblBirds, with validation if a recapture, and with simple new
> > record if an initial capture. All this could occur while the user sees
> > just
> > the unbound form.
> >
> > The same unbound form could have text boxes for fields: site, date, and
> > time. When the user enters these, and then clicks another control button,
> > the code will determine if these fields correspond to an existing ID or if
> > a
> > new record needs to be written in tblEvents. Once the bandnum and EventID
> > have been copied to the data entry form for the junction table, the
> > unbound
> > form will close and the junction table form open with appropriate bandnum
> > and
> > eventID fields filled in.
> >
> > Am I incorrect in assuming that all these events could occur behind the
> > scenes? The user would simply enter the data as if everything was a new
> > record in birds and event, and the code would determine if either was
> > approriate, and put the correct information in the foreign key fields in
> > the
> > junction table form.
> >
> > I have looked at Rick Dobson's book, Alison Balter's book, Getz et al.'s
> > book, and Cardoza et al.'s book. None of them appear to deal with data
> > entry
> > problems of referential integrity except in list boxes and combo tables.
> > Dobson deals with lookup queries that use the criteria from a form text
> > box.
> > I would appreciate knowing a reference that emphasizes issues associated
> > with
> > data entry. PS I have reduced the size of the post because it is getting
> > too long.
> >
> > Thanks,
> >
> > LAF
> >
> >
> >
> >
> >
>
>
>
.

Quantcast