Report with Sub-Report with NO-DATA on Sub-Report

G

Guest

I'm using Office Professional 2003, Access 2003 with service pack at its
latest level for the office product. I have a report in a database that has
3 separate sub reports active in this host report. My problem is there are
times that there will be "NO DATA" to report on any of the 3 different sub
reports and there are times that all 3 sub-report areas will have data to
report on. Think of it as a Primary keyed record with secondary data that is
also keyed to the primary, with possibly 3 different types of records in the
secondary file. Say primary record is a PR record, with secondary records as
SC-Type D, SC-TypeO, SC-TypeIP. On the main report, it has 3 sections: Page
Header, Detail, Page Footer. Page Header has information pertaining to the
Primary record "PR". The Detail section has 3 separate sub-reports with the
same format of: Record type, name, address, city, state, zip-code, with
SC-TypeD positioned to the top-left detail area, with SC-TypeO positioned to
the top-right detail area, and SC-TypeIP positioned just below the SC-TypeD
print area. Again, all three sub-reports have the same format, just
different record types. Each subreport controls what record type prints in
that area, Type D records to SC-TypeD area, etc.

What do I need to do to prevent wasting print line, page area when there may
or may not be a type D, or Type O record, only a Type-IP. Is there a way to
adjust only have two sub-report areas, and have the records float through
these print areas, say if there are no type-D or type-O records, the only one
to print would be the type-IP and it takes up the first sub-report area.
There can be multiple Type-D records, multiple Type-O records and multiple
Type-IP records, there can also be a time when there are no Typd-D, Type-O,
or Type-IP, just a primary record. The page footer contains data from the
primary record as well, its just the detail section that I can not figure out
how to properly do. Is this a code issue or can the tools from Access Report
builders/wizards fix this.

Thanks,

Robert
 
M

Marshall Barton

Robert said:
I'm using Office Professional 2003, Access 2003 with service pack at its
latest level for the office product. I have a report in a database that has
3 separate sub reports active in this host report. My problem is there are
times that there will be "NO DATA" to report on any of the 3 different sub
reports and there are times that all 3 sub-report areas will have data to
report on. Think of it as a Primary keyed record with secondary data that is
also keyed to the primary, with possibly 3 different types of records in the
secondary file. Say primary record is a PR record, with secondary records as
SC-Type D, SC-TypeO, SC-TypeIP. On the main report, it has 3 sections: Page
Header, Detail, Page Footer. Page Header has information pertaining to the
Primary record "PR". The Detail section has 3 separate sub-reports with the
same format of: Record type, name, address, city, state, zip-code, with
SC-TypeD positioned to the top-left detail area, with SC-TypeO positioned to
the top-right detail area, and SC-TypeIP positioned just below the SC-TypeD
print area. Again, all three sub-reports have the same format, just
different record types. Each subreport controls what record type prints in
that area, Type D records to SC-TypeD area, etc.

What do I need to do to prevent wasting print line, page area when there may
or may not be a type D, or Type O record, only a Type-IP. Is there a way to
adjust only have two sub-report areas, and have the records float through
these print areas, say if there are no type-D or type-O records, the only one
to print would be the type-IP and it takes up the first sub-report area.
There can be multiple Type-D records, multiple Type-O records and multiple
Type-IP records, there can also be a time when there are no Typd-D, Type-O,
or Type-IP, just a primary record. The page footer contains data from the
primary record as well, its just the detail section that I can not figure out
how to properly do. Is this a code issue or can the tools from Access Report
builders/wizards fix this.
Click to expand...


If I followed all that, all you need is to open the report
in design view, select a subreport and set its Can Shrink
property to Yes. Then set the detail section's CanShrink
property as well.
 
G

Guest

Thanks for respondinb Marshall.

