Jet Engine erases whole chunks of select statement (1 Viewer)

[Margarita]

Help! Something wacky is going on with the query sql editor. I write a query, save it, run it. It runs fine (doesn't give me the results that I expect, but that's because I'm probably doing something wrong with the functions I'm using- but that's my own problem and I know it). Then, if I close the database and later reopen it and come back to edit the query I get an error saying that it can't find the table '...' (part of the FROM clause here). When I open in SQL view, I see that the internal Access sql editor, or whatever this evil function is called, has put square brackets and a semicolon where I had parentheses brackets- and it ERASED a whole chunk of the select statement in my subquery!!!

Can anyone help me to understand why this is happening? I don't think my database is corrupt- I am in the designing stages and am the only one accessing it, and I barely have any data in it. I ran a Office repair from Control Panel, but that's not helping. I still get the error on reopening the database. (Note: the error doesn't always occur when I just close the database- sometimes my changes stick and Access doesn't mess with my query syntax when I close the application- but it ALWAYS happens after I restart the computer).

The sql is below. I would really appreciate any feedback on this. Thank you!

My original query:

SELECT V.VendorName, Sum(V.CurrentBudget) AS SumOfCurrentBudget, [SpentCurrentFY - ClosedCurrentFY] AS OpenCurrentFY, [SpentPrev - ClosedPrev] AS OpenPrev
FROM 
(SELECT VendorName, sum(Current_FY_Budget) AS CurrentBudget FROM Vendors GROUP BY VendorName) AS V 
INNER JOIN 
(SELECT VendorName, 
sum(IIf(BHSRequisitionNum Like '2012*',POAmountActual,0)) AS SpentCurrentFY, 
sum(IIf(BHSRequisitionNum Like '2012*',ClosedAmount,0)) AS ClosedCurrentFY, 
sum(IIf(BHSRequisitionNum Not Like '2012*',POAmountActual,0)) AS SpentPrev 
FROM PurchaseOrders group by VendorName) AS P 
ON V.VendorName=P.VendorName

GROUP BY V.VendorName;

This is what Access changes it to:

SELECT V.VendorName, Sum(V.CurrentBudget) AS SumOfCurrentBudget, [SpentCurrentFY - ClosedCurrentFY] AS OpenCurrentFY, [SpentPrev - ClosedPrev] AS OpenPrev
FROM [SELECT VendorName, sum(Current_FY_Budget) AS CurrentBudget FROM Vendors GROUP BY VendorName;] AS V INNER JOIN [SELECT VendorName, sum(IIf(BHSRequisitionNum Like '2012*',POAmountActual,0)) AS SpentCurrentFY, sum(IIf(BHSRequisitionNum Like '2012*',ClosedAmount,0)) AS ClosedCurrentFY, sum(IIf(BHSRequisitionNum Not Like '2012*',POAmountActual,0)) AS SpentPrev FROM Pu]. AS P ON V.VendorName=P.VendorName
GROUP BY V.VendorName;

 

[MSAccessRookie]

I have had a similar experience using Sub-Queries like you have in your example. I have never entirely resolved the issue, and have taken to creating Sub-Queries as separate Queries in Access and using them as Data Source for additional Queries. Using your example, it would be something like the Following (Note that the Field ClosedPrev was not defined in either Query and therefore was not assigned a Tag):

Main Query:

SELECT TableV.VendorName, Sum(TableV.CurrentBudget) AS SumOfCurrentBudget, [TableP.SpentCurrentFY TableP.ClosedCurrentFY] AS OpenCurrentFY, [TableP.SpentPrev -ClosedPrev] AS OpenPrev FROM TableV INNER JOIN TableP ON TableV.VendorName = TableP.VendorName
GROUP BY TableV.VendorName;

TableV Query

SELECT VendorName, sum(Current_FY_Budget) AS CurrentBudget FROM Vendors GROUP BY VendorName;

TableP Query

SELECT VendorName, sum(IIf(BHSRequisitionNum Like '2012*',POAmountActual,0)) AS SpentCurrentFY, sum(IIf(BHSRequisitionNum Like '2012*',ClosedAmount,0)) AS ClosedCurrentFY, sum(IIf(BHSRequisitionNum Not Like '2012*',POAmountActual,0)) AS SpentPrev FROM PurchaseOrders GROUP BY VendorName;

 

[Margarita]

Thank you for your reply. I have resolved the syntax issue in a somewhat similar manner. However, the issue with code being randomly erased was caused by a network problem associated with my account- the IT folks are checking up on that. It seems that it had little to do with the application itself.

 

[vbaInet]

Actually, without looking at what the Jet engine re-write you pasted here, I attempted to re-write your sql in notepad and ended up with this:

SELECT V.VendorName Sum(V.CurrentBudget) AS SumOfCurrentBudget, [SpentCurrentFY - ClosedCurrentFY] AS OpenCurrentFY, [SpentPrev - ClosedPrev] AS OpenPrev
FROM Vendors AS V INNER JOIN PurchaseOrders AS P ON V.VendorName = P.VendorName
GROUP BY V.VendorName;

Just shows that all the Summing you're performing in the subqueries were just redundant. In fact the subqueries themselves were not needed.