Sending data to different control sources

[Richard Harison]

My addled brain needs some help! My database is for a volunteer victims in
crisis organization. The heart of it is an incident form which contains
data relevant to each individual incident.One section has 6 drop down boxes
which all get their row source from the same table, which contains all the
organizations to which a victim might be referred. Thus any victim could be
referred to up to 6 different places. Each DD box has its own control source
field. The form works great, but I have to generate a report which lists
all the organizations and the number of referrals made to each. Thus I have
to combine all the data from the 6 different fields to use as a recordsource
for the report. Problem is -- one victim record could have "Dr. Smith" in
the first DD box, but another might have "Dr. Smith in the second, etc. I
need to know how many referrals were made to Dr. Smith, regardless of which
DD box he might appear.
I thought of relating a table that picked off the incident date, the
organization, and the # of referrals total made to each organization. But I
can't think of a way to send data to two different tables simultaneously,
always appending to the new table. VBA?
Or is there a simpler solution---as I said-- addled brain
Thanks in advance!

 

[John W. Vinson]

My addled brain needs some help! My database is for a volunteer victims in
crisis organization. The heart of it is an incident form which contains
data relevant to each individual incident.One section has 6 drop down boxes
which all get their row source from the same table, which contains all the
organizations to which a victim might be referred. Thus any victim could be
referred to up to 6 different places. Each DD box has its own control source
field. The form works great, but I have to generate a report which lists
all the organizations and the number of referrals made to each. Thus I have
to combine all the data from the 6 different fields to use as a recordsource
for the report. Problem is -- one victim record could have "Dr. Smith" in
the first DD box, but another might have "Dr. Smith in the second, etc. I
need to know how many referrals were made to Dr. Smith, regardless of which
DD box he might appear.

Your table design could be better. What if you ever need a SEVENTH
reference?

You have a many to many relationship. Rather than one FIELD for each
referral, consider instead having three tables: Incidents;
Organizations; and Referrals. The Referrals table would have fields
for the IncidentID (I hope your Incidents table has a Primary Key...!)
and the OrganizationID; you'ld have a Subform on the incident form,
bound to the Referrals table, with a (single) combo box on the
subform. If a given incident involves four referrals, you'ld just add
four rows in the subform. They'd all be in the same table so searching
for all referrals to Dr. Smith becomes very easy.

John W. Vinson [MVP]

 

[Richard Harison]

Your table design could be better. What if you ever need a SEVENTH
reference?

You have a many to many relationship. Rather than one FIELD for each
referral, consider instead having three tables: Incidents;
Organizations; and Referrals. The Referrals table would have fields
for the IncidentID (I hope your Incidents table has a Primary Key...!)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
I always use primary (usually autonumber) keys in every table I create
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

and the OrganizationID; you'ld have a Subform on the incident form,
bound to the Referrals table, with a (single) combo box on the
subform. If a given incident involves four referrals, you'ld just add
four rows in the subform. They'd all be in the same table so searching
for all referrals to Dr. Smith becomes very easy.

John W. Vinson [MVP]

Thanks, John
I did smell something like that. Many2many has never been one of my strong
points, so let me see if I have this straight.
1) the incident table has all the incident data plus the DD box for Referral
organization (which uses a rowsource from the organization table)
2) the Organization table has the names of the organizations
3) the referrals table is the linking (junction) table and its fields would
include the foreign keys from the other two, the incident date, organization
referred, # of referrals (the same individual could be referred to the
organization more than once for the same incident)

Am I getting close? Thanks again

 

[Richard Harison]

Your table design could be better. What if you ever need a SEVENTH
reference?

You have a many to many relationship. Rather than one FIELD for each
referral, consider instead having three tables: Incidents;
Organizations; and Referrals. The Referrals table would have fields
for the IncidentID (I hope your Incidents table has a Primary Key...!)
and the OrganizationID; you'ld have a Subform on the incident form,
bound to the Referrals table, with a (single) combo box on the
subform. If a given incident involves four referrals, you'ld just add
four rows in the subform. They'd all be in the same table so searching
for all referrals to Dr. Smith becomes very easy.

John W. Vinson [MVP]

Hi John,
Thought of another difficulty after I posted my reply. If the incident form
only has one DD box for referrals, I assume that the user would repeat using
it if more than one organization is referred. If so, how would the form
reflect how many & which different orgs. were
used. I thought of a list which would be driven by an SQL statement (WHERE
incidentID=....)
But the BIG hurdle is this. What if the user looks at an incident and says
"she was never referred there." How would the deletion process work? Am I
smelling a subform in the incident form instead of a simple DD box?

 

[John W. Vinson]

Hi John,
Thought of another difficulty after I posted my reply. If the incident form
only has one DD box for referrals, I assume that the user would repeat using
it if more than one organization is referred.

INCORRECT. Sorry, but you're missing a key point about how many to
many relationships work.

Let's not even *think* about Forms for a minute. You need to get the
tables right - they are the foundation of your application, and the
superstructure will collapse if the foundation is flawed.

The Incidents table should contain *NOTHING* about referrals.
The Organization table should contain *NOTHING* about incidents.

The reason is that if the Incidents table has a field for referrals,
you can only select *one value* into that field.

Instead, the Referrals table will have a record for each referral for
an incident. It will have an IncidentID field as a link to the
Incidents table, and an OrganizationID as a link to the Organizations
table. Thus each record stores the information that, for *this*
incident, *this* organization was referred

If so, how would the form
reflect how many & which different orgs. were
used. I thought of a list which would be driven by an SQL statement (WHERE
IncidentID=....)

What you would use (turning to Forms now) would be a *Subform*,
probably a Continuous subform, bound to the Referrals table. There
would be a combo box (based on the organizations table) on this
subform.

But the BIG hurdle is this. What if the user looks at an incident and says
"she was never referred there." How would the deletion process work? Am I
smelling a subform in the incident form instead of a simple DD box?

Exactly.

Take a look at how the Many (Orders) to Many (Products) relationship
is handled in the Northwind sample database Orders form for a very
similar example.

John W. Vinson [MVP]

 

[Richard Harison]

INCORRECT. Sorry, but you're missing a key point about how many to
many relationships work.

Let's not even *think* about Forms for a minute. You need to get the
tables right - they are the foundation of your application, and the
superstructure will collapse if the foundation is flawed.

The Incidents table should contain *NOTHING* about referrals.
The Organization table should contain *NOTHING* about incidents.

The reason is that if the Incidents table has a field for referrals,
you can only select *one value* into that field.

Instead, the Referrals table will have a record for each referral for
an incident. It will have an IncidentID field as a link to the
Incidents table, and an OrganizationID as a link to the Organizations
table. Thus each record stores the information that, for *this*
incident, *this* organization was referred


What you would use (turning to Forms now) would be a *Subform*,
probably a Continuous subform, bound to the Referrals table. There
would be a combo box (based on the organizations table) on this
subform.


Exactly.

Take a look at how the Many (Orders) to Many (Products) relationship
is handled in the Northwind sample database Orders form for a very
similar example.

John W. Vinson [MVP]

Thanks John! I should have posted this earlier, since I have good news to
report. I created a junction table, relating the large incident table to the
organizations table through foreign keys. I then created a subform within
the Incidents form, and added a DD box bound to the junction table, its
rowsource being the organizations table. I also added a # referrals text
box. I set the subform Link Master Fields property to the Incident form ID
and the Link Child Fields property to the foreign Incident key in the
referrals junction table. I set the subform to datasheet view, and all
seems right with the world.
Thank you so much...I have learned much more about many2many thanks to your
inspirational help