I tried setting the CAN SHRINK to YES, CAN GROW was alreasy set to YES.
Now, when I run the report, it has another issue that I'm sure that can be
resolved, but I've tried all the tricks I know of to resolve this issue. As
you are aware of, I'm reading primary data from one data source, which is
keyed, indexed on CASE_NUM_YR, CASE_NUM fields, and a second data source
(keyed, indexed on CASE_NUM_YR, CASE_NUM, SEQ_NUM. I attempt to match
records to Primary & Secondary, but now the secondary file spills records
onto the report, even though they do not match the primary key. Both data
source types are the results set of queiries. I'm using the header and
footer section of the report for primary records, and the detail area is the
secondary data query results set. What am I missing to prevent non matching
records from printing in this detail area. The following query is for
selection of primary data in report FRR-FRRInfoSheet:
------------------
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM,
DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT, DPS_FR_CASE_RECORDS.HRG_DATE,
DPS_FR_CASE_RECORDS.PRTNO_NUM, DPS_FR_CASE_RECORDS.HRG_TIME_TXT,
DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT, DPS_FR_CASE_RECORDS.FR_FILE_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM,
DPS_FR_CASE_RECORDS.LIC_FIRST_NME, DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME, DPS_FR_CASE_RECORDS.LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT, DPS_FR_CASE_RECORDS.LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE, DPS_FR_CASE_RECORDS.LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE, DPS_FR_CASE_RECORDS.LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME,
DPS_FR_CASE_RECORDS.DOA_ADDR_TXT, DPS_FR_CASE_RECORDS.DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE, DPS_FR_CASE_RECORDS.DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE, DPS_FR_CASE_RECORDS.MEMO1_TXT,
DPS_FR_CASE_RECORDS.MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT,
DPS_FR_HEARING_LOC.ROOM_NME, DPS_FR_HEARING_LOC.CITY_NME,
DPS_FR_HEARING_LOC.STATE_CDE, DPS_FR_HEARING_LOC.LOC_NME,
DPS_FR_LICENSE_STATUS.DESC_TXT, DPS_FR_ATTORNEY.FIRST_NME,
DPS_FR_ATTORNEY.MIDDLE_NME, DPS_FR_ATTORNEY.LAST_NME,
DPS_FR_ATTORNEY.TELEPHONE_NUM, DPS_FR_ATTORNEY.FIRM_NME,
DPS_FR_ATTORNEY.ADDR1_TXT, DPS_FR_ATTORNEY.ADDR2_TXT,
DPS_FR_ATTORNEY.CITY_NME, DPS_FR_ATTORNEY.STATE_CDE, DPS_FR_ATTORNEY.ZIP_CDE,
DPS_FR_ATTORNEY.ZIP4_CDE, DPS_COUNTY_TABLE.CNTY_NME
FROM (((DPS_FR_CASE_RECORDS INNER JOIN DPS_FR_HEARING_LOC ON
DPS_FR_CASE_RECORDS.LOC_CDE = DPS_FR_HEARING_LOC.LOC_CDE) INNER JOIN
DPS_FR_LICENSE_STATUS ON DPS_FR_CASE_RECORDS.LIC_STAT_CDE =
DPS_FR_LICENSE_STATUS.LIC_STAT_CDE) INNER JOIN DPS_FR_ATTORNEY ON
DPS_FR_CASE_RECORDS.ATTY_NUM = DPS_FR_ATTORNEY.ATTY_NUM) INNER JOIN
DPS_COUNTY_TABLE ON DPS_FR_CASE_RECORDS.CNTY_NUM = DPS_COUNTY_TABLE.CNTY_NUM
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=10))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
------------------

The following is the query result for seconary data and sub-report detail
section:
-----------------
SELECT [FRQ-InfoSheet-Joined-Others].CASE_NUM_YR,
[FRQ-InfoSheet-Joined-Others].CASE_NUM,
[FRQ-InfoSheet-Joined-Others].SEQ_NUM,
[FRQ-InfoSheet-Joined-Others].VEHICLE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_NME,
[FRQ-InfoSheet-Joined-Others].FIRM_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_ADDR_TXT,
[FRQ-InfoSheet-Joined-Others].OTHER_CITY_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_STATE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP4_CDE,
[FRQ-InfoSheet-Joined-Others].DESC_TXT
FROM [FRQ-InfoSheet-Joined-Others];
----------------
I attempted to use a filter with the secondary query set of:
---------------
[FRQ-InfoSheet-Joined-Others].CASE_NUM_YR = [FRQ-FRRINFO].CASE_NUM_YR &
[FRQ-InfoSheet-Joined-Others].CASE_NUM = [FRQ-FRRINFO].CASE_NUM
---------------
but the resulting report allows the secondary data to flow on to non
matching primary results are of the subreport detail area.
---------------
If you do not mind, I'd appreciate some education on Access reports,
queries, reports and sub-reports. I'm sure there is an answer to this issue.


Thanks in advance.

Robert
 
M

Marshall Barton

