SQL Wizard | Access Add-in
There are at least four major shortcomings with the built-in Access SQL view window:
1) To bring up the SQL window you have to take several steps.
2) You can't comment out a block of your SQL statement while you are experimenting with/debugging a SQL statement.
3) You can't adjust the font size of your SQL statement.
4) It is difficult or cumbersome to compare two or more SQL statements in the same SQL window.
With this enhanced version of the Access feature,
1) Your SQL statement is just one-click away from your query name.
2) You can comment out all or part of your SQL statement by using two simple tags: "/*" for an opening tag and "*/" for a closing tag. You can comment out as many parts of your SQL as you want as long as you use this pair of tags. This is a great debugging device to identify an invalid part of your SQL statement as well as an excellent tool to experiment with your SQL statement.
3) You can quickly and easily adjust the font size of your query names or SQL statements by double-clicking on the appropriate text box or typing a font size in the text box.
4) You can bring multiple SQL statements into the SQL view area quickly one after another with each statement followed by its query name for easy reference.
5) You can run any type of Access SQL queries whether saved or ad-hoc, DML (select or action queries) or DDL.
6) You can view all the SQL statements of all your queries in your database with a single click in a table. This table has three columns: “Query Name”, “SQL Text”, “Query Type.” Your queries are classified into eight categories: “Select”, “Summary”, “Union”, “Crosstab”, “Update”, “Append”, “Delete”, “DDL”. You can filter the table for the type(s) of query you want or sort the table by query type.
7) You can also look up a table/query name and its columns quickly in one place while building your query (You can copy and paste table/query/field names easily). If your query is based on a single query, the program even writes your SQL statement for you using what you selected for your table and its fields along with the six key words necessary for a select query: “SELECT”,
“FROM”, “WHERE”, “GROUP BY”, “HAVING”, “ORDER BY”, enabling you to keep your typing to a minimum. You can just delete any unnecessary key words before running the query.
This Access add-in is a handy SQL learning tool, enabling you to view, compare, modify, create, and save a SQL statement quickly and easily. It requires Microsoft Access 2000 or higher.
 
