Re: strategy for data entry in multiple tables

I sometimes use composite keys to prevent duplicates in a table, but I still
put a Autonumber on the table with Index (No Duplicates)...and then I use
the autonumber as FK in other tables (if needed)....even though it isn't set
as the primary key, it still works for an FK. Easier to query that way.

I wouldn't split date and time...if you need to provide separate fields on
the form, ok, but keep it in one field...easier to query and use later on.

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


"LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D153501-8879-4A9F-92B2-26936ED219DB@xxxxxxxxxxxxxxxx
> 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
>> >
>> >
>> >
>> >
>> >
>>
>>
>>


.