Robert said:
I tried setting the CAN SHRINK to YES, CAN GROW was alreasy set to YES.
Now, when I run the report, it has another issue that I'm sure that can be
resolved, but I've tried all the tricks I know of to resolve this issue. As
you are aware of, I'm reading primary data from one data source, which is
keyed, indexed on CASE_NUM_YR, CASE_NUM fields, and a second data source
(keyed, indexed on CASE_NUM_YR, CASE_NUM, SEQ_NUM. I attempt to match
records to Primary & Secondary, but now the secondary file spills records
onto the report, even though they do not match the primary key. Both data
source types are the results set of queiries. I'm using the header and
footer section of the report for primary records, and the detail area is the
secondary data query results set. What am I missing to prevent non matching
records from printing in this detail area. The following query is for
selection of primary data in report FRR-FRRInfoSheet:
------------------
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM,
DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT, DPS_FR_CASE_RECORDS.HRG_DATE,
DPS_FR_CASE_RECORDS.PRTNO_NUM, DPS_FR_CASE_RECORDS.HRG_TIME_TXT,
DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT, DPS_FR_CASE_RECORDS.FR_FILE_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM,
DPS_FR_CASE_RECORDS.LIC_FIRST_NME, DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME, DPS_FR_CASE_RECORDS.LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT, DPS_FR_CASE_RECORDS.LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE, DPS_FR_CASE_RECORDS.LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE, DPS_FR_CASE_RECORDS.LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME,
DPS_FR_CASE_RECORDS.DOA_ADDR_TXT, DPS_FR_CASE_RECORDS.DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE, DPS_FR_CASE_RECORDS.DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE, DPS_FR_CASE_RECORDS.MEMO1_TXT,
DPS_FR_CASE_RECORDS.MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT,
DPS_FR_HEARING_LOC.ROOM_NME, DPS_FR_HEARING_LOC.CITY_NME,
DPS_FR_HEARING_LOC.STATE_CDE, DPS_FR_HEARING_LOC.LOC_NME,
DPS_FR_LICENSE_STATUS.DESC_TXT, DPS_FR_ATTORNEY.FIRST_NME,
DPS_FR_ATTORNEY.MIDDLE_NME, DPS_FR_ATTORNEY.LAST_NME,
DPS_FR_ATTORNEY.TELEPHONE_NUM, DPS_FR_ATTORNEY.FIRM_NME,
DPS_FR_ATTORNEY.ADDR1_TXT, DPS_FR_ATTORNEY.ADDR2_TXT,
DPS_FR_ATTORNEY.CITY_NME, DPS_FR_ATTORNEY.STATE_CDE, DPS_FR_ATTORNEY.ZIP_CDE,
DPS_FR_ATTORNEY.ZIP4_CDE, DPS_COUNTY_TABLE.CNTY_NME
FROM (((DPS_FR_CASE_RECORDS INNER JOIN DPS_FR_HEARING_LOC ON
DPS_FR_CASE_RECORDS.LOC_CDE = DPS_FR_HEARING_LOC.LOC_CDE) INNER JOIN
DPS_FR_LICENSE_STATUS ON DPS_FR_CASE_RECORDS.LIC_STAT_CDE =
DPS_FR_LICENSE_STATUS.LIC_STAT_CDE) INNER JOIN DPS_FR_ATTORNEY ON
DPS_FR_CASE_RECORDS.ATTY_NUM = DPS_FR_ATTORNEY.ATTY_NUM) INNER JOIN
DPS_COUNTY_TABLE ON DPS_FR_CASE_RECORDS.CNTY_NUM = DPS_COUNTY_TABLE.CNTY_NUM
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=10))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
------------------

The following is the query result for seconary data and sub-report detail
section:
-----------------
SELECT [FRQ-InfoSheet-Joined-Others].CASE_NUM_YR,
[FRQ-InfoSheet-Joined-Others].CASE_NUM,
[FRQ-InfoSheet-Joined-Others].SEQ_NUM,
[FRQ-InfoSheet-Joined-Others].VEHICLE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_NME,
[FRQ-InfoSheet-Joined-Others].FIRM_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_ADDR_TXT,
[FRQ-InfoSheet-Joined-Others].OTHER_CITY_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_STATE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP4_CDE,
[FRQ-InfoSheet-Joined-Others].DESC_TXT
FROM [FRQ-InfoSheet-Joined-Others];
Click to expand...


Subreport records are "linked" to the main report data by
using the subreport **control** Link Master/Child Fields
properties. On this case it looks like you want both
properties to be:
CASE_NUM_YR,CASE_NUM
 
G

Guest

