Sub-totals in a Page Header?

[TammyT]

(I didn't get any responses last week; let's try again!)

The report is displaying survey data for a 1 month period.

In the page header there are 2 rows of text boxes to display each day in the month - top row shows the weekday (format ddd = Mon, Tue, etc); bottom row shows the date (format m/d = 4/15, 4/16, etc).

Also in the page header are 4 text boxes that total the following:

- grand total surveys completed (all calls - (unavailable + refused)
- grand total surveys not available
- grand total surveys refused
- grand total all calls

What I also need to display is the daily total of surveys (same 4 types as above). I know I need 4 rows of text boxes to go under my date rows - 1 row for each of the total types; I'm just unclear as to what goes in those text boxes to get the totals!

My available field list contains:
- Date (of the survey)
- Ticket# (unique survey identifier)
- Employee name (not good to use, as not all employees are surveyed each month, & some employees are surveyed multiple times)
- Unavailable (yes/no data type)
- Refused (yes/no data type)
- D0 through D30: column headings in the crosstab query that is used to create this report. They are also the data on which the date text boxes are based.

Did I leave out any pertinent info?

Thanks!

+Tammy

 

[dhookom]

How do you get totals in the Page Header section?
If your report's record source contains a Date field, why do you also have 31 date columns?

Could you type in some sample data and what you would expect to see in your report? We don't need to see 31 date columns when 3-4 would probably be enough to illustrate your needs.

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]

 

[TammyT]

1) I discovered that, if I get totals in the Details section, I can then reference those fields in the Page Header.

2) You helped me create those columns in the page header from the crosstab query, remember? We worked on that for almost 2 weeks! (see thread703-1039756)

The reason I'm putting the fields in the Page Header is for formatting - I'm duplicating an Excel report that's being done manually right now - I'm using Access to automate the report.

How do I insert a display? I can "paste" in part of the Excel sheet to show the formatting, but I don't know now.

Thanks!

 

[dhookom]

Type in some sample records from your report's record source and then how you would like them to calculate and display in the report. Please use TGML for fixed space fonts so the columns line up in the Preview Post.
[ignore]
For instance, you can use [tt]your text[/tt] to force fixed space text.
[/ignore]

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]

 

[TammyT]

I've seen what looks like files that are inserted into these posts - how do folks do that?

It would be easier if I could just email the Excel file to the post!!! I'll work on it & post again.

 

[dhookom]

You would need to post a file somewhere on the internet and then link to it from your post. I rarely open Excel or other files that are on the internet. It's a good way to catch a virus.

If the answer is important to you, find the time to paste or type some samples into a reply. If you can't find the time, I don't think you can expect anyone here to find the time to help you.

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]

 

[TammyT]

Here's how it should display (in the page header; I already have the day info in there, thanks to your previous help!):

Day of Month: Mon Tues Wed
4/25 4/26 4/27

Surveys Completed = 9 10 12
Surveys Not Available = 3 5 3
Surveys Refused = 4 1 0
Totals Surveys for Day = 16 16 15


Then I have the totals for the month below the days; this parts works fine, so I won't add it.

As for the sample data from the source: would you want the table info, or the query results?

Is there someplace here where I can actually upload the Db (I can remove all of the non-related data)? That would probably be a whole lot easier to explain from!

 

[TammyT]

It's not the time - it's trying to get all of the data that you need in a post without being too confusing. I'm doing my best here!

I've found that there is another problem anyway - the query parameters aren't working the way they should, so I'm getting more data than I want. We made the unneeded dates invisible; however, when I'm getting totals, it's including date from outside of my parameters. Here's the SQL from the query:

PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].[Ticket#]) AS [CountOfTicket#]
SELECT [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
FROM [QA Survey Table]
WHERE ((([QA Survey Table].Date) Between DateAdd("d",-30,[Forms]![frmQASurveyReports]![EndDateChoice]) And [Forms]![frmQASurveyReports]![EndDateChoice]))
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
PIVOT "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30");


I think the WHERE statement is overriding the PARAMETERS at the top.

 

[dhookom]

As for the sample data from the source: would you want the table info, or the query results?

I would expect to see the query results in a format like
[tt]
Employee Unavailable Refused Date D0 D1...
a 5 7 4/20/2005 3 4
a 8 12 4/21/2005 5 22
b 1 2 4/20/2005 3 2
[/tt]
The sample data should match the display of the desired results.

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]

 

[TammyT]

Close - the column headings are right, but my query data shows differently.

Under Unavailable & Refused, there are check boxes that are either checked or blank, as that is a yes/no field & a row heading, not a total field. Is there a way to change that without changing the table info?

There is also a Row Heading called Ticket# that displays the call ticket # for that survey. There is 1 row/ticket#.

Also - in the D0 - D30 columns: since it's just 1 row/ticket #, there is a 1 in the corresponding cell that matches the ticket # w/the date that is represented by the column heading (does that make sense?)

You made me realize something - I tested by removing the row headings for Employee & Ticket # - now I just get 1 row/date, and the correct count of tickets for that date. The problem becomes the Unavailable & Refused data - I'm not getting a break-down of which calls calls have them checked.

Also - I still have the problem of getting, for instance, 3/31/05 data when the dates on the form (that the parameter is pointing to) are 4/1/05 - 4/30/05.

 

[dhookom]

Tammy,
I am done doing all or nearly all of this for you. You can't even take the time to post back with sample data and desired display. I still can't figure out why you would use a single date field to calculate both column headings and row headings.

I can't afford the time it takes to walk you through this. Hire a consultant, do some of your own research, or start a new thread. I think you are very close to what might be considered abusing the purpose of these forums.

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]

 

[TammyT]

Duane -

I'm very sorry that I've imposed on you such. I'm not a full-time programmer nor have I had "deep" training.

I did not mean to abuse the forums - I had originally asked what I thought was an easy question; it turned out to be a lot more difficult & complex than I imagined, & beyond my scope of understanding.

As for a consultant - I am a State employee & do not have that option. Research: I've searched through the help files & the FAQs & not found what I've needed.

I will advise my supervisor that we simply may not be able to accomplish what she is asking, & she will need to return to using the Excel file & manually entering the information.

I do thank you for your help over the past few weeks, as I have learned much.