A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

strategy for data entry in multiple tables



 
 
Thread Tools Display Modes
  #11  
Old April 23rd, 2005, 02:16 AM
Paul Overway
external usenet poster
 
Posts: n/a
Default

Yes...the simpler way is what I described previously...although if you want
a clear method for users to add to the listboxes I described, you'd need to
include a "New" button for Birds and one for Events. An alternative is to
display only the junction data and use the not in list event to add new
records for either Birds or Events as needed....the not in list event would
cause the appropriate form to be displayed for the user to enter the Bird or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too distracting for
users. While you do need data entry forms for Birds and Events, they should
be brought up as dialogs just when the user needs to add a new record vs
always there cluttering up the screen.


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


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














  #12  
Old April 23rd, 2005, 04:06 AM
LAF
external usenet poster
 
Posts: n/a
Default

Thanks, Paul,

The problem I see with list boxes is that the database has about 9000 birds
and about 7800 events. Plus, for data verification purposes, the user should
not need to know if the entry is "not in list". Maybe the thing to do is to
keep the 2nd and 3rd forms hidden and become unhidden as necessary.

For example, on the first form, the user would enter bird number (from its
band) and capture.type (whether it is an initial capture or a recapture).
The data validation would be to send the user to an error table with its own
data entry form if capture is initial and the bird number already exists, or
if capture is recapture and the bird number does not exist. These are
errors. Assuming that there is no error, the next step is to determine if a
new eventID is necessary or to bring up an existing one. While an existing
event is probably from a recent record, I prefer to validate the data by
testing whether a similar event exists or not.

The majority of the data entry will be in the junction table and in smaller
tables that have one to one relationship with it. Am I understanding your
previous message?

All the best,

LAF


"Paul Overway" wrote:

Yes...the simpler way is what I described previously...although if you want
a clear method for users to add to the listboxes I described, you'd need to
include a "New" button for Birds and one for Events. An alternative is to
display only the junction data and use the not in list event to add new
records for either Birds or Events as needed....the not in list event would
cause the appropriate form to be displayed for the user to enter the Bird or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too distracting for
users. While you do need data entry forms for Birds and Events, they should
be brought up as dialogs just when the user needs to add a new record vs
always there cluttering up the screen.


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


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















  #13  
Old April 23rd, 2005, 04:41 AM
Paul Overway
external usenet poster
 
Posts: n/a
Default

OK...big list...ditch listboxes and use combo boxes instead. A single form
for the junction data. Bird and Event are combo and you say the bird has a
number. Whenever the data entry person enters a bird number in the junction
data form that is not in the combo list, the not in list event fires and
asks the user if they want to enter a new bird...it then opens up the form
for entering the bird, with the number already entered...then the user just
needs to make whatever other entries are appropriate for Bird, saves the
record and closes the form. If you use the not in list event correctly, the
bird will now be in the list and the user can continue on with the other
junction data entries. Do the same for other fields like event and any
others that are.

Here is an example of the code you'd need for the not in list event:

'This code goes in the Not In List event for the Bird combo in the junction
data entry form
Private Sub cboBird_NotInList(NewData As String, Response As Integer)

Dim i As Integer
Dim Msg As String

Msg = NewData & " is not currently in the list." _
& vbCr & vbCr & "Do you want to add a new bird?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Bird not found")
If i = vbYes Then
Response = acDataErrAdded

'Open form to enter bird
DoCmd.OpenForm "frmBird", , , , acFormAdd, acDialog, NewData

'See if user added the bird...if they did, the new number will
be in the table
If IsNull(DLookup("BirdNum", "tblBird", "BirdNum=" & NewData)) =
False Then
Response = DATA_ERRADDED
Else
Response = DATA_ERRCONTINUE
End If

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub


'This code goes in the entry form for Bird
Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me.txtBirdNum = Me.OpenArgs
End If

End Sub





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


"LAF" wrote in message
...
Thanks, Paul,

