Crosstab - hide null line

  • Thread starter Thread starter AlCamp
  • Start date Start date
A

AlCamp

Hello folks,
I've developed a "dynamic" crosstab report (using the method from
Sample97.mdb) where Products vs
ProductionDefects. I have a table with 30 distinct defects with DefectIDs
numbered from 1 to 30.
I want to see all my DefectIDs across the top of the report (crosstab
Columns)

However, the Products reported on within a date range, do not always
contain values for ALL the available DefectIDs. (Ex. No Product in this
recordset has DefectID 19 or 24)... so my dynamic columns headings "skip"
those numbers.

Using the Defects table, I was able to rework the crosstab query to
"force" the columns to display ALL 30 DefectIDs.
But... this makes the first printed horizontal line blank.
The Product (text) is blank, and all the defect columns values (num) are
blank. (probably really Null)

If I try to get rid of the blank line via my crosstab query... (Product
criteria = IsNotNull), I drop the blank line Product, but I lose my 19 and
24 DefectID columns again.

Is there some way, from the Report aspect (Ex. on Detail Format or Detail
Print) that I can supress that first Null line?
Perhaps based on this code I already have, I could eliminate that first
record...
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("qryInspectionDefectsSummary")
Set rstReport = qdf.OpenRecordset()

Hope I gave enough info... any help would be appreciated.

Thanks,
Al Camp
 
What is the SQL view of your crosstab query? If you defectIDs are always
numbered 1 to 30, you should only need to set the Column Headings property
of your crosstab to: 1,2,3,4,...30. You shouldn't have to run any code.
 
Duane,
SELECT tblFabricInventory.StyleID, tblDefectTypes.DefectID,
Sum(tblInspectionDefects.DefectCount) AS SumOfDefectCount
FROM tblDefectTypes LEFT JOIN (tblFabricInventory RIGHT JOIN
tblInspectionDefects ON tblFabricInventory.RollNo =
tblInspectionDefects.RollNo) ON tblDefectTypes.DefectID =
tblInspectionDefects.DefectID
GROUP BY tblFabricInventory.StyleID, tblDefectTypes.DefectID
ORDER BY tblFabricInventory.StyleID, tblDefectTypes.DefectID;

tblDefectTypes contains all the defects numbered 1-30, with keyfield =
DefectID. (Column Heading)
tblInspectionDefects contain the DefectID and DefectIDCount for a particular
RollNo of Product. (Value)
tblFabricInventory contains the actual FabricStyleID, linked to
tblInspectionDefects by RollNo. (Row Heading)

To my knowledge, the reason I'm getting a blank line on the returned
recordset is that there are DefectIDs returned that have no associated
product within this recordset.

The dynamic crosstab method in Sample97 uses salesperson's names as
column headings... if a salesperson has no sales... they are not listed. I
think this is just the nature of this method...
If I try to filter out the blank line, I lose those DefectID columns that
no products are associated with... and, if I force all the DefectIDs to
list, I get the blank product line again. I think Access is trying to tell
me something here...

Since I know that my DefectIDs will always be 1-30 (or perhaps in the
future even 1-35 or more), I can use a more "mechanical" method that is not
quite so "dynamic" as Sample97, but achieves the result I need. If and when
the client adds "sequential" DefectIDs, my system will handle that.

Please don't spend a lot of time on this question. If you see an obvious
problem I'd appreciate the feedback, but I'm all set for now.

Thanks for your help,
Al Camp
 
First, try use my earlier suggestion of "you should only need to set the
Column Headings property of your crosstab to: 1,2,3,4,...30". The column
headings property is available in the crosstab properties dialog.
 
Duane,
Wicked good!
Sorry I misunderstood...I didn't quite get what you meant about the
ColumnHeadings.

I'm returning to the Sample97 dynamic method and retrying with the
Headings property.
So far, it looks like that will do the trick.

Thanks a lot for your help,
Al Camp
 
If you know the column headings, then there isn't anything "dynamic" about
the query.
 
Yes. As I mentioned...
Since I know that my DefectIDs will always be 1-30 (or perhaps in the
future even 1-35 or more), I can use a more "mechanical" method that is
not quite so "dynamic" as Sample97, but achieves the result I need.
Click to expand...

I set up the Header columns in the query format for 1-35, but there still is
a bit of "dynamics" in hiding the unused 31-35 Header Names, Detail Values,
and Footer Totals. Later, when the customer sequentially adds DefectID 31
and then 32, the report will adjust and display columns 1-32

This was a good Crosstab learning experience for me...

Again, thanks for hanging in there. I appreciate the help.
Al Camp
 
Back
Top