Report control source: tbl or qry? 1

[KerryL]

I have a report that displays summary data from the database, and I used a data table as its source. The summary data is displayed in the Details section of the report.

However, when I print the report, it prints a page for each record contained in the table, so I end up with 50 (so far) pages of the same summary report.

Is there a way around multiple copies of the report? Or is it best for me to use a query as its control source instead of the table itself?

 

[traingamer]

I usually put summary data in a group header or footer as opposed to the detail section. Without specifics for your report, I'm not sure if that applies or not.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dhookom]

Is your table "summary data"? Tables are generally details. Queries allow you to summarized records/data. You can also summarize in a report by using aggregating functions (Sum, Avg, Count,...) in footer sections and hiding your detail section.

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]

 

[KerryL]

The report contains items such as:
- All Notices (records) received YTD
- All Notices received for this period
- Notices related to mass layoffs YTD & Period
- Notices related to plant closings YTD & Period
- Number of team interventions YTD & Period
Etc, etc

IOW, the items on the summary report are sum totals of records in the database, some more specific to filtering criteria than others.

As for the table itself, this is a relatively small database. So there is just one main data table where all data entry information is stored.

I figured since the data I need to manipulate is all in one table, I could base the summary report on the table instead of a query, and I was just wondering if that's wise because the Expression for some of the report fields are getting fairly lengthy.

 

[dhookom]

If you don't want to see any details, then you can hide the detail section and place text boxes in your Report Footer section with control sources like:
- All Notices (records) received YTD
=Sum(Abs(Year([YourField]) = Year(Date())))

- All Notices received for this period
You need to figure this one out since you didn't define "period"

- Notices related to mass layoffs YTD & Period
See above

- Notices related to plant closings YTD & Period
See above

- Number of team interventions YTD & Period
You didn't give any information on how you determine
a team intervention. see above

Etc, etc

You can use the base syntax in your control source like
=Sum(Abs( ...expression... ))
Place an expression in this calculation that returns either true/-1 or false/0.

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]

 

[KerryL]

When I move the text boxes from the detail section to the footer section, or place them in the header section, they display "Error"

It's as if the Expressions cannot complete their calculation because they're no longer tied to the table.

 

[dhookom]

1) does your report have a Record Source of your table/query?
2) did you move the text boxes to the Page Footer or Report Footer?
3) can you share any expressions you used in control sources?
4) can you share any field names or other pertinent information?

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]

 

[KerryL]

I haven't changed anything on the report. All I did was move the body of the report from the detail section to the footer section (then the header section). The same text boxes whose Expressions work in the Detail section give me an #Error in the Header & Footer secions.

I also created a new text box in the header & footer and copied one of the expressions into its control source. That produced the #Error as well.

Here are the details you asked about:

Report = fptActivitySummary
Source = tblWARNData
txtbox = txtNoticesYTD
Control Source = =Sum(Abs(Year([EntryDate])=Year(Date())))

 

[dhookom]

KerryL,
Please re-read each of my posts. There was a key piece of information repeated in each that you seem to have ignored. You didn't answer one of my questions.

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]

 

[KerryL]

Sorry, I thought I covered everything.


1) does your report have a Record Source of your table/query?
Yes, it is table "tblWARNData"


2) did you move the text boxes to the Page Footer or Report Footer?
Page Footer

3) can you share any expressions you used in control sources?
Expression for txtNoticesYTD: =Sum(Abs(Year([EntryDate])=Year(Date())))


4) can you share any field names or other pertinent information?
Report = rptActivitySummary
Source = tblWARNData
Period date range comes from "txtBeginDate" & "txtEndDate" on "frmReportCriteria"

 

[dhookom]

From a previous post

If you don't want to see any details, then you can hide the detail section and place text boxes in your [red]Report Footer section[/red] with control sources like:

You can't bind aggregate expressions (sum, avg, ...) in Page sections.

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]

 

[KerryL]

Wow, I looked right at that and had a brain fart. Didn't even register that you were talking about the report header/footer instead of the Page Sections.

I apologize for missing that. Also, I wasn't aware that you can't bind expressions in the Page Sections.

I moved my fields to the Report Header section (so I could add a timestamp in the Report Footer) and now my expression fields are calculating perfectly.

Thank you, Duane. And again, I'm sorry for not catching the most important part of your response.

Have a great day,
Kerry

 

[dhookom]

Lots of people confuse the Page and Report sections. I wish the was a special [Read and Acknowledge Me] TGML tag that could be used to prompt a user prior to reading more.

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]