Can Shrink Property

[pbt1234]

I currently have a query that creates a table from many different tables in preparation for the report. The table created is filled with information about parts measurements. Each part can have up to 6 measurement labels (could be different for each part - i.e. part 1/measurement label1 could be OD and part2/measurementlabel1 could be ID) and then a min and max value for that measurementlabel. I have created a report from this table which shows the data as follows . . .

Part1 Desc MeasurementLabel1 Min Max
MeasurementLabel2 Min Max
MeasurementLabel3 Min Max
MeasurementLabel4 Min Max
MeasurementLabel5 Min Max
MeasurementLabel6 Min Max
-------------------------------
Part2 Desc MeasurementLabel1 Min Max
MeasurementLabel2 Min Max
MeasurementLabel3 Min Max
MeasurementLabel4 Min Max
MeasurementLabel5 Min Max
MeasurementLabel6 Min Max

The report works fine. My trouble is that places are held for each part for 6 lines as shown above. I started simple by adding one measurementlabel and min/max at a time. I tested after adding the first two measurementlabel and found that I could turn on the "Could Shrink" variable in the properties for the measurementlabel and min/max fields and the report would adjust for missing values. By this I mean that I could have only 1 measurementlabel for part1 and the report would have only one line held for that part. If the part had more than one measurement label, additional lines were held, thus eliminating the wasted space and leaving the user fewer pages to view/print. After testing this on the first two and getting it to work, I proceeded to add the remaining four and then tested again and this functionality ceased to work. I have checked each one of these items individually to ensure that the "Can Shrink" property is set to "Yes" and they are.

The report is functional as is, but I would like for the report size to adjust based on the missing data. Anyone got any suggestions on how to make this work like I desire?

Thanks in advance!
pbt1234

 

[dhookom]

Can you provide your actual table structure and data types? We don't know if each individual measure is a record (properly normalized) or do you have multiple repeating similar fields.

Duane
Hook'D on Access
MS Access MVP

 

[pbt1234]

dhookom -

The table structure and data types are below . . .

PartDesc - Text
DrawingNo - Text
OrderingNo - Number
MeasurementLabel1 - Text
Measurement1Min - Number
Measurement1Max - Number
MeasurementLabel2 - Text
Measurement2Min - Number
Measurement2Max - Number
MeasurementLabel3 - Text
Measurement3Min - Number
Measurement3Max - Number
MeasurementLabel4 - Text
Measurement4Min - Number
Measurement4Max - Number
MeasurementLabel5 - Text
Measurement5Min - Number
Measurement5Max - Number
MeasurementLabel6 - Text
Measurement6Min - Number
Measurement6Max - Number

Hope this provides the information you are looking for. Thanks for your input!

pbt1234

 

[dhookom]

The table structure looks quite un-normalized. This might be the result of your data manipulation prior to reporting. I would normalize this with a union query and only include fields where there are values in the Number fields. You could then group your report by:
PartDesc, DrawingNo, OrderingNo
and each detail section would only be the appropriate
MeasurementLabel Min Max

SELECT PartDesc, DrawingNo, OrderingNo,
MeasurementLabel1 As MeasurementLabel, Measurement1Min As TheMin, Measurement1Max As TheMax
FROM tblWhatever
WHERE Len(MeasurementLabel1 & "") >0
UNION ALL
SELECT PartDesc, DrawingNo, OrderingNo,
MeasurementLabel2, Measurement2Min, Measurement2Max
FROM tblWhatever
WHERE Len(MeasurementLabel2 & "") >0
UNION ALL
SELECT PartDesc, DrawingNo, OrderingNo,
MeasurementLabel3, Measurement3Min, Measurement3Max
FROM tblWhatever
WHERE Len(MeasurementLabel3 & "") >0
UNION ALL
--- etc ---

Duane
Hook'D on Access
MS Access MVP