The problem I see with list boxes is that the database has about 9000
birds
and about 7800 events. Plus, for data verification purposes, the user
should
not need to know if the entry is "not in list". Maybe the thing to do is
to
keep the 2nd and 3rd forms hidden and become unhidden as necessary.

For example, on the first form, the user would enter bird number (from its
band) and capture.type (whether it is an initial capture or a recapture).
The data validation would be to send the user to an error table with its
own
data entry form if capture is initial and the bird number already exists,
or
if capture is recapture and the bird number does not exist. These are
errors. Assuming that there is no error, the next step is to determine if
a
new eventID is necessary or to bring up an existing one. While an
existing
event is probably from a recent record, I prefer to validate the data by
testing whether a similar event exists or not.

The majority of the data entry will be in the junction table and in
smaller
tables that have one to one relationship with it. Am I understanding your
previous message?

All the best,

LAF


"Paul Overway" wrote:

Yes...the simpler way is what I described previously...although if you
want
a clear method for users to add to the listboxes I described, you'd need
to
include a "New" button for Birds and one for Events. An alternative is
to
display only the junction data and use the not in list event to add new
records for either Birds or Events as needed....the not in list event
would
cause the appropriate form to be displayed for the user to enter the Bird
or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In
List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too distracting
for
users. While you do need data entry forms for Birds and Events, they
should
be brought up as dialogs just when the user needs to add a new record vs
always there cluttering up the screen.


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


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

















  #14  
Old April 23rd, 2005, 06:05 PM
LAF
external usenet poster
 
Posts: n/a
Default

Hi Paul,

Thanks for your help and interest. I am learning how to use combo boxes to
enter data.

However, this approach may not be as useful as multiple forms. New birds
are captured throughout the year, so the user will be driven nuts by all the
pop-ups. In addition, I think it would be better to have the data validation
integrated with the data entry. For example, on the data entry form for
tblBirds, the user will enter the bandnum and the capture.type (the latter a
field in the junction table). There are two sources of error (wrong bandnum
for capture.type, wrong capture.type for bandum) that will open a data entry
form for tblError, with fields to that the user can go back to the data book
and attempt to troubleshoot. This provides a record of problems that would
otherwise be a real pain to detect. If there are no problems, the code I am
writing will either put a new record in tblBirds (and the bandnum field in
tblJunction form) or not put a new record in tblBirds, but copy the bandnum
to tblJunction form).

The data entry form for tblEvents is unlikely to have problems of this type.
The code is very simple to form a recordset of tblEvents and form a sql
string that will select site, date, and time from those variables entered on
the form. Because EventID is an autonumbered field, the main issue is
copying the field from an existing record into the data entry form for
tblJunction or writing a new record and copying EventID from that record.

I still need to work on hiding and unhiding forms, and possibly enabling and
disenabling fields. But as long as the logic is correct, I hope to produce a
data entry system that is seamless to the user (including me). I still think
that data entry with referential integrity is one of the most
underrepresented aspects of Access in the plethora of books that deal with
development.

All the best,

LAF (Lenny Freed)


"Paul Overway" wrote:

OK...big list...ditch listboxes and use combo boxes instead. A single form
for the junction data. Bird and Event are combo and you say the bird has a
number. Whenever the data entry person enters a bird number in the junction
data form that is not in the combo list, the not in list event fires and
asks the user if they want to enter a new bird...it then opens up the form
for entering the bird, with the number already entered...then the user just
needs to make whatever other entries are appropriate for Bird, saves the
record and closes the form. If you use the not in list event correctly, the
bird will now be in the list and the user can continue on with the other
junction data entries. Do the same for other fields like event and any
others that are.

Here is an example of the code you'd need for the not in list event:

'This code goes in the Not In List event for the Bird combo in the junction
data entry form
Private Sub cboBird_NotInList(NewData As String, Response As Integer)

Dim i As Integer
Dim Msg As String

