Solved

How format report / subreports

Posted on 2014-07-29
13
246 Views
Last Modified: 2014-08-04
First of all please see the attached file.

I have a report that will contain 34 "blocks" like the example file shows.

These blocks will be 9 across and 4 down (the last row could only have 7 blocks.

But here is the challenge.  The data comes from a query.  But if any give block has no data (such as example "B", or "F", etc., I don't want it to show on the report.  So if for example there were only 19 blocks that contained data, then the report would be 9 across and 3 rows deep with the last row having just one block like the example.

Question:  How do I do this?  I suppose the query could be a make-table query and the report data could come from that made table which would only contain blocks with data.  But the dExampleoes the report actually have to have 34 sub-reports?

I'm sure I've done a lousy job trying to explain this one.

--Steve
0
Comment
Question by:SteveL13
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40227858
"The data comes from a query."
Can we see this query please? (if possible in a sample file...)

Dynamically removing "columns" is always a bit tricky.

I am just thinking if you can display this info in a different way to avoid all the machinations of removing columns dynamically.

But lets see if another expert has anything more innovative...

;-)

JeffCoachman
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40227921
Try playing around with this as a subreport with snaking columns.  Across then Down works fine.  I could never get Down then Across to work correctly in a subreport.
0
 

Author Comment

by:SteveL13
ID: 40228002
Do you mean subreport(s).  Like 34 of them?  I suppose I can do that.  But somehow I have to make a subreport not be there if there is no data.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 40228538
I agree with Pat.  You would just need a single subreport.  Under its Page Setup tab, set it for 9 columns.  If you are doing portrait, you should probably set your page width to about 7/8 inch with column spacing set to zero.  Be sure to select "Across, then Down" in your column layout.  Try 1/4 inch margins in your main report.  Of course your numbers will be different if doing landscape.  Just experiment.  Here are screenshots of a quick one I just did:
9 columnscolumn setupAs for your recordsource, just revise your query's WHERE clause to exclude the records where those fields are null.  Hope this helps.

Ron
0
 

Author Comment

by:SteveL13
ID: 40228919
Interesting.  I will surely give this a try.  But I do not know how to revise the query to exclude fields that are null.  Can someone help?  Here is a sampling of the SQL:

SELECT tblMatlSpecsChemical.HeaderID, tblMatlSpecsChemical.Spec_ID, tblMatlSpecsChemical.C_min, tblMatlSpecsChemical.C_max, tblMatlSpecsChemical.C_aim, tblMatlSpecsChemical.Mn_min, tblMatlSpecsChemical.Mn_max, tblMatlSpecsChemical.Mn_aim, tblMatlSpecsChemical.P_min, tblMatlSpecsChemical.P_max, tblMatlSpecsChemical.P_aim, tblMatlSpecsChemical.S_min, tblMatlSpecsChemical.S_max, tblMatlSpecsChemical.S_aim
FROM tblMatlSpecsChemical
WHERE (((tblMatlSpecsChemical.HeaderID)=[Forms]![frmMatlSpecsHeader]![txtHeaderID]));
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40229151
Try this:
SELECT HeaderID, Spec_ID, C_min, C_max, C_aim, Mn_min, Mn_max, Mn_aim, P_min, P_max, P_aim, S_min, S_max, S_aim
FROM tblMatlSpecsChemical
WHERE HeaderID=[Forms]![frmMatlSpecsHeader]![txtHeaderID] AND (C_min IS NOT NULL OR C_max IS NOT NULL OR C_aim IS NOT NULL)

Open in new window

I only added 3 fields in this criteria because of your sample but if you intend to show all the other fields then revise your criteria accordingly.

Here's a variation on the criteria:
WHERE HeaderID=[Forms]![frmMatlSpecsHeader]![txtHeaderID] AND NOT (C_min IS NULL AND C_max IS NULL AND C_aim IS NULL)

Open in new window

Ron
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:SteveL13
ID: 40230232
I have this SQL now after added another 3 fields:

SELECT tblMatlSpecsChemical.HeaderID, tblMatlSpecsChemical.Spec_ID, tblMatlSpecsChemical.C_min, tblMatlSpecsChemical.C_max, tblMatlSpecsChemical.C_aim, tblMatlSpecsChemical.Mn_min, tblMatlSpecsChemical.Mn_max, tblMatlSpecsChemical.Mn_aim
FROM tblMatlSpecsChemical
WHERE (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_min]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_max]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_aim]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_min]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_max]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_aim]) Is Not Null));

But when I run the query I still see the empty fields and don't want to.  (Mn fields are empty in the underlying table)
0
 

Author Comment

by:SteveL13
ID: 40230248
Here is what I'm getting (see attached)

Query result
0
 

Author Comment

by:SteveL13
ID: 40230252
(I don't want to see the "Mn" fields)
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40230310
But I do not know how to revise the query to exclude fields that are null
 A query has a fixed set of columns.  If you want to eliminate some, that is a different query.  You can't eliminate columns on the fly.  Your query can be built to select or ignore ROWS but columns are always fixed for a particular query.

In any event, queries are not meant to be viewed.  They are meant to be used as the RecordSource for forms/reports or as recordsets that you process in VBA.  In a form or report, you have the ability to hide/show controls and that might solve your problem.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40230794
(I don't want to see the "Mn" fields)
Your report is where you would view the results of your query so I'm not following what you mean by this.  In the sample image you had, there were 3 cells under each column.  Do those refer to C_min, C_max, and C_aim?  Do you intend to have more than 3 rows for each group or just these specific three?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40230804
Steve,

If your question here is :
"Can this be done easily?"
...then the answer is no.

As you know, ...given enough time and energy, ...anything can be done...
;-)

Pat Stated:
"Your query can be built to select or ignore ROWS but columns are always fixed for a particular query."

...the same can be said for a report.
A report hardcodes the fields, (Based on the recordsource)

In any event, you can simulate hidden columns in a report by shrinking the control's width, then moving the remaining controls over to fill the empty space.
(see the attached sample file)
But the code to do this is complex, ...and would have to be even more complex to include your "wrapping" requirement.

Lets see if the other experts can help further...

JeffCoachman
Access--EEQ27327999HideFieldsColumnsInAR
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40230841
I'm a bit confused with what you want to show in your report. Can you identify the fields here?
img
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now