PowerSQL Functions for PowerAccess.js

PowerAccess Screenshot - Toolset Add-in,

Shown below are a few of the sample SQL Queries included with the PowerAccess Framework.

These demostrate how uniquely simple yet powerful PowerSQL™-enabled SQL Queries can be in Microsoft Access. This syntax is support by a wide range of functions and convenient overloads (versions accepting different parameters) which provide support for nature language-like and Excel formula-like queries.

You can see with XPrev() + XRow() or the standalone XPrevRow() allow self-referencing queries can perform lookups and sums over themselves - and at blazing fast speeds, due to calculation reordering, relative row reference support, and on-the-fly caching.

Our roadmap includes providing the PowerSQL Framework and PowerExcel Framework to allow use of XLookup, QWhere(), and other X and Q, MaxOf(), XNPV, XNearest, XSumToDate and other powerful, simple functions for use directly in Excel as well as any other application supporting .NET languages like C# and VB.NET or VBA, and from within the SQL Queries themselves, such as from within SQL Server stored procedures and views.

PowerSQL Example
Powerful, Concise & Intuitive

Longer PowerSQL Example

Function Types & Usage

XPrevRow(), XLookup() Functions, QIs/QWhere() Criteria
for Lightning Fast Dynamic Queries as 1st Class Citizens

Even comments are finally supported in Access SQL with XComment() and QComment(), as are parameters passed to queries used for lookups via QParam().

This query syntax is supported in your databases when PowerAccess Framework is embedded into them, so that installation of any kind of add-in is not required.

Also, QWhere() and other Q functions - as well as XRow() - allow for sharing criteria and conditions and groups of fields to group, filter, and sort by, while providing easy support for automatic completion and descriptive parameter and function names. No more treating dynamic lookups as second-class citizens, they are verified when editing the query and support auto-complete. No more quoting values and combining strings and field names with DLookup().

With PowerSQL, there is no longer a need for writing many long, complex, and repetitive subqueries and you can avoid the performance issues of DLookups() in many cases with these optimized alternatives, as well as allow such queries to be used outside of Access from SQL Server, Excel, Power BI and other applications.

QIsBetween(), QIs(), QIsAnyOf(), MaxOf(), and <=, =, and other comparisons all handle null and other exceptional cases in an intelligent and configurable manner without need for many tedious, error-prone null checks everywhere.

Notice how a variable number of field, value criteria pairs can be passed to most X functions and many QWhere, etc. functions. These field, value pairs can even be combined with QIs (QIsBetween, QIsAnyOf(), etc.) and QWhereAny(), QComment() and QParam() function calls - in any order - with intelligent handling, without needing to provide a name, value pair in those cases.

You can even include X

Also, you can even design your queries to use query, filter, group by, etc. parameters which can be passed dynamically - via forms, VBA, or XLookup() calls - with such use of dynamic queries.

New Financial, BI, Math, and Excel Functions for Access

PowerAccess provides new, faster, more powerful dynamic sum and lookup functions like XQTD, XYTD, XSumToDate(), XSum(), and XNearest(), and XNearestAtBeforeOrAfter(), as well as advanced math, financial functions, including those previously only available in Excel, like XNPV.

SQL Queries Simplified, with Powerful, Readable, Excel-like Formulas

PowerAccess results in vastly simplified SQL queries (and Query Designer view), by extending SQL with powerful, plain language functions like XPrevRow(), PositiveOrZero(), OnlyIf(), as can be see in the sample SQL query below:

Excel-to-Access, Simplified

For the first time ever, PowerAccess brings Excel-like Row Formulas to SQL Queries with XPrevRow().

Not only does this vastly simplify creation of queries and databases, it also enables support for truly Iterative (Recursive or Circular) Calculation, where a row (month, date, etc. record) is calculated based on the previous row's value with relative row references. This makes it easy to accomplish many calculations previously impossible in Access SQL and makes it feasible to convert from Excel workbooks to a relational Access database with SQL queries.

This enables a new kind of Excel to Access conversion, where instead of just repeatedly importing your Excel data into Access, with all the calculation done with formulas in Excel, you actually convert those formulas in SQL queries.

Excel-like SQL Formulas: Faster, Simpler, More Versatile

"SQL Formulas" - Excel-like formulas in SQL queries using XPrevRow() - are not only simpler, more powerful, and more versatile than was previously possible in Access, but they they can also be orders of magnitude faster!

Benchmarks show that compared to complex subqueries and dynamic sums and lookups, XPrevRow-based SQL query formulas can be 1259 times faster, reducing the time it takes to execute such queries from 35 seconds to 0.03 seconds - essentially instantaneous! And, as the number of records and fields increases, that performance gap grows exponentially, with queries that took literally days to execute taking minutes or even seconds.

Data Accelerated - On-the-fly Caching & Instant Query Scrolling

Also, PowerAccess provides on-the-fly caching of query results in-memory combined with XPrevRow() speed-ups provides a far more responsive experience in working with Access. Queries which were previously too slow to even scroll through in Datasheet view (vertically, and sometimes even horizontally across all the fields) can now be seen nearly instantaneously - and that's without even the benefit of Calculated Table™ caching, which provides truly instantaneous access.

Use Queries with VBA, VB.NET & C# Functions Anywhere:
From Excel to Power BI

Calculated Tables™, in addition to providing instant access to data, enable even queries which use VBA, VB.NET, or C# functions to be accessible externally Excel, Tableau, Microsoft Power BI, PowerApps, SharePoint, databases, web services, Business intelligence (BI) & Analytics, and all other applications.

Access Web Apps Replacement: SharePoint & PowerApps

Don't let Microsoft's removing support for Access Web Apps prevent you from sharing your data on the web.

PowerAccess enables you to not only automatically cache queries to Calculated Tables™ (when records change, periodically, and on-demand) in Access for use from Excel, Power BI, and more, it also can auto-publish (sync or upload) those Calculated Tables to Microsoft SQL Server, SharePoint Lists and Excel workbooks or reports. These in turn can be used in directly in SharePoint or in Microsoft PowerApps - standalone or hosted in SharePoint.

Finally PowerApps can be used with Access, through PowerAccess' support for Calculated Table Publish & Sync.

Embedded, Not Installed

PowerAccess is embedded into or deployed as a file side-by-side with your own Access database, so that there is no need for install of any kind of add-in.

Focus on Data, Not Code

The PowerAccess Framework is specifically designed to greatly simplify and reduce need for VBA coding and macro creation through automation and providing implementation of common MS Access database application functionality configured via settings.

Save Time with New Tools and Automation

PowerAccess also provides many powerful and time-saving new features to MS Access such as:

Project-wide Search & Replace - across Queries, Data, Definitions and Code
Smart Rename, Smart Delete
Database Refresh Events
Automatic hookup of Data Macros for monitoring data changes
Smart IDs for up to 4x smaller, faster than AutoNumber, with ability to manually change IDs and option to reuse IDs to prevent gaps

New Events & Macros

New Scriptable Events for Macro Designer and VB so that you can focus on business logic instead of lots of boilerplate VBA coding, and to provide easy control of new features and events (like OnExit and OnRefreshDB) previously unavailable in Access. These include:

OnDBRefresh
OnStartup
OnExit
OnConfigure