Tricky Report Problem!

[Richard Harison]

I am creating a report whose purpose is to subtotal and total referrals to
different institutions used by an abuse counseling agency. To keep it
simple..lets say there are two categories: Hospitals and Shelters. Each
category has subcategories. (e.g. Joe's Hospital, Fred's Hospital) I need to
subtotal the number of referrals to Joe's, the number to Fred's and then total
the total referrals to hospitals. I have set up two grouping levels for the
categories & subcategories...but here's where the fun starts.
If a hospital is referred, the user checks a box, windows appear for the name of
the institution and the # of referrals to it. (Otherwise they remain inactive &
invisible.) I put an IIF statement in the underlying query which works nicely
in identifying the institution category if the box is checked. (Primary
grouping level). The problem is that more than one box may be checked! IOW a
referral might be made to BOTH a hospital AND a shelter. Naturally the sections
for hospitals and shelters would be in different parts of the report.
So how do I deposit a multiple category record into the appropriate locations
and get the counters working?
I thought of using a Visual Basic routine, but as we know report events are not
forthcoming on a record by record basis.
Thanks for your help!

 

[Allen Browne]

Iif a case may have a referral to multiple categories (or mulitple
subcategories?), you have a one-to-many relation between cases and
categories. This imples that you need a CaseCategory table, which will be
interfaced with a subform on your Case form.

Now you have a relational structure instead of those check boxes, the query
will show the Case multiple times where a case was referred to multiple
categories. The report can thefore show the case correctly under each
category.

 

[Richard Harison]

Thanks! I figured there was a 1-to-many situation in the woodwork. I am not as
yet conversant with the CaseCategory structure, but I will bone up on it and get
back to the NG if I have problems...Thanks again

--
All the Best
Richard Harison

Iif a case may have a referral to multiple categories (or mulitple
subcategories?), you have a one-to-many relation between cases and categories.
This imples that you need a CaseCategory table, which will be interfaced with
a subform on your Case form.

Now you have a relational structure instead of those check boxes, the query
will show the Case multiple times where a case was referred to multiple
categories. The report can thefore show the case correctly under each
category.

 

[Allen Browne]

If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd field if
you prefer an autonumber.)

One CaseID can turn up many times in this table, with different CategoryIDs.
Similarly one Category can turn up in this table many times, associated with
different cases.

The junction table is typically named as the concatenation of the other 2
table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html

 

[Richard Harison]

Thank you! I am conversant with junction tables. Used one for a hospital
database where one doctor may practice many different procedures and one
specific procedure may be performed by many doctors. (a many-to-many
relationship.) I consulted your link about the students & grades and see where
you are going. Perhaps you could walk me through a few details in my actual
application.

1) Every record in the main form, which generates the report, will have an area
to indicate what service(s) were provided. (to keep it simple--let's say 3
services-- [hospitals, shelters, counseling]) This is the primary grouping
level.
In most cases (hospitals, shelters), there also will be the name of a specific
institution which was used (name extracted from small tables containing the
names of all institutions associated with that category. (i.e. hospital A,
hospital B, etc.--the main form has drop down boxes with such a table as its
control source) This is the secondary grouping level.

2) In the associated report, there would be 2 counters. One for the primary
grouping level and a nested one # times each specific institution was used.
e.g.
Shelters
Shelter A
# times shelter A used
Shelter B
# times shelter B used
Total All Shelters:

3) In cases such as counseling (with no further specifics), only the totals
counter is necessary

Allen, could you possibly lead me through a basic demo of what necessary fields
would be in each table, assuming the table controlling the main form to be the
main table, and from which table the two controls on the report (service
provided-[primary grouping level] & specific institution-[secondary grouping
level])
Much thanks in advance

--
All the Best
Richard Harison

If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd field if
you prefer an autonumber.)

One CaseID can turn up many times in this table, with different CategoryIDs.
Similarly one Category can turn up in this table many times, associated with
different cases.

The junction table is typically named as the concatenation of the other 2
table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

news:[email protected]...

~~~snip

 

[Allen Browne]

There are several ways to model this kind of data.

