Crosstab Query Combination Question

[karassik]

I am using Access 2000.

I have a crosstab query built on jobid as row, summary actual hours as value and operationID as column. I would like to have essentially the same thing in the same query, but limit the acutal hours dates. It won't let me have more than one value per crosstab query. Do I build multiple crosstab queries then have select query that integrates the two? or?

thanks,

Nishan

 

[gol4]

you need to use parameters
under query /parameters

build your parameter then on the query add the field and where it says group by change that to read where then under criteria add you parameter

 

[karassik]

Sorry I want both All and Last Week. So in crosstab I can't have two. I am building two crosstab queries then combining them with a select query.

I am having problems with the Cross tab left join, it won't show all records (OperationID's) from my operation table, only those with numbers in them. Anyone see something that I missed?

TRANSFORM Sum(tblHoursDetail.HoursDetail) AS SumOfHoursDetail
SELECT tblHoursDetail.JobID
FROM tblOperation LEFT JOIN tblHoursDetail ON tblOperation.OperationID = tblHoursDetail.OperationID
WHERE (((tblHoursDetail.HoursDate)>=Date()-Weekday(Date(),2)-7))
GROUP BY tblHoursDetail.JobID
PIVOT tblOperation.OperationID;

Thanks,

Nishan

 

[karassik]

I solved it by putting all the OperationID's in the Pivot area:

PIVOT tblHoursDetail.OperationID In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17);