Microsoft Access Crosstab query

[ba543]

I created a cross-tab query in MS Access and and I'm trying to make that query into a report in Access. I am using date promts to extract the information for certain time periods. When I try and make a report out of the query it doesn't show any fields, so that I might create the report. It allows me to create the report in design view but because of the parameters I made for the prompts and because I have prompts in my where clause, it causes me to type into the prompts three times and then it shows a report thats really terrible looking. Just curious if anyone has run into this trouble, and could give me some pointers.
Thanks

 

[dhookom]

It would help if you posted your SQL. Your report will expect a static set of fields/columns from your query. Do you have this? If so, add the derived column names to the Column Headings property.

Also, abandon the parameter prompts in favor of referencing controls on a form ie:
Forms!frmDates!txtStartDate and Forms!frmDates!txtEndDate

Make sure you enter these control names into your Query|Parameters with their data types.


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

 

[ba543]

dhookom here is the SQL code hope this helps.

PARAMETERS [Enter the start date:] DateTime, [Enter the finish date:] DateTime;
TRANSFORM Sum(a.HR_CNT) AS Total_Hours
SELECT d.RSRC_NAME AS Resource_Name, Sum(a.HR_CNT) AS [Total Of Total_Hours], [Enter the start date:] AS Date_Started, [Enter the finish date:] AS Date_Ended
FROM ADMUSER_PROJECT AS b, ADMUSER_RSRC AS d, ADMUSER_RSRCHOUR AS a
WHERE (((a.RSRC_ID)=[d].[rsrc_id]) AND ((a.WORK_DATE) Is Not Null And (a.WORK_DATE) Between [Enter the start date:] And [Enter the finish date:]) AND ((a.PROJ_ID)=.[proj_id]) AND ((b.PROJ_SHORT_NAME)="TAECOMMMQRTEAM-2004" Or (b.PROJ_SHORT_NAME)="TAECOMMTECHTEAM-2003" Or (b.PROJ_SHORT_NAME)="TAECOMMTECHTEAM-2004" Or (b.PROJ_SHORT_NAME)="TAECOMMTESTTEAM-2003" Or (b.PROJ_SHORT_NAME)="TAECOMMTESTTEAM-2004"))
GROUP BY d.RSRC_NAME
PIVOT b.PROJ_SHORT_NAME;

 

[dhookom]

I suggest you implement the two suggestions that I made in my earlier reply.

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