Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Linked Reports Nightmare
Daniel Schuster 
     
4 years ago
I'm probably doing this wrong in the first place, so feel free to point me to the video or class that teaches how to do this properly.
Here's the situation:
We are an Army Band with lots of performing groups and musicians who change between groups constantly. We make 'Job Sheets' to identify which musicians are performing on which performance. We also have lots of turnover in the Band since the Army moves us every 3 years. I want a Report that shows the current list of musicians in our organization, arranged by instrument, that will auto-update as people come and go. I created a Report that does this by making individual Queries to isolate the different instruments, then individual Reports based on those Instrument Queries (15 Queries, 15 Reports). Then, on my big Report, I dragged and dropped all those Instrument Reports and resized them so they all fit. However, as you can see in the pictures, there is some space on the form where I can't put that box - it jumps from overlapping the one above to overlapping the one below.

Here is the question - is there a better way to manipulate this report(s) and/or is there a better way to do this in general? Any advice or topics to learn about would be appreciated.
Daniel Schuster OP  @Reply  
     
4 years ago

Daniel Schuster OP  @Reply  
     
4 years ago

Daniel Schuster OP  @Reply  
     
4 years ago

Adam Schwanz  @Reply  
           
4 years ago
Instead of doing separate queries for every instrument. Use an Aggregate Query and use the group by function to group them. I think that would get you a lot cleaner information to work with
Daniel Schuster OP  @Reply  
     
4 years ago
Thank you Adam. I didn't know that was a thing. I had to do a little work around that because we want the instruments listed in something called 'score order' (woodwinds, brass, percussion) and I got that by making an extra field in my table then sorting it by that first.

So I have everyone listed, but I still need individual queries in order to pull them into the Reports, right?. Otherwise, I just have a long list of musicians with their instruments but what I need to make is something that looks like the first two pictures above, where the instrument is a heading above the musicians who play it.

I'm trying to automate this and not have to manually move the report around whenever there is a change of personnel. But maybe I'm hoping for too much?
Scott Axton  @Reply  
        
4 years ago
Daniel
Here are another couple of videos for you that I think would be of value:
Custom Sort Order
Grouping

As far as the change of personnel I would add a Yes/No - "IsActive" field to your table.  Then only print those where IsActive = Yes.
Daniel Schuster OP  @Reply  
     
4 years ago
That's what I needed!!!! Thank you!!

Next question, I want the report to be contained within a certain part of the page and create 2 columns. I am still nesting the one Report inside the other Report (since they are pulling from 2 different tables) so changing the page layout on the InstrumentR doesn't seem to affect it when I embed InstrumentR into JobSheetR. I'll attach pictures.
Daniel Schuster OP  @Reply  
     
4 years ago

Daniel Schuster OP  @Reply  
     
4 years ago

Scott Axton  @Reply  
        
4 years ago
Dan -
I really don't know of an elegant way to have columns on your FORM.
It is definitely possible to have the columns in a printed report using your groups like the REPORT shown in this older Report Columns Tips & Tricks

Just brainstorming here.  You might be able to set up a SubForm for each of you band's sections  Them place multiple SubForms on the Main Form.
Scott Axton  @Reply  
        
4 years ago
I don't see that you are there yet in your courses, but you could also incorporate what is called a SubReport into your main report.  Basically a report with in a report.

That is covered in Access Expert Level 9
Daniel Schuster OP  @Reply  
     
4 years ago
I am focusing on the Report, not the Form, sorry about that confusion. I set up my InstrumentsR Report with the columns like the video showed. However, when I make InstrumentsR a SubForm of JobSheetR, it doesn't keep the columns (pictures attached above). I suppose I can create two different Reports with two different Queries that split the data in half - but there's got to be a way to tell Access to limit the information to the size of the SubForm.
Richard Rost  @Reply  
          
4 years ago
Columns on a form is a nightmare. You're better off scrolling. On a report, it's no big deal. If you're having trouble with a subreport, just use a grouping level for the musicians and put the rest of the data in a group header, much like I do with the invoices in Access Expert 9.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/6/2026 4:30:20 AM. PLT: 0s