Hello,

Had a search but nowt came up.


This works as expected:
Code:
TRANSFORM Count(ISO_Calls.ID) AS CountOfID
SELECT ISO_Calls.Call_Place
FROM ISO_Calls INNER JOIN ISO_Places ON ISO_Calls.Call_Place = ISO_Places.Call_Name
WHERE (((ISO_Calls.Line_Type)="Line") AND ((ISO_Places.Call_Type)="STORE") AND ((ISO_Calls.Planned_Arrive) Is Not Null) AND ((ISO_Calls.Actual_Arrive) Is Not Null) AND ((ISO_Calls.Date)>=#10/3/2009# And (ISO_Calls.Date)<=#10/10/2009#))
GROUP BY ISO_Calls.Call_Place
PIVOT IIf([Actual_Arrive]-[Planned_Arrive]>0.010416,"LATE",IIf([Actual_Arrive]-[Planned_Arrive]<-0.010416,"EARLY","ONTIME")) In ("EARLY", "ONTIME", "LATE");
However I want to join tables to this info (must be done via SQL as it'll be stored in excel and will query the database).

This works. But is using table names and I'd rather use the full SQL as it'll be stored in Excel and some of the dates will be changes depending on the user preferences.

Code:
SELECT [500_Store_Arrival_Pivot].Call_Place, nz([EARLY])+nz([ONTIME])+nz([LATE]) AS Total, [500_Store_Arrival_Pivot].EARLY, [500_Store_Arrival_Pivot].ONTIME, [500_Store_Arrival_Pivot].LATE, (nz([EARLY])+nz([ONTIME]))/[Total] AS [Percent]
FROM 500_Store_Arrival_Pivot;
So I tried this but get a "Sytax error in FROM Clause" and access highlights the TRANSFORM word.

Code:
SELECT subq.Call_Place, nz([EARLY])+nz([ONTIME])+nz([LATE]) AS Total, subq.EARLY, subq.ONTIME, subq.LATE, (nz([EARLY])+nz([ONTIME]))/[Total] AS [Percent]
FROM (

TRANSFORM Count(ISO_Calls.ID) AS CountVal
SELECT ISO_Calls.Call_Place
FROM ISO_Calls INNER JOIN ISO_Places ON ISO_Calls.Call_Place = ISO_Places.Call_Name
WHERE (((ISO_Calls.Line_Type)="Line") AND ((ISO_Places.Call_Type)="STORE") AND ((ISO_Calls.Planned_Arrive) Is Not Null) AND ((ISO_Calls.Actual_Arrive) Is Not Null) AND ((ISO_Calls.Date)>=#10/3/2009# And (ISO_Calls.Date)<=#10/10/2009#))
GROUP BY ISO_Calls.Call_Place
PIVOT IIf([Actual_Arrive]-[Planned_Arrive]>0.010416,"LATE",IIf([Actual_Arrive]-[Planned_Arrive]<-0.010416,"EARLY","ONTIME")) In ("EARLY", "ONTIME", "LATE")


) As subq;
I could select into a table and then run a query based on that but would rather do it all as one SQL if possible.

Many thanks in advance.
Richard