A Building Block Approach to Ad Hoc Queries

 by Bruce Knox  bknox @t uaex.edu     Oracle Database Programmer/Analyst                                                                   date last updated 02/13/2014

BannerAccess is a building block approach to simplify ad hoc query building (using pre-defined MS Access SQL pass-through queries to reduce the Banner learning curve for new users).

This page outlines a simple to use building block approach to using MS Access as an ad hoc reporting tool for Banner.  Pre-defined MS Access SQL Pass-Through Queries are used to simplify reporting for novice users. 


New users create useful MS Access and Excel reports within minutes of introduction to BannerAccess’ Building Blocks.  This is a very comprehensive solution presented in very simple to use terms. The complexity of Banner is hidden from the novice but not obscured from the power user.

1.  The objective here is to have all of the Banner Tables and Object Access Views you will need immediately available and quickly useful for reporting. 

2. The complexity of Banner is hidden from the novice while full detail of the underlying Oracle SQL code is immediately available to the power user.

3. New users create useful MS Access and Excel reports within minutes of introduction to BannerAccess’ Building Blocks approach.

4. We have about 200 Queries Pre-Defined as Building Blocks. Most of these are single table SQL Pass-Though Queries.  They appear to be very simple and they can be for the end user that does not need to know what lies within.

5. The 200 Table Queries include every table we have used in our programming for nearly five years - over 300 IT written programs. 200 tables is less than a 6th of the difficult to use long lists of tables given to MS Access by using Link Table. We do not Link to Banner; We connect via SQL Pass-Through Only.

6. I have created new SQL Pass-Thought Queries in five minutes from tables I had never seen, but more typically, it takes 10-30 minutes to do it right, start to finish. Views are the same unless you want to give them all of the source table field names. (This can be very enlightening.)  You can use the BannerAccess approach without compromising your systems environment or your application results.

MS Access - Pass through Queries will tell you how to quickly setup this environment.


This is a personal site maintained by Bruce Knox ( bknox at uaex.edu ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects. 

Use this information and these scripts at your own risk.
As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

Any University of Arkansas Division of Agriculture website Disclaimer terms found in conflict with terms of this disclaimer shall over ride and replace the conflicting terms found herein.

Most of the works of art on my pages
Book Dragon copyright 2006 J. Wilson Spenceare used by permission of J. Wilson Spence.

http://betwinx.com is my personal site.  betwinx?  This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from Ellucian's Banner product. 
(I maintain a number of special topic pages with links from this primary site.)                                 
View Bruce Knox's profile on LinkedIn  

Near mirror at http://www.uaex.edu/bknox/ which is my university work site.


Copyright © 2009, 2014 Bruce Knox