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.

 

[TammyT]

I just realized - no matter how I "rearrange" the row/column/value choices, it's not reading the parameters - I'm getting all of the records in the table, not just those between the identified dates.

 

[dhookom]

I'm not sure how you got to that SQL view however, try:
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 "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) IN ("D0","D1","D2","D3");

The D0 column will be the same date as your EndDateChoice. D1 will be a day earlier, etc.

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]

Well, at least the query runs now! However, the column heading labels come out as D0, D1, D2 & D3 only - not the real dates, & only those four.

There is no data in the fields (values)

I'm still stuck on how to format for a report!

Have I used up my questions yet?

Yes, I've always been a problem child!

 

[TammyT]

1) It's still not reading the parameter fields.

2) Have you ever used Crystal Reports? You can create a crosstab report easily there - I'm looking for something similar: I should be able to use my basic query, & then pull in the data into the report as a crosstab & get totals/etc. I don't see that option with Access.

 

[dhookom]

The query only displays 4 columns because you have entered only D0 through D3 into the column headings property. If you want a wider date range, enter more Dx values.

Do you have any data from the EndDateChoice on your form and earlier by three or four dates?

We will worry about displaying the date headers in your report after you have your data correctly displaying in your query.

You should be able to delete this parameter:
[Forms]![frmQASurveyReports]![StartDateChoice] DateTime


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 understand where you're going now. However, the query dates/amount of dates are always going to vary. Mostly it will only be for a 1 month period; however, my boss may at some time want to enter the data for 3 or 6 or 12 months.

I can limit her to 1 month at a time, & put in 31 headings if I have to - is that my only option?

There are survey records from the end date back; there is still a problem with the date parameters - my query is returning all of the survey records & nothing in the D0/etc columns.

 

[dhookom]

Try delete the "D0","D1","D2","D3",... from the Column Headings property to see what values your data wants to create.

If you don't limit your crosstab to a particular number of dates, how did you plan to create your report with say 365 columns?

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 get columns labeled D103, D104, D106, D110....through D99 (counts down; there are at least 200 (maybe more!) columns that way.

2) Hadn't thought about the limiting factor, as I originally wasn't thinking about a crosstab report until you so wisely suggested it! Now that you mention it, though - good point!

Since I'm using a crosstab report, I think I'll need to limit my boss to requesting 1 month at a time, which means a max of 31 date columns.

Unless you think there's a way to use Employee (generally about 33 people; I could plan for 40 & know I'd never need that many) as the column & Date in the value, & still get it to work?

Also - still can't figure why it's ignoring the parameters & pulling in all of the records instead of just those for the dates I'm entering!!!!

 

[dhookom]

Your crosstab doesn't have any "WHERE" clause so you will get all the records. Apparently you didn't understand when I asked:
[red]Do you have any data from the EndDateChoice on your form and earlier by three or four dates?[/red]
The Column Headings value of D0, D1, D2, D3, etc will limit your displayed records to only those that have dates that are the date you entered into the control on the form and the few days earlier than the date. If you entered a column heading value of D99, your crosstab would include records with a date that is your end date minus 99 days. Understand?

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) The "WHERE" clause - I thought that is what the parameters were for?

2) I don't totally understand about the column headings & dates. Yes, there is data from the EndDateChoice on the form and earlier by three or four dates - I'm making sure that the date I'm putting there has data - surveys are being done every day (weekdays only), so the data exists.

I'm sorry it's taking me so long to get this! I'm normally a quick learner, but something about the way Access does crosstabs is throwing me, maybe because I'm used to how Crystal Reports does it so easily! Bear with me, I'll get it! (I've never had formal training in SQL or VBA for Access 2000, just "surface" training for using the forms/queries/reports/macros).

Thanks!

 

[dhookom]