Yes, Marshall that is true, originally, the subreport was linked for the
child link as the two fields of (field 1) CASE_NUM_YR, and (field 2) CASE_NUM
(result of 1992214) for CASE_NUMBER 214 of Case year 1992 thus 1992214
joined key for the child link and the master link as the same two fields
CASE_NUM_YR, CASE_NUM. There are multiple case numbers per year, and file
has years 1992 thru 2005 in the file. The SEQ_NUM field is an additional
field to help sort the secondary file on, as there can be no (0) secondary
records or unlimited number of secondary records linked back to the primary
keyed record. Only 1 primary, and ( 0 to xxxxx) secondary link
configuration. problem is that with the origninal bind on the sub-report set
to CASE_NUM_YR, CASE_NUM for both child and master, and the CAN SHRINK set
to on the detail area (subreport) the report works ok but leaves blank space
in between keye record print areas. Report is not unifrom from one report to
the next. But when you change the CAN SHRINK to YES, it rolls up the
secondary data from the next primary record on the first report instead of
the second report.

Thanks,

Robert
--
Robert Nusz @ DPS


Marshall Barton said:
Robert said:
I tried setting the CAN SHRINK to YES, CAN GROW was alreasy set to YES.
Now, when I run the report, it has another issue that I'm sure that can be
resolved, but I've tried all the tricks I know of to resolve this issue. As
you are aware of, I'm reading primary data from one data source, which is
keyed, indexed on CASE_NUM_YR, CASE_NUM fields, and a second data source
(keyed, indexed on CASE_NUM_YR, CASE_NUM, SEQ_NUM. I attempt to match
records to Primary & Secondary, but now the secondary file spills records
onto the report, even though they do not match the primary key. Both data
source types are the results set of queiries. I'm using the header and
footer section of the report for primary records, and the detail area is the
secondary data query results set. What am I missing to prevent non matching
records from printing in this detail area. The following query is for
selection of primary data in report FRR-FRRInfoSheet:
------------------
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM,
DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT, DPS_FR_CASE_RECORDS.HRG_DATE,
DPS_FR_CASE_RECORDS.PRTNO_NUM, DPS_FR_CASE_RECORDS.HRG_TIME_TXT,
DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT, DPS_FR_CASE_RECORDS.FR_FILE_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM,
DPS_FR_CASE_RECORDS.LIC_FIRST_NME, DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME, DPS_FR_CASE_RECORDS.LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT, DPS_FR_CASE_RECORDS.LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE, DPS_FR_CASE_RECORDS.LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE, DPS_FR_CASE_RECORDS.LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME,
DPS_FR_CASE_RECORDS.DOA_ADDR_TXT, DPS_FR_CASE_RECORDS.DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE, DPS_FR_CASE_RECORDS.DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE, DPS_FR_CASE_RECORDS.MEMO1_TXT,
DPS_FR_CASE_RECORDS.MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT,
DPS_FR_HEARING_LOC.ROOM_NME, DPS_FR_HEARING_LOC.CITY_NME,
DPS_FR_HEARING_LOC.STATE_CDE, DPS_FR_HEARING_LOC.LOC_NME,
DPS_FR_LICENSE_STATUS.DESC_TXT, DPS_FR_ATTORNEY.FIRST_NME,
DPS_FR_ATTORNEY.MIDDLE_NME, DPS_FR_ATTORNEY.LAST_NME,
DPS_FR_ATTORNEY.TELEPHONE_NUM, DPS_FR_ATTORNEY.FIRM_NME,
DPS_FR_ATTORNEY.ADDR1_TXT, DPS_FR_ATTORNEY.ADDR2_TXT,
DPS_FR_ATTORNEY.CITY_NME, DPS_FR_ATTORNEY.STATE_CDE, DPS_FR_ATTORNEY.ZIP_CDE,
DPS_FR_ATTORNEY.ZIP4_CDE, DPS_COUNTY_TABLE.CNTY_NME
FROM (((DPS_FR_CASE_RECORDS INNER JOIN DPS_FR_HEARING_LOC ON
DPS_FR_CASE_RECORDS.LOC_CDE = DPS_FR_HEARING_LOC.LOC_CDE) INNER JOIN
DPS_FR_LICENSE_STATUS ON DPS_FR_CASE_RECORDS.LIC_STAT_CDE =
DPS_FR_LICENSE_STATUS.LIC_STAT_CDE) INNER JOIN DPS_FR_ATTORNEY ON
DPS_FR_CASE_RECORDS.ATTY_NUM = DPS_FR_ATTORNEY.ATTY_NUM) INNER JOIN
DPS_COUNTY_TABLE ON DPS_FR_CASE_RECORDS.CNTY_NUM = DPS_COUNTY_TABLE.CNTY_NUM
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=10))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
------------------