One approach would be to use a ReferTo table that contains both the
institution types (shelter, hospital, counselling, ...) and also the
institutions
ReferToID AutoNumber primary key
ReferToName Text
ReferToParentID Number
In the Relationships window, you can then add a 2nd copy of the table, and
relate it to itself. Your Referral table (table of actual referrals, which
is the source for your subform) would than have a foreign key to this table.
The classes of institution are those where the ReferToParentID is null, so
in your interface you can use a combo with that rowsource for the
ReferToParentID. But when selecting a related record in the ReferToID, you
can choose either the class of institution, or a specific institution since
they are in the same table. To create the query for your report, it might be
very simple, or you might need to use a UNION query to generate the records
the way you want.

Another (perhaps more common) approach is to use 2 different tables for the
InstitutionType (hospital, shelter, ...) and the Institution (actual
institutions), instead of the single ReferTo table above. The Referral table
has 2 foreign keys: one for choosing the InstitutionType, and another for
choosing the actual Institution. The user can just enter the type if they
don't know the actual institution or it does not apply; or they can enter
the Institution where appropriate.

An issue with the 2nd design is that it is not strictly normalized, i.e. it
is possible to make a referral to a Shelter called Fred's Hospital, which is
clearly wrong. You can help avoid that by programmatically assigning values,
but it's not foolproof.

An issue with the 1st design is that it is recursive. That may be useful
(e.g. where a clinic is part of a hospital campus, which is part of a
hospital), but the SQL statements for this become really messy.

Either design should give you the report you want. Be sure to use outer
joins so you get the records even where there are null foreign keys. For the
first design, you will also have 2 copies of the same table in the query, or
possibly a UNION query.

Post back if that's still unclear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you! I am conversant with junction tables. Used one for a hospital
database where one doctor may practice many different procedures and one
specific procedure may be performed by many doctors. (a many-to-many
relationship.) I consulted your link about the students & grades and see
where you are going. Perhaps you could walk me through a few details in
my actual application.

1) Every record in the main form, which generates the report, will have an
area to indicate what service(s) were provided. (to keep it simple--let's
say 3 services-- [hospitals, shelters, counseling]) This is the primary
grouping level.
In most cases (hospitals, shelters), there also will be the name of a
specific institution which was used (name extracted from small tables
containing the names of all institutions associated with that category.
(i.e. hospital A, hospital B, etc.--the main form has drop down boxes with
such a table as its control source) This is the secondary grouping level.

2) In the associated report, there would be 2 counters. One for the
primary grouping level and a nested one # times each specific institution
was used. e.g.
Shelters
Shelter A
# times shelter A used
Shelter B
# times shelter B used
Total All
Shelters:

3) In cases such as counseling (with no further specifics), only the
totals counter is necessary

Allen, could you possibly lead me through a basic demo of what necessary
fields would be in each table, assuming the table controlling the main
form to be the main table, and from which table the two controls on the
report (service provided-[primary grouping level] & specific
institution-[secondary grouping level])
Much thanks in advance

--
All the Best
Richard Harison

If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd field
if you prefer an autonumber.)

One CaseID can turn up many times in this table, with different
CategoryIDs. Similarly one Category can turn up in this table many times,
associated with different cases.

The junction table is typically named as the concatenation of the other 2
table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html

 

[Richard Harison]

I think the second method is the one best suited for my application as I already
have tables containing the specific institutions.
HospitalID
HospitalName (etc.)

There are 3 such small tables. At present the user simply operates a drop down
box to select the specific institution.
Creating another table with the generic referral types (hospital, shelter...)
not a problem. The issue arises when both a particular hospital and a
particular shelter are associated with the same record. Additionally there are
4 generic types that do not require specifics (e.g. counseling)
Allen, I am better suited to math and programming than envisioning form/table
relationships, and, while grasping the concept, I am struggling with the actual
nuts & bolts. I assume the subform's control source is what you refer to below
as the Referral table, which I assume to be the junction table.

Would it be asking too much to send me a simple working model?
rharison at sympatico dot ca
Thank you!
--
All the Best
Richard Harison

There are several ways to model this kind of data.

