SQL Syntax (1 Viewer)

[dapfeifer]

Hey all,

Is the following statement syntactically correct?

SELECT tblFD.Payor_Code AS [Payor Code], tblFD.Payor_Name AS [Payor Name], tblFD.SRCol AS [S/R], Sum(tblFD.Inbnd_Bytes) AS [Inbound Bytes], Sum(tblFD.Otbnd_Bytes) AS [Outbound Bytes], Sum(tblFD.Bytes) AS Bytes, Sum(tblFD.Billable_Bytes) AS [Billable Bytes], Sum(tblFD.Amount) AS Amount, Year(tblFD.Date_Entered) AS [Year]
FROM tblFinalData AS tblFD, [B](SELECT Max(Date_Entered) as maxDate FROM tblFinalData)[/B] [B]AS maxResults[/B]
WHERE (((tblFD.SRCol) Is Not Null) And ((tblFD.Date_Entered) Between DateSerial(Year(maxResults.maxDate)-1,1,1) And Year(Now())-1))
GROUP BY tblFD.Payor_Code, tblFD.Payor_Name, tblFD.SRCol, Year(tblFD.Date_Entered);

I don't have a problem with the query loading in the above form, but access kindly decides that it needs to be changed to this form:

SELECT tblFD.Payor_Code AS [Payor Code], tblFD.Payor_Name AS [Payor Name], tblFD.SRCol AS [S/R], Sum(tblFD.Inbnd_Bytes) AS [Inbound Bytes], Sum(tblFD.Otbnd_Bytes) AS [Outbound Bytes], Sum(tblFD.Bytes) AS Bytes, Sum(tblFD.Billable_Bytes) AS [Billable Bytes], Sum(tblFD.Amount) AS Amount, Year(tblFD.Date_Entered) AS [Year]
FROM tblFinalData AS tblFD, [B][SELECT Max(Date_Entered) as maxDate FROM tblFinalData][/B] [B]AS maxResults[/B]
WHERE (((tblFD.SRCol) Is Not Null) And ((tblFD.Date_Entered) Between DateSerial(Year(maxResults.maxDate)-1,1,1) And Year(Now())-1))
GROUP BY tblFD.Payor_Code, tblFD.Payor_Name, tblFD.SRCol, Year(tblFD.Date_Entered);

Then when I go to use the query in a form or report access throws an error regarding the SELECT statment inside of the from statement. I've bolded the offensive statements. Any ideas to correct the issue?

 

[georgedwilkinson]

Once you get it working in SQL view, don't go back into design view...it'll hose up your nice working SQL.

 

[MSAccessRookie]

I find that MS Access 2003 does not like queries formatted that way. I imagine that other versions act the same way. One way for you to resolve this (perhaps not what you wanted) would be to create a new query (called qrymaxDate?) that contains the following:

[COLOR=black][FONT=Courier New][B]SELECT Max(Date_Entered) as maxDate FROM tblFinalData;[/B][/FONT][/COLOR]

That would make your query:

[FONT=Courier New][COLOR=black][FONT=Courier New][COLOR=black][COLOR=black][FONT=Courier New]SELECT tblFD.Payor_Code AS [Payor Code],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]tblFD.Payor_Name AS [Payor Name],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]tblFD.SRCol AS [S/R],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Sum(tblFD.Inbnd_Bytes) AS [Inbound Bytes],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Sum(tblFD.Otbnd_Bytes) AS [Outbound Bytes],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Sum(tblFD.Bytes) AS Bytes,[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Sum(tblFD.Billable_Bytes) AS [Billable Bytes],[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Sum(tblFD.Amount) AS Amount,[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Year(tblFD.Date_Entered) AS [Year][/FONT][/COLOR]
[COLOR=black][FONT=Courier New]FROM tblFinalData AS tblFD, [COLOR=red][B]qrymaxDate AS maxResults[/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Courier New]WHERE (((tblFD.SRCol) Is Not Null) And ((tblFD.Date_Entered) Between[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   DateSerial(Year(maxResults.maxDate)-1,1,1) And Year(Now())-1))[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]GROUP BY tblFD.Payor_Code,[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]tblFD.Payor_Name,[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]tblFD.SRCol,[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Year(tblFD.Date_Entered);[/FONT][/COLOR]
[/COLOR][/FONT][/COLOR][/FONT]

 

[dapfeifer]

I find that MS Access 2003 does not like queries formatted that way. I imagine that other versions act the same way.

Yes, I'm using Access XP and it seems that it doesn't like it either. Thank you both for the help. If I can't seem to remember to not go back to design view I will try AccessRookie's technique.

Thanks again!

 

[georgedwilkinson]

Yes, that is a good technique.

The cool thing about design mode is that if you saved the query while in SQL mode and later open it in "design" mode, it will re-open in SQL mode.