The following is the query result for seconary data and sub-report detail
section:
-----------------
SELECT [FRQ-InfoSheet-Joined-Others].CASE_NUM_YR,
[FRQ-InfoSheet-Joined-Others].CASE_NUM,
[FRQ-InfoSheet-Joined-Others].SEQ_NUM,
[FRQ-InfoSheet-Joined-Others].VEHICLE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_NME,
[FRQ-InfoSheet-Joined-Others].FIRM_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_ADDR_TXT,
[FRQ-InfoSheet-Joined-Others].OTHER_CITY_NME,
[FRQ-InfoSheet-Joined-Others].OTHER_STATE_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP_CDE,
[FRQ-InfoSheet-Joined-Others].OTHER_ZIP4_CDE,
[FRQ-InfoSheet-Joined-Others].DESC_TXT
FROM [FRQ-InfoSheet-Joined-Others];
Click to expand...


Subreport records are "linked" to the main report data by
using the subreport **control** Link Master/Child Fields
properties. On this case it looks like you want both
properties to be:
CASE_NUM_YR,CASE_NUM
Click to expand...
 
M

Marshall Barton

Robert said:
Yes, Marshall that is true, originally, the subreport was linked for the
child link as the two fields of (field 1) CASE_NUM_YR, and (field 2) CASE_NUM
(result of 1992214) for CASE_NUMBER 214 of Case year 1992 thus 1992214
joined key for the child link and the master link as the same two fields
CASE_NUM_YR, CASE_NUM. There are multiple case numbers per year, and file
has years 1992 thru 2005 in the file. The SEQ_NUM field is an additional
field to help sort the secondary file on, as there can be no (0) secondary
records or unlimited number of secondary records linked back to the primary
keyed record. Only 1 primary, and ( 0 to xxxxx) secondary link
configuration. problem is that with the origninal bind on the sub-report set
to CASE_NUM_YR, CASE_NUM for both child and master, and the CAN SHRINK set
to on the detail area (subreport) the report works ok but leaves blank space
in between keye record print areas. Report is not unifrom from one report to
the next. But when you change the CAN SHRINK to YES, it rolls up the
secondary data from the next primary record on the first report instead of
the second report.
Click to expand...


What I think you are describing doesn't make sense to me.
Setting the Link Master/Child fields has nothing to do with
CanShrink and neither should cause data to disappear from
the report.

Could you try typing a simple example in a post so I can try
to figure out what your problem might be?
 
G

Guest

I have two tables, primary and secondary. They are linked together on a
one-to-many relationship, one primary can have zero secondary records or 999
records. Key is CASE_NUM_YR & CASE_NUM on primary record, secondary record
is key is CASE_NUM_YR, CASE_NUM, SEQ_NUM. Master-Child link on subreport is
CASE_NUM_YR, CASE_NUM.
Primary table has 3 cases:
1992 149 licensee 1, address, city, state info
1992 150 licensee 1, address, city, state info
1992 151 licensee 1, address, city, state info

Secondary record has 14 records:
1992 149 1 driver 2 of car b name, address, city, state, zip info
1992 149 2 owner of car b name, address, city, state, zip info
1992 149 3 driver 3 of car c name, address, city, state, zip info
1992 149 4 owner of car c name, address, city, state, zip info
1992 149 5 driver 4 of car-d name, address, city, state, zip info
1992 149 6 owner of car d name, address, city, state, zip info
1992 149 7 interested party of car a name, address, city, state, zip info
1992 149 8 interested party of car d name, address, city, state, zip info
1992 151 1 driver 2 of car-b name, address, city, state, zip info
1992 151 2 driver 3 of car-c name, address, city, state, zip info

Printed report has three sections: Page Header, Detail, Page footer.
Header area contains crash report, names primary person. Detail section of
report has subreport attachment one beside the other, like printing two
labels of name, address, city, state, zip, information side by side. footer
section has more crash data.
----------------------------- header section
-------------------------------------------------------------
state of caos creash report

case number 1992-149
licensee 1 name
address,
city, state, zip
----------------------- detail section using subreport a
---------------------------------------------
record 1992-149-1 record 1992-149-2

driver 2, car b name owner car b name
driver 2, car b addresss owner car b address
driver 2, car b city, state, zip owner car b,
city, state, zip
----------------------------------------------------------------------------------------------------
record 1992-149-3 record 1992-149-4
driver 3, car c name owner car c name
driver 3, address owner car c
address
driver 3, city, state, zip owner car c,
city, state, zip
-----------------------------------------------------------------------------------------------------
record 1992-149-5 record 1992-149-6
driver 4, car d name owner car d name
driver 4, address owner car d
address
driver 4, city, state, zip owner car d,
city, state, zip
---------------------------------------------------------------------------------------------------
record 1992-149-7 record 1992-149-8
interested party 1 name interested part 2
name
I/P #1 address I/P #2 address
I/P # 1 city, state, zip I/P # 2 city,
state, zip
-------------------------------- end of detail section starts footer below
-------------
crash was at
highway I-40 mile 10
--------------------------------------- end of report
-------------------------------------