Msg = NewData & " is not currently in the list." _
& vbCr & vbCr & "Do you want to add a new bird?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Bird not found")
If i = vbYes Then
Response = acDataErrAdded

'Open form to enter bird
DoCmd.OpenForm "frmBird", , , , acFormAdd, acDialog, NewData

'See if user added the bird...if they did, the new number will
be in the table
If IsNull(DLookup("BirdNum", "tblBird", "BirdNum=" & NewData)) =
False Then
Response = DATA_ERRADDED
Else
Response = DATA_ERRCONTINUE
End If

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub


'This code goes in the entry form for Bird
Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me.txtBirdNum = Me.OpenArgs
End If

End Sub





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


"LAF" wrote in message
...
Thanks, Paul,

The problem I see with list boxes is that the database has about 9000
birds
and about 7800 events. Plus, for data verification purposes, the user
should
not need to know if the entry is "not in list". Maybe the thing to do is
to
keep the 2nd and 3rd forms hidden and become unhidden as necessary.

For example, on the first form, the user would enter bird number (from its
band) and capture.type (whether it is an initial capture or a recapture).
The data validation would be to send the user to an error table with its
own
data entry form if capture is initial and the bird number already exists,
or
if capture is recapture and the bird number does not exist. These are
errors. Assuming that there is no error, the next step is to determine if
a
new eventID is necessary or to bring up an existing one. While an
existing
event is probably from a recent record, I prefer to validate the data by
testing whether a similar event exists or not.

The majority of the data entry will be in the junction table and in
smaller
tables that have one to one relationship with it. Am I understanding your
previous message?

All the best,

LAF


"Paul Overway" wrote:

Yes...the simpler way is what I described previously...although if you
want
a clear method for users to add to the listboxes I described, you'd need
to
include a "New" button for Birds and one for Events. An alternative is
to
display only the junction data and use the not in list event to add new
records for either Birds or Events as needed....the not in list event
would
cause the appropriate form to be displayed for the user to enter the Bird
or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In
List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too distracting
for
users. While you do need data entry forms for Birds and Events, they
should
be brought up as dialogs just when the user needs to add a new record vs
always there cluttering up the screen.


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


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


















  #15  
Old April 23rd, 2005, 06:55 PM
Paul Overway
external usenet poster
 
Posts: n/a
Default

Actually, the topic is covered very well. However, there is some added
difficulty because you omitted some information that is helpful in
determining what would be the best way to design this.

Stepping back, we need to look at the entire process for entering the
junction data. From what I've been able to gather from you so far, it
appears that some of the steps might be...

1. Enter a BirdNum, BandNum, and capture type
a) Does Bird exist in table?
b) Is BandNum correct for capture type?
2. If BirdNum does not exist, and BandNum/capture type are correct, create
new Bird record and a related capture record...enter additional data
associated with each of the record types....and then continue to subsequent
steps
Else continue to subsequent steps
3. Enter event
a) Does event exist?
4. If Event does not exist, create new event record...enter additional data
associated with this record type...then continue to subsequent steps
Else continue to subsequent steps
5. Enter junction data?

Given that you've said that the number of times that a new entry would be
required for Birds is high, this suggests to me that what you really need
then is a Bird form that can be used both for new entries and to
review/lookup existing entries. Given that the overall entry process seems
somewhat involved, a wizard type form may be appropriate. There is a tool
on web site below that helps create wizards (if you're interested, see
Add-ins/Extras/Tools). The wizard it creates allows for the possibility of
skipping steps, which seems appropriate given what you've described (i.e.,
if the Bird exists, move on).

Your insistance on multiple forms being opened simulateously that aren't
required will be as frustrating to your users as it is to me...you should
reevaluate that decision.

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


"LAF" wrote in message
...
Hi Paul,

Thanks for your help and interest. I am learning how to use combo boxes
to
enter data.

