Philosophy forDisplaying 1-to-Many Data Sets

[Don]

(This is an extension of my post "Data Source for a Sub-Report" I posted on
Sunday, 21 February.)

After pondering my problem a bit more, I generalized to the case of
displaying 1-to-many data sets. Is there a preferred approach to displaying
data generated from a single query which has a 1-to-many form? Or is this
type of query bad to begin with?

As a somewhat contrived example to demonstrate this question, consider an
apartment building management system. The building owner might want a
report of all renters (and dates of occupancy) for each apartment. (As the
building has been around for some time and there is a high turnover rate,
each apartment has had several tenants. And there are a lot of Smiths in
this town as it is Smithville.) The building owner now wants a report (in
the same format as the one described earlier) which lists all the residents
named "Smith" who have lived in each apartment on the third floor of the
building. Obviously, this requires a query looking at not only the
apartment table, but the resident table. Not a big stretch. However, how
does one set up the report (or form)? The query provides all the data, but
the report/sub-report format assumes the sub-report is pulling in data from
a separate table or query.

Is there a way to tell the sub-report to use the query used in the report?
Is there a way to dynamically pass a (SQL) query to the sub-report? Is
there a better way to approach this problem?

Thanks!!

Don

 

[Brian]

(This is an extension of my post "Data Source for a Sub-Report" I posted on
Sunday, 21 February.)

After pondering my problem a bit more, I generalized to the case of
displaying 1-to-many data sets. Is there a preferred approach to displaying
data generated from a single query which has a 1-to-many form? Or is this
type of query bad to begin with?

As a somewhat contrived example to demonstrate this question, consider an
apartment building management system. The building owner might want a
report of all renters (and dates of occupancy) for each apartment. (As the
building has been around for some time and there is a high turnover rate,
each apartment has had several tenants. And there are a lot of Smiths in
this town as it is Smithville.) The building owner now wants a report (in
the same format as the one described earlier) which lists all the residents
named "Smith" who have lived in each apartment on the third floor of the
building. Obviously, this requires a query looking at not only the
apartment table, but the resident table. Not a big stretch. However, how
does one set up the report (or form)? The query provides all the data, but
the report/sub-report format assumes the sub-report is pulling in data from
a separate table or query.

Is there a way to tell the sub-report to use the query used in the report?
Is there a way to dynamically pass a (SQL) query to the sub-report? Is
there a better way to approach this problem?

Thanks!!

Don

Don't use a sub-report, simply create a report based on the query and add a
grouping level as appropriate.

 

[Albert D. Kallal]

I would think that you obviously have a table with the rooms list. And, the
"many" side would the renters.

So, you just build a report that lists:

Room/Suite# Floor NumberOfBedRooms


You put whatever room fields you want. Now, to list the all of the renters
for each room, you simply drop in a sub-report into the details section. So,
just build a nice report that lists the persons name

Name PhoneNumber Address


You then simply drop in the above report into the first report you made. You
set the link child/link master fields to the same fields you used to make
the relationship in first place.

I am not sure where I am missing something here, but for editing of one to
many data, forms are generally the "one" side, and the "many" side is a
sub-form. This logical concept thus follows and applies to reports when you
want to display that data again. So, the main report, and the sub-report are
the solution for reports..and is the same concept as a sub-form..

It would be really quite amazing if you have build a system to manage a
apartment, and it has all kinds of one to many relationships, but you have
not yet used forms and sub-forms accomplish the editing of such data. And,
if you have used sub-forms, then it should follow for reporting you use
sub-reports.

I am not saying that all one to many relaltionships are edting via
sub-forms..but VERY often, this is the case. You can take a look at the
follwing screen shots, and in these cases...often two sub-forms were used,
as I wanted "many" of the one on the left side..and to show the "many" side
on the right...

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

However, for reporting on the above data, you can use sub-forms, or in fact
as the other poster in this thread suggested, you build a query, and join in
the child data (table), and then use the grouping options. Both approach can
work quite well.

 

[Don]

Brian,

Excellent suggestion! I will be getting back to this project later in the
week and give it a try then.

Thanks!

Don

 

[Don]

Albert,

My problem is that part of the query effects which "renters" (sub-form data)
that are displayed. I think Brian's suggestion regarding using grouping
will address my problem.

Thanks!

Don