One approach would be to use a ReferTo table that contains both the
institution types (shelter, hospital, counselling, ...) and also the
institutions
ReferToID AutoNumber primary key
ReferToName Text
ReferToParentID Number
In the Relationships window, you can then add a 2nd copy of the table, and
relate it to itself. Your Referral table (table of actual referrals, which is
the source for your subform) would than have a foreign key to this table. The
classes of institution are those where the ReferToParentID is null, so in your
interface you can use a combo with that rowsource for the ReferToParentID. But
when selecting a related record in the ReferToID, you can choose either the
class of institution, or a specific institution since they are in the same
table. To create the query for your report, it might be very simple, or you
might need to use a UNION query to generate the records the way you want.

Another (perhaps more common) approach is to use 2 different tables for the
InstitutionType (hospital, shelter, ...) and the Institution (actual
institutions), instead of the single ReferTo table above. The Referral table
has 2 foreign keys: one for choosing the InstitutionType, and another for
choosing the actual Institution. The user can just enter the type if they
don't know the actual institution or it does not apply; or they can enter the
Institution where appropriate.

An issue with the 2nd design is that it is not strictly normalized, i.e. it is
possible to make a referral to a Shelter called Fred's Hospital, which is
clearly wrong. You can help avoid that by programmatically assigning values,
but it's not foolproof.

An issue with the 1st design is that it is recursive. That may be useful (e.g.
where a clinic is part of a hospital campus, which is part of a hospital), but
the SQL statements for this become really messy.

Either design should give you the report you want. Be sure to use outer joins
so you get the records even where there are null foreign keys. For the first
design, you will also have 2 copies of the same table in the query, or
possibly a UNION query.

Post back if that's still unclear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you! I am conversant with junction tables. Used one for a hospital
database where one doctor may practice many different procedures and one
specific procedure may be performed by many doctors. (a many-to-many
relationship.) I consulted your link about the students & grades and see
where you are going. Perhaps you could walk me through a few details in my
actual application.

1) Every record in the main form, which generates the report, will have an
area to indicate what service(s) were provided. (to keep it simple--let's say
3 services-- [hospitals, shelters, counseling]) This is the primary
grouping level.
In most cases (hospitals, shelters), there also will be the name of a
specific institution which was used (name extracted from small tables
containing the names of all institutions associated with that category.
(i.e. hospital A, hospital B, etc.--the main form has drop down boxes with
such a table as its control source) This is the secondary grouping level.

2) In the associated report, there would be 2 counters. One for the primary
grouping level and a nested one # times each specific institution was used.
e.g.
Shelters
Shelter A
# times shelter A used
Shelter B
# times shelter B used
Total All
Shelters:

3) In cases such as counseling (with no further specifics), only the totals
counter is necessary

Allen, could you possibly lead me through a basic demo of what necessary
fields would be in each table, assuming the table controlling the main form
to be the main table, and from which table the two controls on the report
(service provided-[primary grouping level] & specific institution-[secondary
grouping level])
Much thanks in advance

--
All the Best
Richard Harison

If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd field if
you prefer an autonumber.)

One CaseID can turn up many times in this table, with different CategoryIDs.
Similarly one Category can turn up in this table many times, associated with
different cases.

The junction table is typically named as the concatenation of the other 2
table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html

 

[Allen Browne]

Hi Richard

Could you combine the 3 small tables into one that has an extra field that
indicates the InstitutionType (hospital, etc.)? This allows you to have a
foreign key in your referral table.

Presumably you have a one to many relation between say a Case and a
Referral, so one case can have referrals to different institutions.

We cannot build your database for you for free.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think the second method is the one best suited for my application as I
already have tables containing the specific institutions.
HospitalID
HospitalName (etc.)

There are 3 such small tables. At present the user simply operates a drop
down box to select the specific institution.
Creating another table with the generic referral types (hospital,
shelter...) not a problem. The issue arises when both a particular
hospital and a particular shelter are associated with the same record.
Additionally there are 4 generic types that do not require specifics (e.g.
counseling)
Allen, I am better suited to math and programming than envisioning
form/table relationships, and, while grasping the concept, I am struggling
with the actual nuts & bolts. I assume the subform's control source is
what you refer to below as the Referral table, which I assume to be the
junction table.

Would it be asking too much to send me a simple working model?
rharison at sympatico dot ca
Thank you!
--
All the Best
Richard Harison

There are several ways to model this kind of data.