However, this approach may not be as useful as multiple forms. New birds
are captured throughout the year, so the user will be driven nuts by all
the
pop-ups. In addition, I think it would be better to have the data
validation
integrated with the data entry. For example, on the data entry form for
tblBirds, the user will enter the bandnum and the capture.type (the latter
a
field in the junction table). There are two sources of error (wrong
bandnum
for capture.type, wrong capture.type for bandum) that will open a data
entry
form for tblError, with fields to that the user can go back to the data
book
and attempt to troubleshoot. This provides a record of problems that
would
otherwise be a real pain to detect. If there are no problems, the code I
am
writing will either put a new record in tblBirds (and the bandnum field in
tblJunction form) or not put a new record in tblBirds, but copy the
bandnum
to tblJunction form).

The data entry form for tblEvents is unlikely to have problems of this
type.
The code is very simple to form a recordset of tblEvents and form a sql
string that will select site, date, and time from those variables entered
on
the form. Because EventID is an autonumbered field, the main issue is
copying the field from an existing record into the data entry form for
tblJunction or writing a new record and copying EventID from that record.

I still need to work on hiding and unhiding forms, and possibly enabling
and
disenabling fields. But as long as the logic is correct, I hope to
produce a
data entry system that is seamless to the user (including me). I still
think
that data entry with referential integrity is one of the most
underrepresented aspects of Access in the plethora of books that deal with
development.

All the best,

LAF (Lenny Freed)


"Paul Overway" wrote:

OK...big list...ditch listboxes and use combo boxes instead. A single
form
for the junction data. Bird and Event are combo and you say the bird has
a
number. Whenever the data entry person enters a bird number in the
junction
data form that is not in the combo list, the not in list event fires and
asks the user if they want to enter a new bird...it then opens up the
form
for entering the bird, with the number already entered...then the user
just
needs to make whatever other entries are appropriate for Bird, saves the
record and closes the form. If you use the not in list event correctly,
the
bird will now be in the list and the user can continue on with the other
junction data entries. Do the same for other fields like event and any
others that are.

Here is an example of the code you'd need for the not in list event:

'This code goes in the Not In List event for the Bird combo in the
junction
data entry form
Private Sub cboBird_NotInList(NewData As String, Response As Integer)

Dim i As Integer
Dim Msg As String

Msg = NewData & " is not currently in the list." _
& vbCr & vbCr & "Do you want to add a new bird?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Bird not found")
If i = vbYes Then
Response = acDataErrAdded

'Open form to enter bird
DoCmd.OpenForm "frmBird", , , , acFormAdd, acDialog, NewData

'See if user added the bird...if they did, the new number
will
be in the table
If IsNull(DLookup("BirdNum", "tblBird", "BirdNum=" &
NewData)) =
False Then
Response = DATA_ERRADDED
Else
Response = DATA_ERRCONTINUE
End If

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub


'This code goes in the entry form for Bird
Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me.txtBirdNum = Me.OpenArgs
End If

End Sub





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


"LAF" wrote in message
...
Thanks, Paul,

The problem I see with list boxes is that the database has about 9000
birds
and about 7800 events. Plus, for data verification purposes, the user
should
not need to know if the entry is "not in list". Maybe the thing to do
is
to
keep the 2nd and 3rd forms hidden and become unhidden as necessary.

For example, on the first form, the user would enter bird number (from
its
band) and capture.type (whether it is an initial capture or a
recapture).
The data validation would be to send the user to an error table with
its
own
data entry form if capture is initial and the bird number already
exists,
or
if capture is recapture and the bird number does not exist. These are
errors. Assuming that there is no error, the next step is to determine
if
a
new eventID is necessary or to bring up an existing one. While an
existing
event is probably from a recent record, I prefer to validate the data
by
testing whether a similar event exists or not.

The majority of the data entry will be in the junction table and in
smaller
tables that have one to one relationship with it. Am I understanding
your
previous message?

All the best,

LAF


"Paul Overway" wrote:

