Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic Crosstab report 2

Status
Not open for further replies.
KellyJo

KellyJo

Technical User
Joined
Sep 14, 2004
Messages
24
Location
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
The solutions.mdb is a very inefficient and not too flexible method of creating dynamic crosstab reports. THere is a more flexible method at
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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];
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

telephoto
  • Locked
  • Question Question
Report waiting for printer before displaying results
Replies
3
Views
644
dhookom
dhookom
JimStrand
  • Locked
  • Question Question
Expression Limit on Sum IIf In Method ? Used in Report Calculation
Replies
1
Views
1K
dhookom
dhookom
TrekBiker
  • Locked
  • Question Question
Print multiple copies of report data
Replies
5
Views
1K
dhookom
dhookom
SamuelR
  • Locked
  • Question Question
Dlookup in a report
Replies
13
Views
1K
dhookom
dhookom
monkeysee
  • Locked
  • Question Question
Muti Page Report
Replies
5
Views
986
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top