Example of correct report format above, with primary records above, report
fails when it adds subreport records for case # 1992-151 into the same
subarea for only record 149.
by adding the following just below the IP information for 1992-149
--------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
record 1992-151-2 record 1992-151-3
driver 2 car-b name driver 3, car c
name
driver 2 b address driver 3 c
address
driver 2 b city, state, zip driver 3 c
city, state, zip
------------------------------------------------------------------------------------------------------
thus invalidly merging secondary records for 1992-151 case with secondary
records for case 1992-149.

Does this help.

Thanks
 
M

Marshall Barton

Robert said:
I have two tables, primary and secondary. They are linked together on a
one-to-many relationship, one primary can have zero secondary records or 999
records. Key is CASE_NUM_YR & CASE_NUM on primary record, secondary record
is key is CASE_NUM_YR, CASE_NUM, SEQ_NUM. Master-Child link on subreport is
CASE_NUM_YR, CASE_NUM.
Primary table has 3 cases:
1992 149 licensee 1, address, city, state info
1992 150 licensee 1, address, city, state info
1992 151 licensee 1, address, city, state info

Secondary record has 14 records:
1992 149 1 driver 2 of car b name, address, city, state, zip info
1992 149 2 owner of car b name, address, city, state, zip info
1992 149 3 driver 3 of car c name, address, city, state, zip info
1992 149 4 owner of car c name, address, city, state, zip info
1992 149 5 driver 4 of car-d name, address, city, state, zip info
1992 149 6 owner of car d name, address, city, state, zip info
1992 149 7 interested party of car a name, address, city, state, zip info
1992 149 8 interested party of car d name, address, city, state, zip info
1992 151 1 driver 2 of car-b name, address, city, state, zip info
1992 151 2 driver 3 of car-c name, address, city, state, zip info

Printed report has three sections: Page Header, Detail, Page footer.
Header area contains crash report, names primary person. Detail section of
report has subreport attachment one beside the other, like printing two
labels of name, address, city, state, zip, information side by side. footer
section has more crash data.
----------------------------- header section
-------------------------------------------------------------
state of caos creash report

case number 1992-149
licensee 1 name
address,
city, state, zip
----------------------- detail section using subreport a
---------------------------------------------
record 1992-149-1 record 1992-149-2

driver 2, car b name owner car b name
driver 2, car b addresss owner car b address
driver 2, car b city, state, zip owner car b,
city, state, zip
----------------------------------------------------------------------------------------------------
record 1992-149-3 record 1992-149-4
driver 3, car c name owner car c name
driver 3, address owner car c
address
driver 3, city, state, zip owner car c,
city, state, zip
-----------------------------------------------------------------------------------------------------
record 1992-149-5 record 1992-149-6
driver 4, car d name owner car d name
driver 4, address owner car d
address
driver 4, city, state, zip owner car d,
city, state, zip
---------------------------------------------------------------------------------------------------
record 1992-149-7 record 1992-149-8
interested party 1 name interested part 2
name
I/P #1 address I/P #2 address
I/P # 1 city, state, zip I/P # 2 city,
state, zip
-------------------------------- end of detail section starts footer below
-------------
crash was at
highway I-40 mile 10
--------------------------------------- end of report
-------------------------------------

Example of correct report format above, with primary records above, report
fails when it adds subreport records for case # 1992-151 into the same
subarea for only record 149.
by adding the following just below the IP information for 1992-149
--------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
record 1992-151-2 record 1992-151-3
driver 2 car-b name driver 3, car c
name
driver 2 b address driver 3 c
address
driver 2 b city, state, zip driver 3 c
city, state, zip
Click to expand...


Are you really using the PAGE header/footer for the case
info? I don't see how that can be expected to work when
there are multiple cases in the report.

That may very well be the issue here. Try creating a group
(View menu - Sorting and Grouping) using the expression:
=CASE_NUM_YR & CASE_NUM

Make sure you select Yes for Headerin the group's properties
in the lower part of the window. Back in the report's
design window select the group header section and set its
ForceNewPage property to Before Section and set it's height
to as small as needed.

