Report set-up to show dates 2

[cav]

There should be an easy way to do this--but...Based on a parameter query* grouped by [Project]grouped by [WorkDate] Sum of [Hours]I want to create a report.<br>*The Parameter in the field[WorkDate]is(Between[Enter Start date]And [Enter End date])There will be 5 work dates.<br>I want the report to have as headers the project and the 5 individual work dates and total hours...&nbsp;&nbsp;and in the detail&nbsp;&nbsp;project name and under each date the total hours. Any help will be greatly appreciated.&nbsp;&nbsp;If you need any other info, just let me know.

 

[elizabeth]

I find the easiest way to do this is use a pop-up form to enter the parms, then on the report just add a label that refers to the parms on the form such as:<br><br>=&quot;Starting &quot; & Format([Forms]![frmPrompt4Dates]![txtBeginDate],&quot;mmm d&quot;&quot;, &quot;&quot;yyyy&quot

 

[Cornerstone]

I don't see why you cannot go through the report wizard for this one. Click Reports&nbsp;&nbsp;New and select the Query you want the report for and the fields. Then on the Grouping Step choose the field you want to group on, Project and Work Date and on the Summary Screen click Summary Option and Sum the Hours field. Try that if you haven't.<br><br>

 

[elizabeth]

Guess I misread this one. Thought you wanted to display the selection parameters as report headers. Cornerstone's answer is about setting up column headings, which is probably what you meant.

 

[cav]

<FONT FACE=monospace><br>Project 04/02/00 04/03/00 04/04/00 04/05/00 04/06/00 Hours 1x03&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;54 <br><br>3x25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;16<br><FONT FACE=monospace>&nbsp;&nbsp;&nbsp;&nbsp;This is how I tried to set up the Report...in 7 columns. I did try the wizard but could not get the dates in the header.&nbsp;&nbsp;The parameter set up the 5 dates by prompt between[Start date]And[End date. I have most of the info on the form but not in this format. Thanks again for all your help.

 

[cav]

Any other ideas?

 

[elizabeth]

Have you tried the Crosstab Query Wizard? Use Project for your rows, date for your headers and sum of hours for your intersection.

 

[Cornerstone]

Yes, I agree it seems the crosstab query may do what you want and then make a report out of it.

 

[cav]

Yes, the cross tab was exactly the way I needed the data to look...but now I can't get the parameters to work.&nbsp;&nbsp;I went into the cross-tab query to enter the parameters but the date now is in an expression and won't take the parameter info.???any ideas??

 

[elizabeth]

Are you getting an error on the query or the report and what is the error?

 

[cav]

The query worked until I put the parameter in the field created by the cross_tab Query. The field is:<br>Expr1: Format([WorkDate],&quot;Short Date&quot. The Total: Grouped by.&nbsp;&nbsp;Crosstab: Column Heading.<br>The error: Microsoft Jet database engine does not recognize&quot; [Enter Start date].&nbsp;&nbsp;THANKS FOR STICKING WITH THIS. I REALLY APPRECIATE IT!

 

[elizabeth]

When you enter the date, are you using the special formatting characters? Example: #1/1/2000#<br>There was some problem I kept runing into trying to collect parameters this way in a query but I can't remember exactly. If you can't get it to accept the built in [] method, you can always pop up a form and refer to those fields in your query, as in: <br><br>[Forms]![frmPrompt4Dates]![txtBeginDate]<br><br>BTW, crosstabs <i><b>reports</b></i> can be tricky because the underlying query is dynamic but the report is not. So if the data changes and the report calls for data that is not in the query, the report will error out. Example: Using Column headers of employee names. Employee John leaves, and his data is deleted, but there is a column looking for data on John, so the report errors out on any John fields. You can build a dynamic report but it's pretty involved, for this reason I usually just use the crosstab query itself with no report.