Access functions

[noetsi]

This is a two part question. I am new to Access SQL having used PROC SQL
(SAS) historically.

1) Does anyone know where I can find a site that has a comprehensive list
(or a book) for ACCESS functions? I have tried without success to find this
including the MS site.

2) I was hoping someone can explain why something works in ACCESS that does
not work with other SQL I have used. If you have a set of fields and set them
all to Group By (without creating a summary function at all) ACCESS still
runs a GROUP BY function - including eliminating duplicate records. PROC SQL
in contrast, converts it to an ORDER BY because there is no summary function,
simply sorting the data.

I have looked at the SQL code for ACCESS when it does this, and sure don't
see any summary function. My book on ACCESS says flat out it won't run GROUP
BY without a summary function, but it apparently does.

 

[Allen Browne]

Best resource is the one built-into Access, but (depending on your version
of Access) you may need to open the code window to get it.

Press Ctrl+G to open the Immediate Window.
Then go to the Help menu, and look in the index for the JET SQL reference.

To answer your specific questions:
1. Queries in Access can use most of the functions built into the VBA
library, such as DateDiff() or CLng(). It's best not to do this if JET SQL
has handles it natively. For example, you really don't need to call the VBA
function IsNull(), because JET can natively use Is Null.

2. A VBA function will work in Access, but not in other programs that can't
call VBA. There are also other non-standard aspects in Microsoft SQL that
don't work elsewhere, such as the TOP predicate. Similarly there are some
other implementations of SQL that are not supported in JET SQL.

3. Not sure what context you are referring to n your comments about the
GROUP BY clause. Access normally uses a GROUP BY clause when you depress the
Total button on the toolbar. However, there are cases where it doesn't, e.g.
if you just take one field in a Totals query, and use Sum on the Total row,
Access will generate a SQL statement that uses Sum() around the field but
does not include a GROUP By clause.

Hope that's of some use.

 

[Jerry Whittle]

For Access 2003 the Microsoft Jet database engine SQL is generally ANSI -89
compliant. PROC SQL could be based on another standard such as ANSI-92.

There's more about it at:

http://office.microsoft.com/en-us/access/HP010322501033.aspx