Text Box in Master Report from a SubReport 1

[Dontremb]

Ok, this is a simple question, but I'm not sure if it can be done.

I've got a Master Report, with about 30-50 sub reports on it.

Each of the subreports show a single number, which I'm getting from BILLIONS of queries. (I'm not real worried about the number of queries, my naming convention is good.)

The problem is arising because it's so difficult to work with subreports on a report. Getting them all lined up, putting them in a table (not a real table, just lines around them.)

So, I tried making a text box on the master form, and having the control source as the single object from one of the sub forms, and it just returns "#Name" Very annoying.

Is there ANY other way to get lots of information from several reports without having to use a subform?

(Took me a LONG time to ask that question, didn't it? Sorry.)

 

[dhookom]

Can you clarify whether you are referencing subforms or subreports? You aren't using subforms in place of subreports are you?

When you need to pull information from multiple record sources, it is sometimes easiest to put the information into temporary "reporting" tables. You can also use code or functions to display values.

It is difficult to understand your report since you haven't given us and record sources or Control Sources.

Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

Ah, I hadn't realized that I interchanged subform with subreport.

I meant subreport for everything.

Hrm... Reporting tables sounds interesting, any idea how to make one of those?

I would like to put all the queries' data into one place, just so I can make a single report off of that data. However, the queries aren't related in ANY way.

For instance: I have: qryAsianApplicantsTotal

This is a query that counts the number of records on: qryAsianApplicants

That query simply sorts all my data by anyone of that race/ethnicity group who applied to a vacancy in FY09 who is not Hispanic.

Then, I have: qryAsianHiredTotal

You can probably guess what this query does.

To put them on the same report, I have been using Subforms, but it's so clunky. I was just wondering if there's another way.

Your Temporary table sounds like an interesting idea, but I have NO idea how to do it.

 

[dhookom]

A reporting table is a standard table that might have all the records deleted prior to beginning a reporting process. Various queries or code could then put values into the table or tables. These "temporary" records would be used in the reports/subreports. Your statement 'Getting them all lined up, putting them in a table' suggests there might be some order or relationship either across or down.

Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

I see. My 'getting them all lined up' is just about making the report look good.

Right now, I have all these subreports strewn across the master report, and getting them lined up, and in columns would be infinitely prettier to the eye.

Would this be a good forum to ask about help on the code to make this 'reporting table?'

 

[Ray1127]

If each query is returning 1 number create a query with those queries as the source (You do not need to link them) I have created several reports that include multiple queries that are not linked into 1 query then design the report based on that 1 query. for example

Select q1.field1 as firstfld,q2.field1 as secondfield
from q1,q2

as long as each query in the from clause returns only 1 record the query will return 1 record. If 1 query has multiple records but the rest return only 1 record you will still be fine just remember that those fields will be repeated for each record. If more than 1 query has multiple records you'll get a cartesian product which you don't want.

 

[Dontremb]

Ok, I'm going to try this right now.

I thought that you couldn't put fields in a query unless they were related.

 

[Dontremb]

Ok, so I'm a complete idiot, it turns out.

Thank you for helping dhookom, but Ray1127's answer was EXACTLY what I needed.

I couldn't really make sense of the SQL (not your fault, of course, I just think you assumed I had a better grasp of SQL than I actually do.)

I just made a new query, with all my total queries in it, and used each of their one fields.

Works perfectly.

Thanks again.

 

[Dontremb]

Ok, so now, I've run into the problem where you can only have 32 queries linked to a query.

I'm pretty sure there's no way around this "Query too complex" error, so I've been looking at other, more complicated, and more annoying ways of doing this.

 

[dhookom]

I think Leslie (or someone else) gave you some good advice about using totals queries etc. I think your main issue is either your table structures or your report solution. Since we don't really know what your data/tables look like or what information you are attempting to get into your report, we can't provide any good alternatives.

Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

Just to finish this thread up. I DID find a way around, still using subreports, but pulling the info from the subreport to my main report.