Cross Tab MS Jet * does not * etc 1

[DAVEKELLY]

I have a query that looks at a project milestone table.
The query assigns a value to each Milestone (ie MS 1, MS 2 etc). There are a max of 4 Milestones on each project.

SELECT Table1.Project, Table1.MS_Date, (SELECT "MS " & Count(*)
From Table1 N WHERE N.Project = Table1.Project And n.MS_Date <= Table1.MS_Date) AS MS
FROM Table1;

When I create the coss tab based on above I receive "does not recognize "Table1.Project" message.

TRANSFORM Last(qry_MS_Title.MS_Date) AS LastOfMS_Date
SELECT qry_MS_Title.Project
FROM qry_MS_Title
GROUP BY qry_MS_Title.Project
PIVOT qry_MS_Title.MS;

If I use 'make table query' and then make cross tab from table, it works fine.

I tried inserting parameters before the TRANSFORM - but I am now out of my depth.

Advice much appreciated.

 

[dhookom]

You can't use a query with a subquery like that to generate a crosstab. I think you can use a self join like:

SELECT Table1.Project, Table1.MS_DATE, 
Count(*) as MS
FROM Table1 JOIN Table1 N ON Table1.Project = N.Project
WHERE Table1.MS_Date <= N.MS_Date
GROUP BY Table.Project, Table1.MS_DATE;

Duane
Hook'D on Access
MS Access MVP

 

[DAVEKELLY]

Duane - Thank you so much.
For completeness of this question - the SQL is listed below:
As can be seen I added an INNER JOIN.

SELECT Table1.Project, Table1.MS_Date, "MS " & Count(*) AS MS
FROM Table1 INNER JOIN Table1 AS N ON Table1.Project = N.Project
WHERE (((Table1.MS_Date)<=[N].[MS_Date]))
GROUP BY Table1.Project, Table1.MS_Date
ORDER BY Table1.Project, Table1.MS_Date;

The subsequent Cross Tab code now functions without error.

Best Regards

 

[DAVEKELLY]

As a post note to this - if you are looking at Milestones off projects and want to classify them in date ascending order ie) earliest date MS1, next date to MS2 etc for use in for example 'project traffic light' reports, I changed the WHERE to:

WHERE (((Table1.MS_Date)>=[N].[MS_Date]))


Resultant output from Cross Tab
Project MS1 MS2 MS3
A 20/01/10 15/03/10 20/06/10
B 15/03/10 25/10/10 20/01/11

Hope this might help someone.