Make header display horizontal not vertical?

[TammyT]

Okay, my report has a date header, breaks on each day.

What I want is to have it display the dates across the page, not down - sounds easy enough, but I have not clue! Has to be done in the header - not the details. It's one field (called Date) only, not several fields that I can spread across.

 

[dhookom]

How about sharing some data with actual field names and type as well as your desired display in your 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]

 

[TammyT]

I'm not sure what else to give you - it's a report pulling survey ticket data by day; in the date header there is a field called Date that will display every date for which there are records (it's actually 2 fields, on top of each other, formatted to show the weekday & then the date as m/d).

The goal of the report is to show (across) each day in the reporting period (normally 1 month), & under each day the # of surveys done, the # attempted, the # refused, etc.

Basically, I'm replacing a manually-entered Excel sheet, so think "cells" here - where the info is in rows, not columns.

Does that help? I'm not sure I can explain it any better!

 

[mph1]

It sounds like you are trying to group dates. In the design view of the report go to Sorting and Grouping. I think this may be of assistance. Since there are choices there, try various opitions. Even if this for some reason doesn't solve your problem, you will learn.

Also, the field you have named DATE -- I would change it to something else since this word is an Access reserved word and can cause problems.

Hope this helps.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[dhookom]

[red]"I'm not sure what else to give you"[/red]
I suggested actual data and desired display in your report.

"2 fields, on top of each other" isn't very descriptive. I can only take a WAG that you might need a crosstab query that has column headings based on the date and row headings based on some other field and a count or sum of something in the value.

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]

That's it - crosstab!!!

ard to give you the actual data/display here - I work for the gov't, & we have confidentiality laws & all. Anyway, I figured if I said it needs to look like an Excel sheet that would do it.

Let me work on the crosstab - that's probably what I was missing.

 

[TammyT]

Okay, I made a crosstab query, but now I can get it to use the right dates. On my "regular" query, the date field criteria is

"Between ([Forms]![frmQASurveyReports]![StartDateChoice]) And ([Forms]![frmQASurveyReports]![EndDateChoice])"

Points to a form where the person requesting the data puts in begin & end dates - usually a 1 month period, but not always. I want the query to run only on the records inside those dates, but it's not recognizing them.

Any thoughts?

 

[dhookom]

You can use a criteria like this for a crosstab or the source of a crosstab. However, you must enter the data types of the parameters into the Crosstab->Parameters. For instance:
[Forms]![frmQASurveyReports]![StartDateChoice] Date/Time
[Forms]![frmQASurveyReports]![EndDateChoice] Date/Time

I create this type of crosstab with always the same number of columns and use a column heading expression of:

ColHead:"D" & DateDiff("d",[YourDateField], [Forms]![frmQASurveyReports]![EndDateChoice])
This creates columns with names like: "D0","D1","D2",... D0 column is the same date as the EndDate.


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]

Crosstab just isn't working, no matter how I configure it!!


Isn't there some simple code that can go into the page setup to make each new header go to the right instead of down? This is crazy!

I think I'm thinking too hard for a Friday afternoon!!!

 

[dhookom]

Share your SQL of the crosstab.

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]

Hope you're still there & checking this thread!

Here's the SQL from the crosstab query I built today - tried w/different parameters; I get the query to run, but it won't let me put in the date criteria.

TRANSFORM Count([QA Survey Table].Date) AS CountOfDate
SELECT [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused, Count([QA Survey Table].Date) AS [Total Of Date]
FROM [QA Survey Table]
GROUP BY [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused
PIVOT [QA Survey Table].Employee;

 

[dhookom]

Did you find how to specify the data types of your parameters? If so, you query would look like:

PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice]DateTime;
TRANSFORM Count([QA Survey Table].Date) AS CountOfDate
SELECT [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused, Count([QA Survey Table].Date) AS [Total Of Date]
FROM [QA Survey Table]
GROUP BY [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused
PIVOT [QA Survey Table].Employee;

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]

Okay, I can get the query to run; however, I still can't get the report to work!

Using the query as is, I don't have the option for the date header; if I switch Employee & Date, it doesn't seem to hold the parameter info.

What I need my report to display is the dates across the top (going left to right, not top to bottom), & the totals for each day (I have the formulas for those that work fine in my other report with the wrong formatting!).

Any ideas as to how to where the date field needs to go in this query (if at all?) to be able to get it as a header & to get totals off of it?

Thanks for all of your help!

 

[dhookom]

If you want dates across the top then use the [Date] field as the column header. I don't see where you are using the date range in your sql.

Did you try my suggestions from #8 Apr 05 14:30#?

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]

Okay, I put it in as

"D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice])

1) Why am I using the end date only?

2) I want the column headings to be the actual dates, at least in the reports - will I be able to do that?

3) When I preview the query, I just get 3 new columns entitled:

D" & DateDiff("d

_Date_

_Forms__frmQASurveyReports__


What did I do wrong?

Thanks again!

 

[TammyT]

Sorry - forgot to say address the other part of your answer.

When I tried putting Date as a column heading it got crazy & didn't hold the parameter info (I had mentioned that in my previous post, but may not have made it clear)

Of course, I haven't tried the switch since you advised me about adjusting the column headings - I'll keep trying!

Bear with me - I work on a Help Desk, so I'm trying to code in between calls - not easy!!

 

[TammyT]

I just tried putting Date as the column header, & Employee for the value. When I try to run the query I get an error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

???

 

[dhookom]

Show us the SQL that you think doesn't work. Always provide the sql view of queries when you are having a problem.

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]

Sorry! You'll see that I swapped Employee & Date - Date is now a column heading, & Employee is a value. (not that it helped!!). Here's the SQL that gives me the error I put in the post above:

PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].Employee) AS CountOfEmployee
SELECT [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused
FROM [QA Survey Table]
GROUP BY [QA Survey Table].[Ticket#], [QA Survey Table].Unavailable, [QA Survey Table].Refused
PIVOT [QA Survey Table].Date In ("D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]));

 

[TammyT]

Another problem will arise when I actually create the report - the Help section (yes, I know - MS Help doesn't often!) says to create a label & text box for each column heading, but that would be impossible for a report that is going to change each time new criteria is put on the form. Here's the info from Help:

Create a crosstab report with fixed-column headings
Create the underlying query
Create a crosstab query.
How?

In query Design view, display the query's property sheet by double-clicking anywhere outside the design grid and outside the field lists.


In the ColumnHeadings property box, type the column headings you want to appear in the report, separating each one with a semicolon. If a column heading contains spaces, enclose it in double (" ") quotation marks. For instance, in a query showing sales by quarter, you would type:
"Qtr 1";"Qtr 2";"Qtr 3";"Qtr 4"

Create the report
Create a blank report that is bound to the crosstab query.
How?

Add the controls for the fields you want to appear in the report.
Place labels for the row headings and column headings in the page header.


Place text boxes for the row headings and column values in the detail section.