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" wrote in message
...
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" wrote in message
...
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