One approach would be to use a ReferTo table that contains both the
institution types (shelter, hospital, counselling, ...) and also the
institutions
ReferToID AutoNumber primary key
ReferToName Text
ReferToParentID Number
In the Relationships window, you can then add a 2nd copy of the table,
and relate it to itself. Your Referral table (table of actual referrals,
which is the source for your subform) would than have a foreign key to
this table. The classes of institution are those where the
ReferToParentID is null, so in your interface you can use a combo with
that rowsource for the ReferToParentID. But when selecting a related
record in the ReferToID, you can choose either the class of institution,
or a specific institution since they are in the same table. To create the
query for your report, it might be very simple, or you might need to use
a UNION query to generate the records the way you want.

Another (perhaps more common) approach is to use 2 different tables for
the InstitutionType (hospital, shelter, ...) and the Institution (actual
institutions), instead of the single ReferTo table above. The Referral
table has 2 foreign keys: one for choosing the InstitutionType, and
another for choosing the actual Institution. The user can just enter the
type if they don't know the actual institution or it does not apply; or
they can enter the Institution where appropriate.

An issue with the 2nd design is that it is not strictly normalized, i.e.
it is possible to make a referral to a Shelter called Fred's Hospital,
which is clearly wrong. You can help avoid that by programmatically
assigning values, but it's not foolproof.

An issue with the 1st design is that it is recursive. That may be useful
(e.g. where a clinic is part of a hospital campus, which is part of a
hospital), but the SQL statements for this become really messy.

Either design should give you the report you want. Be sure to use outer
joins so you get the records even where there are null foreign keys. For
the first design, you will also have 2 copies of the same table in the
query, or possibly a UNION query.

Post back if that's still unclear.

Thank you! I am conversant with junction tables. Used one for a
hospital database where one doctor may practice many different
procedures and one specific procedure may be performed by many doctors.
(a many-to-many relationship.) I consulted your link about the students
& grades and see where you are going. Perhaps you could walk me through
a few details in my actual application.

1) Every record in the main form, which generates the report, will have
an area to indicate what service(s) were provided. (to keep it
simple--let's say 3 services-- [hospitals, shelters, counseling]) This
is the primary grouping level.
In most cases (hospitals, shelters), there also will be the name of a
specific institution which was used (name extracted from small tables
containing the names of all institutions associated with that category.
(i.e. hospital A, hospital B, etc.--the main form has drop down boxes
with such a table as its control source) This is the secondary grouping
level.

2) In the associated report, there would be 2 counters. One for the
primary grouping level and a nested one # times each specific
institution was used. e.g.
Shelters
Shelter A
# times shelter A used
Shelter B
# times shelter B used
Total All
Shelters:

3) In cases such as counseling (with no further specifics), only the
totals counter is necessary

Allen, could you possibly lead me through a basic demo of what
necessary fields would be in each table, assuming the table controlling
the main form to be the main table, and from which table the two
controls on the report (service provided-[primary grouping level] &
specific institution-[secondary grouping level])
Much thanks in advance

--
All the Best
Richard Harison
If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd
field if you prefer an autonumber.)

One CaseID can turn up many times in this table, with different
CategoryIDs. Similarly one Category can turn up in this table many
times, associated with different cases.

The junction table is typically named as the concatenation of the other
2 table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html

 

[Richard Harison]

Understandable! Yes I could combine the tables. I also see what you mean about
the 2nd option allowing for a user to select Fred's Hospital as a shelter. I
once created a database with outer joins to fill in a US state or Canadian
Province. The user would fill in either a
state or province (other field naturally left blank), and the output combined
into a single control on report. I will dink around with some ideas I mulled
over last night, and if I stumble across anything interesting, I will let you
know. Thanks so much for your help!!
--
All the Best
Richard Harison

Hi Richard

Could you combine the 3 small tables into one that has an extra field that
indicates the InstitutionType (hospital, etc.)? This allows you to have a
foreign key in your referral table.

Presumably you have a one to many relation between say a Case and a Referral,
so one case can have referrals to different institutions.

We cannot build your database for you for free.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think the second method is the one best suited for my application as I
already have tables containing the specific institutions.
HospitalID
HospitalName (etc.)