Yes...the simpler way is what I described previously...although if you
want
a clear method for users to add to the listboxes I described, you'd
need
to
include a "New" button for Birds and one for Events. An alternative
is
to
display only the junction data and use the not in list event to add
new
records for either Birds or Events as needed....the not in list event
would
cause the appropriate form to be displayed for the user to enter the
Bird
or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In
List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too
distracting
for
users. While you do need data entry forms for Birds and Events, they
should
be brought up as dialogs just when the user needs to add a new record
vs
always there cluttering up the screen.


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


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




















  #16  
Old April 23rd, 2005, 07:53 PM
LAF
external usenet poster
 
Posts: n/a
Default

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





"Paul Overway" wrote:

Actually, the topic is covered very well. However, there is some added
difficulty because you omitted some information that is helpful in
determining what would be the best way to design this.

Stepping back, we need to look at the entire process for entering the
junction data. From what I've been able to gather from you so far, it
appears that some of the steps might be...

1. Enter a BirdNum, BandNum, and capture type
a) Does Bird exist in table?
b) Is BandNum correct for capture type?
2. If BirdNum does not exist, and BandNum/capture type are correct, create
new Bird record and a related capture record...enter additional data
associated with each of the record types....and then continue to subsequent
steps
Else continue to subsequent steps
3. Enter event
a) Does event exist?
4. If Event does not exist, create new event record...enter additional data
associated with this record type...then continue to subsequent steps
Else continue to subsequent steps
5. Enter junction data?

Given that you've said that the number of times that a new entry would be
required for Birds is high, this suggests to me that what you really need
then is a Bird form that can be used both for new entries and to
review/lookup existing entries. Given that the overall entry process seems
somewhat involved, a wizard type form may be appropriate. There is a tool
on web site below that helps create wizards (if you're interested, see
Add-ins/Extras/Tools). The wizard it creates allows for the possibility of
skipping steps, which seems appropriate given what you've described (i.e.,
if the Bird exists, move on).

Your insistance on multiple forms being opened simulateously that aren't
required will be as frustrating to your users as it is to me...you should
reevaluate that decision.

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


"LAF" wrote in message
...
Hi Paul,

Thanks for your help and interest. I am learning how to use combo boxes
to
enter data.

However, this approach may not be as useful as multiple forms. New birds
are captured throughout the year, so the user will be driven nuts by all
the
pop-ups. In addition, I think it would be better to have the data
validation
integrated with the data entry. For example, on the data entry form for
tblBirds, the user will enter the bandnum and the capture.type (the latter
a
field in the junction table). There are two sources of error (wrong
bandnum
for capture.type, wrong capture.type for bandum) that will open a data
entry
form for tblError, with fields to that the user can go back to the data
book
and attempt to troubleshoot. This provides a record of problems that
would
otherwise be a real pain to detect. If there are no problems, the code I
am
writing will either put a new record in tblBirds (and the bandnum field in
tblJunction form) or not put a new record in tblBirds, but copy the
bandnum
to tblJunction form).

The data entry form for tblEvents is unlikely to have problems of this
type.
The code is very simple to form a recordset of tblEvents and form a sql
string that will select site, date, and time from those variables entered
on
the form. Because EventID is an autonumbered field, the main issue is
copying the field from an existing record into the data entry form for
tblJunction or writing a new record and copying EventID from that record.

I still need to work on hiding and unhiding forms, and possibly enabling
and
disenabling fields. But as long as the logic is correct, I hope to
produce a
data entry system that is seamless to the user (including me). I still
think
that data entry with referential integrity is one of the most
underrepresented aspects of Access in the plethora of books that deal with
development.

All the best,

LAF (Lenny Freed)


"Paul Overway" wrote:

