MS-Access Crosstab error

[AQAGuy]

I have seen a couple of other threads on this subject, but the solutions presented are not working in my case (at least they're not working the way I'm implementing them). I have a crosstab query which is pulling data from a select query. The select query has some 'WHERE' statements, but they are not prompting the user for a parameter. Here is the first query:

SELECT JB_task_number_summary.year, JB_task_number_summary.month, JB_task_number_summary.Date, JB_task_number_summary.WBScode, JB_task_number_summary.ProjectNumber, [ProjectNumber] & '/' & [WBScode] AS ProjectTask, JB_task_number_summary.badge, A.Job, A.job_category, JB_task_number_summary.Total_hours
FROM [JB_job_categories_M-W] AS A INNER JOIN JB_task_number_summary ON (A.badge = JB_task_number_summary.badge) AND (A.emptype = JB_task_number_summary.emptype)
WHERE (((A.eff_date)=(SELECT MAX(C.eff_date)
FROM [JB_job_categories_M-W] AS C
WHERE C.badge = A.badge
AND C.eff_date<=JB_task_number_summary.Date)));

Here is the second query:

TRANSFORM Sum(JB_time_query_level1.Total_hours) AS SumOfTotal_hours
SELECT JB_time_query_level1.year, JB_time_query_level1.month, JB_time_query_level1.Date, JB_time_query_level1.WBScode, JB_time_query_level1.ProjectNumber, JB_time_query_level1.ProjectTask
FROM JB_time_query_level1
GROUP BY JB_time_query_level1.year, JB_time_query_level1.month, JB_time_query_level1.Date, JB_time_query_level1.WBScode, JB_time_query_level1.ProjectNumber, JB_time_query_level1.ProjectTask
PIVOT JB_time_query_level1.Job;

This results in the "Access does not recognize (such and such) field..." error. So I attempt to explicitly declare my parameters in either query by adding this code:

PARAMETERS A.badge Text ( 255 ), JB_task_number_summary.Date Text ( 255 );

The problem is that the query then prompts me for parameters 'A.badge' and 'JB_task_number_summary.Date', which I don't want it to do. The parameters are for linking fields, not for prompting input.

Any help in this will be greatly appreciated.

 

[lameid]

Most likely the field you are pivoting on conains a null or error that is causing the whole thing to bomb out. That said look for it in the output of your base query.

Other than that, I've seen Jet not play nice with subqueries, so you might change your base query to have another query under it instead of the sub query. Instead of limiting to results of the subquery, inner join the query.

SELECT C.badge, MAX(C.eff_date)
FROM [JB_job_categories_M-W] AS C INNER JOIN JB_task_number_summary ON (A.badge = JB_task_number_summary.badge) AND (A.emptype = JB_task_number_summary.emptype)
Group By C.badge
Where C.eff_date<=JB_task_number_summary.Date

You might also have to add the field emptype to the above first base query... Hard to say for sure without knowing how it works.