Data Change Analyzer | Access Add-in
Once every day/week/month/quarter, you run a query to extract pertinent customer information from your database. You want to compare any two snapshots of your customer data to see what changes took place between the two points in time. For example, you have a list of your customers with their account balances. You want to identify who are your new customers ("New"), who are no longer your customers ("Lost"), and what happened to their account balances if they are existing customers ("Existing").
If your table/query does not include any numerical data, this Access add-in can still come up with a list of three categories of customers, i.e. "New", "Lost", "Existing".
This three-step wizard enables you to get an instant analysis of your data change:
1) You can compare any pair of tables/queries as long as they are comparable (You can select a pair of a table and a query or just two tables/queries.)
2) You can navigate the wizard back and forth to select/reselect your tables/queries the same way you can with those built-in Access query wizards.
3) The add-in can detect any duplicate values in a column.
4) The add-in also can detect whether the tables/queries are identical in structure (i.e., they have the same number of columns arranged in the same order and their names and data types match between the two tables/queries.)
5) You can either run a query or make a table for your report at the end of the process.
This product requires Access 2000 or higher.
Consolidate & Update Wizard | Access Add-in
Scenario A:
You have a list of your customers with balances due (base table). Each month they make their monthly payments (source table).
Some customers may skip their payments while others make one or more payments. Your source table
may include customers not in your base table. Your base table may also have some duplicate records except for the
amount (balance).
You first need to back up both tables and consolidate those multiple payments/records.
Your second task is to update your base table for the payments received with the balances reduced by the payments.
With this Consolidate & Update Wizard, you can accomplish all your tasks with a few mouse clicks.
The add-in will also report to you those unmatched customers who are in your source table but not in the base table,
even if they are automatically added to the base table.
Scenario B:
This scenario is identical to the Scenario A above except that you need to add those monthly payments to the base table.
You can choose one of the two types of operation at the end of this three-step wizard: Add or Subtract.
Scenario C:
You occasionally may need to clean up a table like the base table described above (Scenario A) without having to update the table
with new data. All you have to do is just select the table from the list of tables provided and click the button labeled "Consolidate"
on the first page of the wizard. You will also get the table backed up automatically.
This product requires Access 2000 or higher.
Median Finder | Access Add-in
Are you frequently in a hurry to find a median value for a column in your Access table?
Do you wish there could be such a function in Access?
Do you move your data to Excel so that you can use the Excel Median function?
If you must include all the values in the column for the calculation, the task may not be too hard.
But what if your task involves multiple steps? For example, you may want to apply the function to only unique (distinct) values after substituting zeros for nulls and excluding top five and/or bottom five values. You may have to do a great deal of number crunching in Excel.
This Access add-in provides an easy solution to what could otherwise be a cumbersome and time-consuming process. This three-step wizard enables you to get the job done quickly in a single step in one place.
With this three step wizard:
1) You can select a table and a column for your calculation quickly.
2) You have an option to put an index on the column to speed up the process.
3) You can use one of more than a dozen combinations of criteria to prepare your data for the calculation.
4) You have three ways of dealing with nulls: exclude them, substitute zeros for them, or treat them as values.
5) You get an average value of your data in addition to the median value.
6) You can create a log table/make a log entry to save your calculation along with the criteria used
This product requires Access 2000 or higher.
111+ Power Access Query/SQL Examples
Are you mining all the nuggets out of your goldmine? Are you asking your database questions tough enough to extract all the pertinent information for your business? Do you want to push the envelope and create some esoteric yet powerful queries? Do you find most Access books on the market just scratching the surface? (We picked up where they left off.)
Built-in Access functions, query parameters, derived tables, self-joins, unequal joins, cross joins and correlated subqueries can make synergistic combinations, enabling you to build compact, powerful, and reusable queries.
Take your query building skills to the next level. Use our query examples as teaching materials for an advanced Access class. With this tool in your toolbox, you no longer have to pull your hair out over your knotty Access query problems. Profit from these expert query techniques now.
All our query examples employ advanced techniques based mostly on the tables in the Northwind database which comes with MS Access.This product requires Microsoft Access 2000 or higher.
Self-imposed guidelines followed in constructing the query examples:
1. Do not use user-defined functions (VBA code).
2. Use nested queries and sub-queries sparingly.
3. Introduce as many Access's built-in functions as possible.
4. Go outside the box and exclude run-of-the mill type of queries.
5. Use parameters wherever appropriate.
6. Cover as many types of query as possible.
Note: You cannot open some queries in design view (QBE). You can open them in SQL view only.
Duplicates Finder | Access Add-in
Duplicates Finder is your ultimate tool to remove duplicate records from your table. This add-in is a self-explanatory wizard, which takes you through the process step by step. It is simple and easy to use yet powerful, versatile, and comprehensive enough to be your one-stop resource as well as a real productivity-enhancing tool. It overcomes all the shortcomings of the Access’s Find Duplicates Query Wizard, using 12 advanced query techniques. Duplicates Finder gets the job done quickly and efficiently.
Duplicates Finder requires Microsoft Access 2000 or higher.
Duplicates Filter | Excel Add-in
Duplicates Filter works like Excel’s Advanced Filter. This add-in is your ultimate tool to remove duplicate records from your list. It is simple and easy to use yet powerful, versatile, and comprehensive enough to be your one-stop resource as well as a real productivity-enhancing tool. Duplicates Filter gets the job done quickly and efficiently.
Duplicates Filter works with MS Excel 2000, MS Excel 2002, and MS Excel 2003, but NOT with MS Excel 2007.
Excel Tips & Tricks
In this day and age of free, ubiquitous Excel tips and tricks, you may wonder why bother paying for additional tips and tricks. Well, all tips and tricks are not created equal. We have some fascinating tips and tricks which even Excel gurus will find incredible. We did not just rehash some old tips and tricks. We have an Excel workbook full of examples demonstrating all our tips and techniques accompanied by a ten page notes explaining each tip or trick.
With these powerful, sophisticated, and esoteric tips and tricks, you can boost your office productivity significantly. Find out what you have been missing out on over many years of using Excel.
This product requires Excel 2000 or higher.
Macros Finder | Excel Add-in
Do you need to look up a macro in a hurry from your workbook window (not VBE window)? Do you have many macros scattered over many standard modules? Do you find the Excel’s built-in tool (Tools>Macro>Macros>Macro) inadequate because the dialog box does not list all your macros?
Macros Finder, an Excel add-in, makes your macro search a breeze, enabling you to do your search not only by workbook but also by module. This add-in lists all your sub procedures and function procedures whether they are private or public in scope, whether they require parameters or not, whether they are sub procedures or function procedures as long as they are in a standard module. Macros Finder even picks up your macros in a module labeled “Option Private Module.”
Macros Finder requires Microsoft Excel 2000 or higher.
We offer an unconditional 30-day Money Back Gurantee and free e-mail support! Your order is secured via RegNow SSL Secure Server (see RegNow Security/Privacy statement). You will be able to download the product immediately after ordering!
|
