Creating a Dynamic Report 1

[LeoPascual]

I have to create a summary report that lists problematic data. These data come from certain fields - from different tables - named xPROBLEM, where x is the name of the table the field is in.

What I'm trying to do is to create an Access Report that shows ONLY the fields that does not have a NULL or an empty string. I created a Select Query that gathers all the xPROBLEM fields (over 100 fields) from the different tables and used that as the underlying query for the report. However, most of the xPROBLEM fields are empty and I don't want to print out a 15-page summary report with a lot of empty spaces in between. Is there a way to create a report that can pull fields that have value in them? Any clue would be greatly appreciated!!!

 

[glalsop]

In your query, try setting the criteria for the relevant fields to:

Not Is Null

This will return only those records that are not null. If also wish to disregard zero-length strings, try:

Not Is Null and <> ""

Perhaps this will get you started.

I'm not sure that I follow the table/problematic data structure that you describe as serving as the report's record source.

Do you have one PROBLEM field for every table in the query?

Perhaps you could post some sample query results and a sample of what you want to see on your report.

Good luck

-Gary

 

[LeoPascual]

Thanks for the response, but setting the criteria will filter the record, not the fields.

The report's record source is a Select Query that combines all the PROBLEM fields from all the tables in the database. All the tables have n number of PROBLEM fields.

For example, I have three tables (with promblem fields in parenthesis): TRANSMISSION (Clutch_Problem,Gasket_Problem), BRAKE (Calipers_Problem, Disk_Problems) and ENGINE (Piston_Problem, Electrical_Problem, Valve_Problem). These PROBLEM fields are combined with a Select Query, which will then be used for the summary report's record source.

The issue I'm having is that a lot of these PROBLEM fields won't have data in them (no data means there's no problem). So when I generate the report, it comes out as a 5-page report with a lot of empty spaces because each fields from the Select Query have their own specific area on the report.

So going back to the example, let's say only the Clutch_Problem and Calipers_Problem fields have data in them. The way the report will look is the Clutch_Problem will be on the 1st page of the report while Calipers_Problem will be on the 4th. In between them is a huge empty space.

What I'm trying to accomplish is to have all PROBLEMS fields (from the Select Query) that has data to appear on the report side by side, without empty spaces and multiple pages. How would one create a dynamic report such as this?

 

[glalsop]

So you have some sort of a carID that ties all of these tables together, and that particular car can have 0 problems or a lot of problems.

And you have several fields on your report, one for each possible problem.

Ultimately, you may want to consider redesigning the database in a more normalized fashion, with perhaps just one PROBLEM table. The un-normalized data structure that you have currently is the cause of your troubles.

But for now, here is one potential solution (assuming that the fields are in the detail section of the report):

thread703-757909 describes a method for creating 'multiple detail bands' by placing a group in your report on the primary key field. You could insert one group on the primary key field for every possible problem field, and put just one field in the group footer for each section, and ignore group headers.

Then in the GroupFooter format event for each field, you could test for no value and hide the section if it is null:

If IsNull(Me.Field) Or Me.Field = "" Then
Me.GroupFooterx.Visible = False
Else
Me.GroupFooterx.Visible = True
End If

This will allow you to reclaim the space for empty fields.

Good luck

-Gary

 

[LeoPascual]

Thanks Gary! I think you hit a nerve. I will be out for a few days, but I will let you know how well this goes.