FROM clause error on nested query seeking max date

[ammeek]

Periodically I'm getting an error on a query (Query "A") that looks at one table and pulls the most recent dated transaction for each person in the file. The error did not keep the query from running properly or being opened UNTIL I added an Append query that utilizes the data from Query A. Query A still runs, but I can no longer open Query A and look at the data (as I could before) as it gives me the error message and then closes the query (makes it very difficult to hack in and edit the SQL, too!).

The error message is Syntax Error in FROM Clause.

Query "A" looks like this:

SELECT [Transaction Calc File Master].SSN, Plan, [Plan Title], [Election Year], Transaction, [As of Date], [Current Share Balance]
FROM [Transaction Calc File Master] AS T
INNER JOIN [SELECT SSN, Max([Transaction Calc File Master].[As of Date]) AS Recent
FROM [Transaction Calc File Master]
GROUP BY SSN]. AS T1
ON (T.[As of Date] = T1.Recent) AND (T.SSN = T1.SSN);

I can remove the inner join and no longer get the error, so I suspect the error is occuring in the FROM clause of the imbedded query.

The append query looks like this:

INSERT INTO Transactions ( SSN, Plan, [Election Year], [Previous Share Balance], [As of Date], TransCt, [Transaction] )
SELECT A.SSN, A.Plan, A.[Election Year], A.[Current Share Balance], [Enter Quarterly Date] AS Exp1, 1 AS Exp2, "QV" AS Exp3
FROM A;

Any tips would be appreciated.

 

[Golom]

Change it to

SELECT [Transaction Calc File Master].SSN, Plan, [Plan  Title], [Election Year], Transaction, [As of Date], [Current Share Balance]
FROM [Transaction Calc File Master] AS T 
INNER JOIN [COLOR=red yellow]([/color]SELECT SSN, Max([Transaction Calc File Master].[As of Date]) AS Recent
FROM [Transaction Calc File Master]
 GROUP BY SSN[COLOR=red yellow])[/color] AS T1 
ON (T.[As of Date] = T1.Recent) AND (T.SSN = T1.SSN);

Access "helps you out" by inserting the [red][ ... ].[/red] syntax. If you edit the query and make any changes then you also need to make the change indicated, substituting parentheses for square brackets.

 

[ammeek]

Thanks for the suggestion...tried it. It fixed the error until I went and ran the append query and now the error is back again.

 

[Golom]

Yeah ... that is the problem.
Make this a separate query

SELECT SSN
     , Max([Transaction Calc File Master].[As of Date]) AS Recent
FROM [Transaction Calc File Master]
GROUP BY SSN

and call it (for example) [blue]qryMaxDate[/blue], and then your main query is

SELECT T.SSN
     , Plan
     , [Plan  Title]
     , [Election Year]
     , Transaction
     , [As of Date]
     , [Current Share Balance]

FROM [Transaction Calc File Master] AS T 
     INNER JOIN [blue]qryMaxDate[/blue] AS T1 
     ON (T.[As of Date] = T1.Recent) AND (T.SSN = T1.SSN);