Solved

How format report / subreports

Posted on 2014-07-29
13
248 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 35

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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 35

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

777 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