Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How format report / subreports

Posted on 2014-07-29
13
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 39

Accepted Solution

by:
PatHartman earned 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 1000 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 39

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

670 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