You may (or may not) want to move the controls from the page
header to the group header (which has a RepeatSection
property if you want to see it on every page) and/or use the
group footer instead of the page footer.
 
G

Guest

Marshall,

I'll try another way.

I've got two table/query sources. The Primary source record is keyed on
case_num-yr field and case_num and the secondary source is keyed off of a
three part key of case_num_yr, case_num, seq_num. I have the following
record sets, a (-) separates fields of case_num_yr, case_num, driver_name on
primary record
primary source:
record # 1 = 1992-100-Robert Unlucky Driver-1313 E. 13th St. - OKC-OK-71313
record # 2 = 1992-101-Driver Stan
record # 3 = 1992-102-Driver Steve

secondary source, a (-) separates fields of case_num_yr, case_num, seq_num,
driver_name, address, city, state, zip, rectype
record # 1 = 1992-100-001-Henry Hany -123 Henry Street - OkC - OK - 73112 -
Driver
record # 2 = 1992-100-002-Tom Thumb -30 Tom Street - OKC - OK - 73112 - Owner
record # 3 = 1992-100-003-Bobs Car Ins -50 Bob Avenue - OKC - OK - 73112 -
IntPrty
record # 4 = 1992-100-004-Lee's Wrecker - 40 Lee Street - OKC - OK - 73112 -
IntPrty
record # 5 = 1992-102-001-Mark Markup-1313 Bird Lane-OKC-Ok-73112-Driver
record # 6 = 1992-102-002-Bud Weisor-18 TooMany-OKC-Ok-73099-Driver

Naturally, I want to print the primary record of 1992-100 for driver Robert
and all of his associated records together from secondary file, 1992-100-001,
1992-100-002, 1992-100-003. Since next record in secondar file is
1992-102-001 it doesn't match 1992-100 so don't print it on primary report
page.

report needs to be like this for Header part of report
-------------------------- report header -----------------
case Year = 1992 Case Number: 100
Driver: Robert Unlucky Driver
1313 E. 13th St.
OKC, Ok. 71313
---------------------- detail section ---------------
Driver: Henry Haney Owner: Tom Thumb
123 Henry Street 30 Tom Street
OKC, Ok. 73112 OKC, Ok, 73112

Int/Prty: Bobs Car Ins. Int/Prty:
Lee's Wrecker
50 Bob Avenue
40 Lee Street
OKC, Ok. 73112
OKC, Ok. 73112
-------------------------- footer section ------------------------------
Dl Number: 13-13
Car tag: 1313-OKS
Date of Accident: October 31, 2005
---------------------------- end of report ---------------------------
The Page Header section contains part 1 of the primary record, with the
detail section containing a sub-report with 2 columns of print. Each column
taking data from associated secondary records, and printing two columns (two
records) per section,
keep in mind there might not be any associated secondary records, (say
result of 1 car accident with no interested parties or other victims). and
the footer section, describing the car, accident date of the crash. I want
to make sure that the detail section only contains data for each associated
primary record.

the Sub-report I've got has been linked master/child to case_num_yr,
case_num. secondary file is sorted on case_num_yr, case_num, seq_num.

Does this help
 
M

Marshall Barton

Robert said:
I've got two table/query sources. The Primary source record is keyed on
case_num-yr field and case_num and the secondary source is keyed off of a
three part key of case_num_yr, case_num, seq_num. I have the following
record sets, a (-) separates fields of case_num_yr, case_num, driver_name on
primary record
5,10,15,20,or 25source:
record # 1 = 1992-100-Robert Unlucky Driver-1313 E. 13th St. - OKC-OK-71313
record # 2 = 1992-101-Driver Stan
record # 3 = 1992-102-Driver Steve

secondary source, a (-) separates fields of case_num_yr, case_num, seq_num,
driver_name, address, city, state, zip, rectype
record # 1 = 1992-100-001-Henry Hany -123 Henry Street - OkC - OK - 73112 -
Driver
record # 2 = 1992-100-002-Tom Thumb -30 Tom Street - OKC - OK - 73112 - Owner
record # 3 = 1992-100-003-Bobs Car Ins -50 Bob Avenue - OKC - OK - 73112 -
IntPrty
record # 4 = 1992-100-004-Lee's Wrecker - 40 Lee Street - OKC - OK - 73112 -
IntPrty
record # 5 = 1992-102-001-Mark Markup-1313 Bird Lane-OKC-Ok-73112-Driver
record # 6 = 1992-102-002-Bud Weisor-18 TooMany-OKC-Ok-73099-Driver

