Dynamic Crosstab report 2

[KellyJo]

Hi! I have a crosstab query that I want to base a report on. The dynamic portion is the "Date Scheduled" field. I need a column for each individual date. Do you know I can code this so that it will work? So vague, I know, but I'm hoping that you know what I mean. I don't want to complicate things with TMI. Thanks sOOO much!

KellyJo

 

[RobPotts]

There is a method on the solutions databse which comes with access. The Developers handbook by getz, letwin and gilbert also has a simple method for doing this.



Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[dhookom]

The solutions.mdb is a very inefficient and not too flexible method of creating dynamic crosstab reports. THere is a more flexible method at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

KellyJo,
Is is possible that your reports can be limited to a specific number of days (columns)? I would definitely recommend a "7-day" report or "14-day" report since there is a much easier solution.

Let me know if this is possible.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

Duane,

I looked at your link and it's great. It doesn't quite work for me, though, and that could just be because I'm very novice at coding. It's funny how you get to a point that you HAVE to use it to improve your product...

I need something like this, but I need it to automatically add the labels and bound controls for the dates I specify. Our employee schedules run on 28 day cycles. I don't want to have to go in every cycle and make a new report, or modify my old one. I'm trying to make this data base so user friendly that, barring a major catastrophe, the hospital be able to use it even if I'm no longer here. Any suggestions on the labels and controls on the report? Thank you!!!!

KellyJo

 

[dhookom]

I don't think you answered "Is is possible that your reports can be limited to a specific number of days (columns)?" Can you provide more information? Maybe your current SQL of the crosstab?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

This is my coding. The query works GREAT. But, I don't know how to tell my report to do the same thing that this query does for the column headers. Any ideas?

Also, I can't restrict my report to just 14 days. Because of the 28 day cycle, the dates are always changing. If we were on a 30 or 31 day cycle, it'd be fine because I could just do 1-30 or 31 across the top because they would correspond with the date. But say a cycle starts on the 5th, I don't want to have a 1 as the column header because the employees would think that was the 1st, noth the 5th. Make sense? Any suggestions would be awesome! Thanks a bunch!
KellyJo

TRANSFORM Count([Staffing Query].Title) AS CountOfTitle
SELECT [Staffing Query].ERName, [Staffing Query].Department, Count([Staffing Query].Department) AS [Total Of Department]
FROM [Staffing Query]
GROUP BY [Staffing Query].ERName, [Staffing Query].Department
PIVOT [Staffing Query].[Date Scheduled];

 

[dhookom]

All I want to know is if you can create a report with a specific number of dates/columns. Forget for a minute what the column headings will look like 'cause I have a method to assign the column labels.

Does it work for you to have a report that displays a set number of columns? For instance 28 columns/dates that could be any 28 contiguous dates.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

OHHH!!! There's my daftness coming out again. Yeah, I can assign them. That's not a problem at all. I feel so smart! haha...

What do you recommend after I make the 28 columns?
Thank you!

 

[dhookom]

Use relative columns in your query based on a date entered by a user in Forms!frmDates!txtEnd. Your query would look like:

Parameters [Forms]![frmDates]![txtEnd] DateTime;
TRANSFORM Count([Staffing Query].Title) AS CountOfTitle
SELECT [Staffing Query].ERName, [Staffing Query].Department, Count([Staffing Query].Department) AS [Total Of Department]
FROM [Staffing Query]
GROUP BY [Staffing Query].ERName, [Staffing Query].Department
PIVOT "d" & DateDiff("d",Forms!frmDates!txtEnd,[Date Scheduled]) IN ("d0", "d1", "d2", etc , "d27");

The Column Headings property would have the values:
"d0", "d1", "d2", etc , "d27"
The column with a heading of d0 will be the values from the date on the form. d27 will be 27 days earlier.

Then in the report use text boxes for the column labels. Set the control sources to:
=DateAdd("d",0,Forms!frmDates!txtEnd)
=DateAdd("d",-1,Forms!frmDates!txtEnd)
=DateAdd("d",-2,Forms!frmDates!txtEnd)
...
=DateAdd("d",-27,Forms!frmDates!txtEnd)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

AWESOME!! The query works, but now I can't get the column labels to work. The values in the report DO work, but the lables don't. When I'm in Print Preview, after entering in the dtae for txtEnd, the error #Name is in place of where the column lables should be. I'm so close! Can you help? Thank you, thank you, THANK YOU!

KellyJo

 

[dhookom]

What are the control sources that you used for the column labels?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

I put them in just like you said to:

=DateAdd("d",0,Forms!frmDates!txtEnd)

etcetera

Do you know why this isn't working? The data columns work great, it's just the labels that give me the #NAME error. Could it have anything to do with the fact that only "d" is used as the variable instead of the entire date? I tried to enter just the day of the month, (15), and it wouldn't let me because of field type. Any ideas? Thank you!
KellyJo

 

[dhookom]

Is the form still open? Did you name your form and control frmDates and txtEnd?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KellyJo]

I didn't realize I actually had to have a form for Dates!txtEnd. I thought that since it was a parameter in the query, it would automattically calculate. I guess I was wrong. What do I set my control as in the Dates form? Should I have a dates table, too?? Sorry to be so daft, just a little new at this stuff. Thanks so much!
KellyJo

 

[KellyJo]

Wow! I got it to work! You are the MAN! Duh, I had my fields named wrong on the frm dates. How should I run my macro to open the frmDates? Should I run it on report open? I think this way, I'll be able to tell it what date to put in right up front. Good idea?

KellyJo

 

[KellyJo]

Also, is there way to tell the report to just put in the day of the month part (15th, 16th, etc.) on the column label? Thanks!

 

[dhookom]

I always open the form first, enter the criteria values, and then click a button to run the report.

You can set the format property of your column label text boxes to any date format you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.