There are 3 such small tables. At present the user simply operates a drop
down box to select the specific institution.
Creating another table with the generic referral types (hospital, shelter...)
not a problem. The issue arises when both a particular hospital and a
particular shelter are associated with the same record. Additionally there
are 4 generic types that do not require specifics (e.g. counseling)
Allen, I am better suited to math and programming than envisioning form/table
relationships, and, while grasping the concept, I am struggling with the
actual nuts & bolts. I assume the subform's control source is what you refer
to below as the Referral table, which I assume to be the junction table.

Would it be asking too much to send me a simple working model?
rharison at sympatico dot ca
Thank you!
--
All the Best
Richard Harison

There are several ways to model this kind of data.

One approach would be to use a ReferTo table that contains both the
institution types (shelter, hospital, counselling, ...) and also the
institutions
ReferToID AutoNumber primary key
ReferToName Text
ReferToParentID Number
In the Relationships window, you can then add a 2nd copy of the table, and
relate it to itself. Your Referral table (table of actual referrals, which
is the source for your subform) would than have a foreign key to this table.
The classes of institution are those where the ReferToParentID is null, so
in your interface you can use a combo with that rowsource for the
ReferToParentID. But when selecting a related record in the ReferToID, you
can choose either the class of institution, or a specific institution since
they are in the same table. To create the query for your report, it might be
very simple, or you might need to use a UNION query to generate the records
the way you want.

Another (perhaps more common) approach is to use 2 different tables for the
InstitutionType (hospital, shelter, ...) and the Institution (actual
institutions), instead of the single ReferTo table above. The Referral table
has 2 foreign keys: one for choosing the InstitutionType, and another for
choosing the actual Institution. The user can just enter the type if they
don't know the actual institution or it does not apply; or they can enter
the Institution where appropriate.

An issue with the 2nd design is that it is not strictly normalized, i.e. it
is possible to make a referral to a Shelter called Fred's Hospital, which is
clearly wrong. You can help avoid that by programmatically assigning values,
but it's not foolproof.

An issue with the 1st design is that it is recursive. That may be useful
(e.g. where a clinic is part of a hospital campus, which is part of a
hospital), but the SQL statements for this become really messy.

Either design should give you the report you want. Be sure to use outer
joins so you get the records even where there are null foreign keys. For the
first design, you will also have 2 copies of the same table in the query, or
possibly a UNION query.

Post back if that's still unclear.

Thank you! I am conversant with junction tables. Used one for a hospital
database where one doctor may practice many different procedures and one
specific procedure may be performed by many doctors. (a many-to-many
relationship.) I consulted your link about the students & grades and see
where you are going. Perhaps you could walk me through a few details in my
actual application.

1) Every record in the main form, which generates the report, will have an
area to indicate what service(s) were provided. (to keep it simple--let's
say 3 services-- [hospitals, shelters, counseling]) This is the primary
grouping level.
In most cases (hospitals, shelters), there also will be the name of a
specific institution which was used (name extracted from small tables
containing the names of all institutions associated with that category.
(i.e. hospital A, hospital B, etc.--the main form has drop down boxes with
such a table as its control source) This is the secondary grouping level.

2) In the associated report, there would be 2 counters. One for the
primary grouping level and a nested one # times each specific institution
was used. e.g.
Shelters
Shelter A
# times shelter A used
Shelter B
# times shelter B used
Total All
Shelters:

3) In cases such as counseling (with no further specifics), only the totals
counter is necessary

Allen, could you possibly lead me through a basic demo of what necessary
fields would be in each table, assuming the table controlling the main form
to be the main table, and from which table the two controls on the report
(service provided-[primary grouping level] & specific
institution-[secondary grouping level])
Much thanks in advance

--
All the Best
Richard Harison
If you are searching on this, it is typically called a junction table.

It will contain fields:
CaseID foreign key to Case.CaseID
CategoryID foreign key to Category.CategoryID
The primary key could be the combination of the 2 fields (or a 3rd field
if you prefer an autonumber.)

One CaseID can turn up many times in this table, with different
CategoryIDs. Similarly one Category can turn up in this table many times,
associated with different cases.

The junction table is typically named as the concatenation of the other 2
table names (with the tbl prefix if you use that.)

If that whole concept is new, there is another example here:
http://allenbrowne.com/casu-06.html