OK...big list...ditch listboxes and use combo boxes instead. A single
form
for the junction data. Bird and Event are combo and you say the bird has
a
number. Whenever the data entry person enters a bird number in the
junction
data form that is not in the combo list, the not in list event fires and
asks the user if they want to enter a new bird...it then opens up the
form
for entering the bird, with the number already entered...then the user
just
needs to make whatever other entries are appropriate for Bird, saves the
record and closes the form. If you use the not in list event correctly,
the
bird will now be in the list and the user can continue on with the other
junction data entries. Do the same for other fields like event and any
others that are.

Here is an example of the code you'd need for the not in list event:

'This code goes in the Not In List event for the Bird combo in the
junction
data entry form
Private Sub cboBird_NotInList(NewData As String, Response As Integer)

Dim i As Integer
Dim Msg As String

Msg = NewData & " is not currently in the list." _
& vbCr & vbCr & "Do you want to add a new bird?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Bird not found")
If i = vbYes Then
Response = acDataErrAdded

'Open form to enter bird
DoCmd.OpenForm "frmBird", , , , acFormAdd, acDialog, NewData

'See if user added the bird...if they did, the new number
will
be in the table
If IsNull(DLookup("BirdNum", "tblBird", "BirdNum=" &
NewData)) =
False Then
Response = DATA_ERRADDED
Else
Response = DATA_ERRCONTINUE
End If

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub


'This code goes in the entry form for Bird
Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me.txtBirdNum = Me.OpenArgs
End If

End Sub





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


"LAF" wrote in message
...
Thanks, Paul,

The problem I see with list boxes is that the database has about 9000
birds
and about 7800 events. Plus, for data verification purposes, the user
should
not need to know if the entry is "not in list". Maybe the thing to do
is
to
keep the 2nd and 3rd forms hidden and become unhidden as necessary.

For example, on the first form, the user would enter bird number (from
its
band) and capture.type (whether it is an initial capture or a
recapture).
The data validation would be to send the user to an error table with
its
own
data entry form if capture is initial and the bird number already
exists,
or
if capture is recapture and the bird number does not exist. These are
errors. Assuming that there is no error, the next step is to determine
if
a
new eventID is necessary or to bring up an existing one. While an
existing
event is probably from a recent record, I prefer to validate the data
by
testing whether a similar event exists or not.

The majority of the data entry will be in the junction table and in
smaller
tables that have one to one relationship with it. Am I understanding
your
previous message?

All the best,

LAF


"Paul Overway" wrote:

Yes...the simpler way is what I described previously...although if you
want
a clear method for users to add to the listboxes I described, you'd
need
to
include a "New" button for Birds and one for Events. An alternative
is
to
display only the junction data and use the not in list event to add
new
records for either Birds or Events as needed....the not in list event
would
cause the appropriate form to be displayed for the user to enter the
Bird
or
the Event if it isn't in the list.

I think what you are really missing here is the utility of the Not In
List
event in this situation...I'm sure you can do some reading on that.

I'd stay away from having 3 forms up at the same time...too
distracting
for
users. While you do need data entry forms for Birds and Events, they
should
be brought up as dialogs just when the user needs to add a new record
vs
always there cluttering up the screen.


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


  #17  
Old April 23rd, 2005, 08:32 PM
Paul Overway
external usenet poster
 
Posts: n/a
Default

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







  #18  
Old April 23rd, 2005, 09:21 PM
LAF
external usenet poster
 
Posts: n/a
Default

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








  #19  
Old April 25th, 2005, 04:04 AM
Paul Overway
external usenet poster
 
Posts: n/a
Default

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










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Another Multiple Entry TJ General Discussion 2 February 18th, 2005 05:35 PM
Criterion - How to Write Query for Multiple Tables jcinn Running & Setting Up Queries 1 February 8th, 2005 01:42 PM
Multiple tables - Multiple forms (Access 2000) Alastair Using Forms 0 November 12th, 2004 04:30 PM
Querying from multiple tables - newbie question Simon Pleasants General Discussion 2 August 26th, 2004 03:02 PM


All times are GMT +1. The time now is 11:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.