Let's take a look at data we both should have. Consider this crosstab in the Northwind.mdb

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
PIVOT "D" & DateDiff("m",[OrderDate],#6/1/1997#) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13");

This shows the count of orders by country for the 14 months up to and including June of 1997. If I remove the "In ('D0',...)", the crosstab would show all months. The new, changed crosstab would not show D12 and D13 since there were no OrderDates earlier than July of 1996.

If I change the #6/1/1997# to another date, I will still generate the exact same column headings but the "window" of months will shift based on the new date. Ideally the date would not be "hard-coded" in the SQL. The date should come your control on your form. If you do this, you must set the query parameter data type as noted earlier.

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, that helped a bit (took me all morning to get it quite enough, long enough, to try to understand!).

I tried something - I changed around the "location" of the elements so that now Ticket# is my value, & Employee is in the Row Headings - it works!!! I'm getting data, & from the right dates only!

Here's the SQL now:

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
FROM [QA Survey Table]
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused
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","D31","D32");


I checked the numbers in the value fields & they are correct! I did notice that some Employee Rows were repeated because they had, on the same day, some completed surveys & some unavailable surveys; however, it did NOT duplicate the counts on those - that's a good thing!

So I think we're in business? The column headings are still coming up as the D0, D1, etc - but I believe you said we could fix that in the report?

+Tammy

 

[dhookom]

Look at this faq703-5466 and figure out how to use
=DateAdd("d",0,[Forms]![frmQASurveyReports]![EndDateChoice])
rather than the FAQ usage of months.

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 get it for the report; however, I was testing the query some more, & I'm bothered by some info it's pulling in. We only started using the Unavailable & Refused fields in 4/2005; however, when I use 03/31/2005 for the End Date Choice I get the correct values in the date columns, but it looks like I'm getting the 4/05 data in the Unavailable & Refused columns. Any ideas? (Does what I wrote make sense?!)

Thanks - we're almost there!

 

[TammyT]

Okay, now I feel like a total idiot!! I made changes to the report that I had where the totals worked fine (see thread703-1039746 - you helped me there, too!), & I deleted the Date Footer with those formulas, & I can't remember what they were - AARRRGGGGHHHHH!!!!

I put the formatted date fields in the report; however, I'm still having a problem with it only showing the dates on the form. Because I have enough "potential" text boxes for up to 31 days, it's showing 31 days back from the end date choice on the form, & ignoring the begin date. For months with 28 or 30 days, I'll get too much data. How do I get it to make the "unneeded" fields not display if they are outside the parameters chosen?

And, do you have any idea as to how I'm going to get my totals in again? It still puzzles me why something that should be so simple isn't!!

 

[dhookom]

To limit the unavailable and refused fields, you will need to use a criteria against your date field. I would use

Between DateAdd("d",-30,[Forms]![frmQASurveyReports]![EndDateChoice]) AND [Forms]![frmQASurveyReports]![EndDateChoice]

You could use code in your report to hide columns if necessary.

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 - not following you; where in the SQL am I putting in that criteria - I'm assuming you mean in the query, not the report, right? Or does it go in the report?

If I try putting it in the Criteria field for the query (using Design view), then I get an error trying to run the query that it's too complex.

Also - the "-30" part - that's assuming a 30-day month, right? What about Feb & all of the 31 day mos?

By the way - I haven't mentioned lately how much I REALLY appreciate your sticking with me on this - while I may be clueless at times, I'm learning a lot!!!

 

[dhookom]

You place the "Between..." under a column in your query for the [Date] field. Set the "Totals:" to "Where".

Regarding the -30 part, did you try subtracting 30 days from 3/31/2005 to see what you get?

Don't worry about hiding columns until you actually have stuff displaying correctly in your report. Once you get 31 days correctly showing, then you can worry about if and how to hide a column in a report. Remember I told you we could worry about the column titles in the report once we had the query working? Let's take one step at a time.

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 get it - I needed to add in another Date column on the query & put in that info - it works - gives me 31 days (okay, I got it: orig day + "negative" 30 days = 31 days!)

Do you think we're finally ready to tackle the report?! =)

 

[dhookom]

You should now have a "static" list of fields/columns which make reporting much easier. Your actual data comes from a dynamic range of 31 days.

Review the link from my posting on #14 Apr 05 13:35# to see how to create column titles in your report. Once you have the column titles working correctly, you can consider hiding columns that you might not want to see.

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]