Report with multiple data sources

[kenif]

Hello all. I have learned much from this site, thank you.

I am trying to creat a report that summarizes many tables/queries. If I can get this to work, I would like to have 15 or 20 items shown as a 'snapshot' of activity. I wish there was (is there?) a way to access the reccordcount displayed at the bottom of a viewed query and plug it into a report.

Heres what I have found so far.

If I create a bound report and place a hidden text box in detail sec. =Sum(Abs([ssn]<>"")) and then in footer a text box to refer to that value I get a correct total.

My problem is trying to add totals from more than one table/query. I have tried the following with bound and unbound reports without luck.

Trying to specify the source table/query
=Sum(Abs([individuals_1001]![ssn]<>""))

and

Trying to use dlookup:
dlookup("Sum(Abs([ssn]<>""))","individuals_1001")
and many variations of the above.

Thanks in advance.

ps: picture a cardboard sign... 'will trade fish tank answers for Access answers'

ken

 

[dhookom]

Placing a text box in a report footer section with a control source like:
=Count(*)
will display the total number of records in your report.

I have no idea what all of this has to do with the subject of your thread.

A report can contain multiple subreports. A single query can be created from multiple queries. You can use DCount(), DSUm() and other functions in control sources.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kenif]

Thanks for the answer Duane. I had not used the count() function before. That works simular to my first example if I use a subreport for each total I want to display. I need at least 15 totals on this report, and 15 subreports would get quite cumbersome. I was hoping to be able to just directly use a text box. I read that DCount() and DSum() are not available in reports so I was trying to use DLookup(). Is that the right direction and my syntax is bad?

Ken

 

[dhookom]

I don't know where you read that DCount() and DSum() are not available in reports. That is not true. Normally I recommend against using these functions if the "domain" of DCount() is the same as the record source for the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]