Naturally, I want to print the primary record of 1992-100 for driver Robert
and all of his associated records together from secondary file, 1992-100-001,
1992-100-002, 1992-100-003. Since next record in secondar file is
1992-102-001 it doesn't match 1992-100 so don't print it on primary report
page.

report needs to be like this for Header part of report
-------------------------- report header -----------------
case Year = 1992 Case Number: 100
Driver: Robert Unlucky Driver
1313 E. 13th St.
OKC, Ok. 71313
---------------------- detail section ---------------
Driver: Henry Haney Owner: Tom Thumb
123 Henry Street 30 Tom Street
OKC, Ok. 73112 OKC, Ok, 73112

Int/Prty: Bobs Car Ins. Int/Prty:
Lee's Wrecker
50 Bob Avenue
40 Lee Street
OKC, Ok. 73112
OKC, Ok. 73112
-------------------------- footer section ------------------------------
Dl Number: 13-13
Car tag: 1313-OKS
Date of Accident: October 31, 2005
---------------------------- end of report ---------------------------
The Page Header section contains part 1 of the primary record, with the
detail section containing a sub-report with 2 columns of print. Each column
taking data from associated secondary records, and printing two columns (two
records) per section,
keep in mind there might not be any associated secondary records, (say
result of 1 car accident with no interested parties or other victims). and
the footer section, describing the car, accident date of the crash. I want
to make sure that the detail section only contains data for each associated
primary record.

the Sub-report I've got has been linked master/child to case_num_yr,
case_num. secondary file is sorted on case_num_yr, case_num, seq_num.
Click to expand...


The way I read all that, it is so easy that I am really
having trouble figuring out what the problem is. The main
report's record source is the primary table and the
subreport's record source is the secondary table. Your
Link Master/Child properties are the way I think they should
be.

All the bound text boxes in the main report are in the
detail section followed by the subreport. No headers or
footer sections are needed and the report will list any
number of primary records with their associated secondary
records.

Maybe the problem is the concept of "header" data versus a
header section in the report? When using a subreport like
this, the "header" data is just text boxes placed above the
subreport and any "footer" data text boixes are also in the
detail section but below the subreport.

Or is the problem how to present the subreport data in two
columns?
 
G

Guest

Right now it is only how to make the sub-report work with two colums side by
side of the secondary data that ONLY applies to matching primary record data
held in the header/footer of the report. If I have 20 secondary records in
sequential order that matches the primary record I DO NOT WANT a report like
this

------------------------
primary header
---- detail --------
src # 1 src # 11
src # 2 src # 12
src # 3 src # 13
src # 4 src # 14
src # 5 src # 15
src # 6 src # 16
src # 7 src # 17
src # 8 src # 18
src # 9 src # 19
src # 10 src # 20
------------------------------
primary footer report
------------------------------

I need this report to print as

------------------------
primary header
---- detail --------
src # 1 src # 2
src # 3 src # 4
src # 5 src # 6
src # 7 src # 8
src # 9 src # 10
src # 11 src # 12
src # 13 src # 14
src # 15 src # 16
src # 17 src # 18
src # 19 src # 20
------------------------------
primary footer report
------------------------------

I've tried sub-report which details report layout of 1 secondary record and
specified 1 row but 2 columns, like a label format, but I still couldnt get
it to separate secondary data to match primary record. It continued to add
secondary records that matched other primary records, in other words the
secondary records case year and number did not match the header, primary case
year and number controls. Is this due to the header/footer issue?

Thanks,

Robert
 
M

Marshall Barton

Getting pnly subreport records that match the parent record
is a matter of setting Link Master/Child Fields properties
correctly. There is nothing else that should cause any
trouble with this. You said that you had them set to the
case year and number fields for both the master and child
properties, which should take care of it. Aside from double
checking to make certain that is the case, all I can think
of is that it might make a difference if the Link Master
Fields property refered to the text boxes that are bound to
those fields instead of referring to the fields themselves.

To get the columns the way you want them, open the subreport
in design view, then go to the Page Setup window (File menu)
and select the Across then Down option on the columns tab.

Since we are no longer using any header or footer sections
in either the main report or the subreport, let's avoid
using the word header to describe a set of text boxes.

Also, you are still describing the information in the
subreport as detail, but all of the text box controls and
the subreport control are in the main report's detail
section so that word's meaning is somewhat cloudy. Let's
only use the word detail to mean a section in a report.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable Sub-report 2
Sub-Report Problem 5
Print Report If Sub-Report Has Data 2
Columns in sub-report on detail section 1
Can shrink with sub-report 1
Two Columns, no sub-report 4
Sub Report placement 1
Sub-report missing last record 2

Top