report and grouping for mdb with broad data structure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I’m trying to develop a comprehensive report for a database with a broad data
structure.

Table A––––>>Table B<<-–––––Table C (Lookup for Table B)
|
|––––>>Table D<<–––––Table E (Lookup for Table D)
|
|––––>>Table F<<–––––––Table G (Lookup for Table F)
|
|––––>>Table H––––––>> Table I

I’d like to be able to group by Local Organization (Table A) and then by
month.

Local Organization 123
July
Table B data
Table D data
Table F data
Table H and I data
Aug, etc

Local Organization 234
July
(As above), etc

I’d appreciate any suggestions.
 
RWilly said:
I’m trying to develop a comprehensive report for a database with a broad data
structure.

Table A––––>>Table B<<-–––––Table C (Lookup for Table B)
|
|––––>>Table D<<–––––Table E (Lookup for Table D)
|
|––––>>Table F<<–––––––Table G (Lookup for Table F)
|
|––––>>Table H––––––>> Table I

I’d like to be able to group by Local Organization (Table A) and then by
month.

Local Organization 123
July
Table B data
Table D data
Table F data
Table H and I data
Aug, etc

Local Organization 234
July
(As above), etc
Click to expand...


The key to creating reports is to first create a query that
retrieves the data you want to display. In this case, I
can't tell for sure if you want to use one query that
combines all the table's data, but I suspect that you should
use subreports for the B, C, etc. tables.

If the latter is appropriate, then the main report would be
based on a query that refers to table A. The query would
just be something like:
SELECT tableA.primarykeyfield, tableA.thedatefield,
tableA.Organizationfield, tableA.somefield.
tableA.anotherfield
FROM tableA
with the likely assition of a WHERE clause.

Use the report's Sorting and Grouping window (View menu) to
specify a group with header for the Organization field. Add
a second group with header for the month, but this time you
can't just group on the date field. Instead group on the
expression
=Format(thedatefield, "yyyydd"
then add a text box in the month group header using the
expression =Format(thedatefield, "mmmm")
Any other data from table A will probably(?) go in the
deatil section.

The query for subreport B will look something like:
SELECT tableB.foreignkeyfield,
tableB.thisfield, tableB.thatfield, . . .,
tableC.namefield
FROM tableB INNER JOIN tableC
ON tableB,primarykeyfield = tableC.linkingfield
The subreport itself should be very simple.

I think the subreports for tables D, F ans H should be very
similar to the arrangement fo subreport B.

Create and test each subreport separately before adding it
to the main report. Be sure to set each subreport control's
Link Master/Child properties to their corresponding linking
fields.
 
Marshall Barton said:
The key to creating reports is to first create a query that
retrieves the data you want to display. In this case, I
can't tell for sure if you want to use one query that
combines all the table's data, but I suspect that you should
use subreports for the B, C, etc. tables.

If the latter is appropriate, then the main report would be
based on a query that refers to table A. The query would
just be something like:
SELECT tableA.primarykeyfield, tableA.thedatefield,
tableA.Organizationfield, tableA.somefield.
tableA.anotherfield
FROM tableA
with the likely assition of a WHERE clause.

Use the report's Sorting and Grouping window (View menu) to
specify a group with header for the Organization field. Add
a second group with header for the month, but this time you
can't just group on the date field. Instead group on the
expression
=Format(thedatefield, "yyyydd"
then add a text box in the month group header using the
expression =Format(thedatefield, "mmmm")
Any other data from table A will probably(?) go in the
deatil section.

The query for subreport B will look something like:
SELECT tableB.foreignkeyfield,
tableB.thisfield, tableB.thatfield, . . .,
tableC.namefield
FROM tableB INNER JOIN tableC
ON tableB,primarykeyfield = tableC.linkingfield
The subreport itself should be very simple.

I think the subreports for tables D, F ans H should be very
similar to the arrangement fo subreport B.

Create and test each subreport separately before adding it
to the main report. Be sure to set each subreport control's
Link Master/Child properties to their corresponding linking
fields.
Click to expand...

Thanks for the reply. I'll give it another shot.
 
Marshall Barton said:
The key to creating reports is to first create a query that
retrieves the data you want to display. In this case, I
can't tell for sure if you want to use one query that
combines all the table's data, but I suspect that you should
use subreports for the B, C, etc. tables.

If the latter is appropriate, then the main report would be
based on a query that refers to table A. The query would
just be something like:
SELECT tableA.primarykeyfield, tableA.thedatefield,
tableA.Organizationfield, tableA.somefield.
tableA.anotherfield
FROM tableA
with the likely assition of a WHERE clause.

Use the report's Sorting and Grouping window (View menu) to
specify a group with header for the Organization field. Add
a second group with header for the month, but this time you
can't just group on the date field. Instead group on the
expression
=Format(thedatefield, "yyyydd"
then add a text box in the month group header using the
expression =Format(thedatefield, "mmmm")
Any other data from table A will probably(?) go in the
deatil section.

The query for subreport B will look something like:
SELECT tableB.foreignkeyfield,
tableB.thisfield, tableB.thatfield, . . .,
tableC.namefield
FROM tableB INNER JOIN tableC
ON tableB,primarykeyfield = tableC.linkingfield
The subreport itself should be very simple.

I think the subreports for tables D, F ans H should be very
similar to the arrangement fo subreport B.

Create and test each subreport separately before adding it
to the main report. Be sure to set each subreport control's
Link Master/Child properties to their corresponding linking
fields.
Click to expand...

Thanks for your help. I seem to be on the right path now. The main poblem
was that I wasn't setting the parent/child relationships properly for the
subreports.
 
Back
Top