Tutorial for Converting Excel Formulas to Access Database & SQL Queries with PowerAccess for Microsoft Access
Less VBA and SQL with PowerAccess and PowerSQL
PowerAccess for Microsoft Access and PowerSQL for SQL Queries make macro and query creation easy with over 800 powerful, yet easy-to-use functions can use from Macro Designer, VBA, SQL, and Query Designer.
It enables converting (migrating or upscaling) from many slow, complex, difficult-to-maintain Excel workbooks to a single, faster, decentralized Microsoft Access Database by converting Excel formulas to PowerSQL Queries visually editable in the Access Query Designer.
This is possible through PowerAccess's new functions extending with SQL and Query Design with Excel Formula-like syntax and even, for the first time ever, support for simple "Previous Row-based" formulas to define fields (columns).
This is enabled through XPrevRow(), XLookup, QIsBetween() and 800+ other new, more powerful, easier to use and faster functions for Excel & PowerQuery-like query building
Not only does this enable converting Excel calculation workbooks (formulas and all - not just the data) to Access Databases with SQL Queries, but it also makes it much easier and more intuitive to edit queries.
Excel Formulas vs. Calculated Fields in Query Designer or SQL
Though it's easy to define a (relative reference) formula in Excel using relative references to calculate a value based on previous row's values. However, that's been impossible to do in Access Query Design or SQL - until now, with PowerAccess's new XPrevRow() function. Until now, in order to convert something as simple as "Previous Row's Value + This Row's Sales Amount * This Row's Multiplier) in Excel to Access or other database would often require advanced SQL subquery writing. Sometimes this was even impossible to do without PowerAccess' new XPrevRow()-based formula support.
At best, this is extremely complex to do in Access SQL Queries. However, I have often seen cases where it's entirely impossible to convert Excel spreadsheets calculating this to an Access Database (eg. SQL Query), as they requires recursive (iterative or circular) calculation - even with something as simple as capping/limiting, let alone bonus tier/rates/range lookups and the like.
Intro to Queries & Tables for Excel Users
For those only familiar with Excel use, Tables are used to store the data from one or more Spreadsheets with the same column headings - sometimes using Project or Sheet Name field for grouping to avoid having a separate table per project, so some sheets can be combined.
Each Table often has at least one Query - visually editable in Access Query Designer (as well as SQL directly, for those who prefer) which defines what fields to show (and in what order, with what column label), as well as defining "Calculated Fields" for the table, with one field per column you had calculated via a formula in Excel.
Additional Queries can also be created to define alternative Views of your data, like how you can have multiple PivotTables for the same cell range. They can define which fields to sum (or other aggregate functions like Average, Count, etc.), group by, filter by, and sort by. You can also use Queries to combine tables or include lookup fields (via PowerAccess' new XLookup functions - far easier to use and even with in-memory-cache accelerated speedup).
Unlike Excel where you have to maintain many copies of the same formula for every row or cell, and often across multiple sheets (eg. per project / department) and many workbooks (from different users or for different workbook versions), Access with PowerAccess is centralized to make it much easier to edit, add and maintain fields and formulas.
You can use PowerAccess' many functions to avoid lots of repetition in the formulas you define and maintain. You can even define your own functions to use in queries as a kind of "Field Formula Template".
Excel Formulas vs. SQL Queries for Calculated Fields
I've seen a number of Excel workbooks with formulas calculating based on previous row's values which required truly recursive (iterative / circular) calculation, preventing them from being converted at all to an Access database with automatic calculation. For example, if you have Monthly Employee Bonuses calculated based on Total Profit (YTD), where Total Profit in turn is calculated based on Bonuses (and other expenses paid), you have a recursive formula.
This can be done easily in Excel, by having the Monthly Bonuses cell formula with a (relative) reference to Total Profit in the previous (month's) row, and the Total Profit cell formula reference Bonuses in the current row.
However as there aren't really ordered "rows" in Databases - just a single field formula definition (without relative cell references or each cell having its own different formula), you can't do the same in Access. You define 1 formula for a field (not a formula per row/cell), you have to re-sum and recalculate everything again for each row. You can do something like the following in SQL Queries (simplified, as real SQL would be 20x as long and complex):
[Total Profit (YTD)]
= ( Sum(of Sales Amounts from Month 0 to Current Row's Month)
- Sum(of Expenses to Current Month) )
* ( 100% - [Constant Bonus %] )
This works OK for a Constant Bonus Percent (though, is 20x longer and more complex in SQL than shown above, even for just that). It even can work with a Table with a Bonus Percent Hardcoded - though that means calculating Bonus Rates in Excel or another tool and importing them each month (which somewhat defeats the purpose of converting to a Database). Or you can even setup a manual process to calculate and save out the Bonus Amount to a table each month (though requiring manual work and without ability to go back and edit a month's values or the formulas without having to repeat and re-save for each past date manually, incrementally).
However, in reality, you'll probably want to have a Bonus Rate Schedule (eg. a step schedule defining Target Ranges and associated multipliers) and/or Limits (such as Minimum and Maximum Bonus Amounts applied each month, vs. just total for the year).
Iterative or Recursive Calculation - Easy in Excel, Impossible in Access or SQL Until Now
Forecasting or Projection for Future Months it is even more problematic in Access and SQL Queries, often requiring complex recursive/iterative calculation, sometimes without even knowing (or even having in a table) the number of rows or months in advance until Balance Owed goes to 0 or Total Investment or Total Payments reaches the Target. In many cases, there isn't even a workaround for it (beyond an impractical solution like manually running a query over and over again to add the next row to a table until you see Balance going to 0).
However, for the first time ever, powerful, concise, and far more intuitive Excel-like "Previous Row-based" "Row Formulas" can be used in Access to define "Calculated Fields" - both visually in the Query Designer and in SQL with XPrevRow(), XLookup(), XSumToMonth(), XNearestMinOrDefault() and 800+ other new functions that PowerAccess™ provides - a set of functions collectively referred to as PowerSQL™. PowerAccess enabling creating simpler, more concise, powerful "PowerSQL Queries" with intuitive syntax and feature-set similar to Excel Formulas, DAX in Power BI, or Power Query.
Simpler Excel-Formula-like Queries which run up to 1000x faster with PowerAccess & PowerSQL
Not only can XPrevRow()-based queries reduce amount of SQL required (or size of formulas entered into Query Designer) by up to 20x, but also XPrevRow-based Query Formulas can run up to 1000x faster than the alternative: subqueries and dynamic sums/lookups.
This is made possible by PowerAccess' unique support for on-the-fly caching to avoid repeated subquery or dynamic lookup calculation. This can often be exponentially faster as the size of the dataset and number of related fields increases. Instead of waiting days for a query to finish running, in some cases, PowerAccess can accelerate this down to completing in seconds!
Also, where it was previously impossible in many cases to convert from Excel to Access and SQL - such as where iterative (recursive or circular) calculation is required - something as easy as relative-row reference to previous row's cell in Excel - it is now actually possible to achieve with PowerAccess and PowerSQL.