From SQL to QBE and Reporting Wizardry

1
From SQL to QBE and Reporting Wizardry

• Coaxing and presenting business information from
  databases

2
Advanced Reporting in Access

• For complex reports, often several sequential
  queries are needed to create the record source
  (either as a query or table)
• Complex reports often consist of an empty main
  report and several embedded sub-reports
• EXAMPLE The Magic One Pager

3
The Magic One Pager
Three subreports, each required many queries to
create record source.
4
Basic Strategy for Complex Access Reports

• WORK BACKWARDS - Manually create the final
  tables or queries needed so that the report can
  be based on it
• Sometimes I use Excel to mock up final report
• Figure out what series of queries are needed to
  arrive at the final table(s) or query(s)
• Build the component queries
• Automate the series of queries if necessary
• Macros
• VBA
• Stored queries
• SQL in code (well start learning this next week)

5
The Problem Analyzing Call Center Data

• Call centers with Automatic Call Distributors
  generate tons of data
• This fictitious call center provides support for
  MS Office questions
• We have a small sample of data from the ACD
  system
• its in a text file
• want to get it into Access
• Once in Access we want to build some queries and
  reports
• do data transformations (very common)
• create queries to drive reports
• create sequence of queries to drive more
  complicated report
• want to understand how to use banded report
  writers
• Instead of SQL, well use Accesss QBE tool
• Toggle into SQL mode after building queries so
  you see link between SQL and QBE

6
Preliminary exploration of the data

• call-log.txt open it up in Wordpad
• What do you notice?
• overall structure?
• field names?
• delimiters?
• use of quotes?
• potentially troublesome field values?
• how much data? Implications?
• Look for exploitable structure

7
Importing text data

• Lets do Excel and Access
• Text Import Wizard similar for both
• Delimited vs. fixed width
• Field headings?
• Data types
• Destination

8
Action Queries

• Make Table yep, it makes tables from other
  tables
• Delete deletes records
• Always preview your delete queries by toggling
  to datasheet view (it doesnt actually run the
  query but instead shows you what will happen)
• Update changes values in a field
• Well use this to fix the TxtServiceTimeMins
  field
• Append adds records to a table
• Useful when youre adding records in bulk to a
  table

9
Transforming data with Update Queries

• Often need to transform data imported from
  external files before doing analysis
• see field TxtServiceTimeMins
• make changes to make it more convenient to do
  analysis
• Update Query allows you to make bulk changes to
  many records
• Be careful! Preview the query in Datasheet View
  before actually executing the query
• Can use many types of general expressions to make
  complex data transformations
• Lets see how to deal with TxtServiceTimeMins
• goal?
• new field?
• how to create the Update Query?

10
Creating the Update Query

• Create a new field to hold the numeric version of
  TxtServiceTimeMins
• why a separate field? Data type?
• Problem 15m25s ? 15.42 minutes
• first version is text, second is numeric
• strategy do it in small pieces and then put the
  pieces together
• FullMins, FullSeconds, FracMins,
  NewServiceTimeMins
• Do as a Select Query until you get it right, then
  make it an Update Query
• useful functions Left(), Mid(), Val()

11
qupdServiceTimeMins
Use Expression Builder when creating long,
complicated expressions.
12
Step back now and examine the big picture

• Want to import this data into a table so we can
  do queries and reports
• We might end up doing this a bunch of times
• Eventually want a table that just has the numeric
  version of the service time mins field
• Lets think about the sequence of steps to move
  from call-log.txt to tblCallLog
• Then, lets use macros to do some simple
  programming
• Eventually we might need VBA to totally
  generalize and automate the procedure

13
Import call-log.txt

• Why import the text file into a predefined
  utility table?
• What do we need to ensure before we do such an
  import? How can we do that?
• How can we have this process execute
  automatically using macros?
• Lets build the macros and see what other issues
  arise
• Warnings
• Make Table when tables exists
• Generalizing the input file

utility import shell
Update ServiceTimeMins field
Make new table and exclude the text version of
the service time field (12m34s)
14
The Append Query

• Useful for adding records to a table
• It is an SQL INSERT
• Lets add tblCallLog-2001_2914 to tblCallLog
• qappCallLog-2001_2914

15
Aggregate QueriesGROUP BY

• These are the key to analysis
• Must display Totals in grid
• Functions that aggregate or summarize many
  records into less records
• SUM(), AVG(), MIN(), MAX(), many others
• GROUP BY lets you do it by category
• Ex count customers by state

16
Ready to do some analysis with aggregate queries

• tblCallLog has 2914 phone call records in it
• Lets start with some basic analysis
• How many calls did each customer make?
• What is the average service time (in seconds) by
  application by problem type? Also include
  standard deviation, min and max. Include counts
  too.
• What is the average on hold time?
• How many customers were on hold longer than one
  minute?
• Over what date range is the dataset?
• Now onto some more difficult questions

17
A little more challenging

• How many calls were there by date?
• How many total calls by day of week?
• How many total calls by hour of day?
• How many by hour of day and day of week?
• Select the calls that were either installation
  related or were for the K Services company?
• What is the average service time for calls that
  are advanced use of either MS Excel or MS Access?

18
Or
1.
easier
tricky
3.
2.
19
4.
5.
20
Use the And and Or logical operators

• When you need to use multiple conditions for a
  query, you can use the logical operators to
  combine conditions.
• The And logical operator specifies that both
  conditions must be met
• The Or logical operator specifies that one or the
  other of the conditions must be met
• When you enter two conditions on the same row of
  the query design grid, an And condition is
  created.
• If you enter two conditions that are on separate
  rows, an Or condition is created.

21
Illustration of And and Or logic
The And condition.
The Or condition.
22
5. Mixing AND and OR
WHERE (tblCallLog.ProbName Like "Adv" AND
tblCallLog.AppName"Excel") OR (tblCallLog.ProbNam
e Like "Adv" AND tblCallLog.AppName"Access")
YES
WHERE (tblCallLog.ProbName Like "Adv" AND
tblCallLog.AppName)"Excel") OR
tblCallLog.AppName)"Access"
NO
23
Perform calculations in a query using calculated
fields and expressions

• Queries can be built to perform a calculation as
  part of the query (e.g. converting minutes to
  seconds)
• Expressions can be entered into the query design
  grid.
• Make certain that you are following the rules of
  precedence.
• To perform a calculation in a query, you must add
  a calculated field to the query design.
• You have three options for entering expressions
• Enter the expression directly into the field text
  box
• Enter the expression in the Zoom box (Shift-F2)
• Enter the expression in the Expression Builder
• Example Lets find the percentage of calls that
  had service times gt 5 minutes using the IIF()
  function

24
The Expression Builder dialog box
The Expression Builder allows you to create
expressions by clicking fields and operators from
list boxes and buttons.
Click a button to choose an operator.
Choose an Access object from this pane.
Choose a field for the selected object from this
pane.
25
Crosstab QueriesComplex but Powerful

• A pre-cursor to Excel Pivot Tables
• Spins values in a field into column headings
  (i.e. fields)
• Access has Crosstab Query Wizard
• Example Count calls by customer by Application

26
Basing a Query on a Query

• Access lets you build queries upon queries upon
  queries
• Often useful when building complex nested queries
• Example Using the crosstab we just created
• Lets find the percentage of calls related to
  Access for each customer

27
Queries as Basis for Forms and Reports

• Record source for a Form or a Report can be a
  table or a query
• Useful for displaying calculated values
• Useful for restricting records user can navigate
  (on Forms)
• Really useful for reporting on ad-hoc analyses
• Create aggregate query first to do the analysis
• Then base a report on that query
• Example report based on our Crosstab query

28
Basic Reporting

• You did some basic reporting in ATB 306
• Banded Report Writer
• See Access-2002-AdvReports.ppt. It uses
  Northeast-AdvFormsReports.mdb. Both available on
  ATB 307 course web
• Feel free to ask me questions if you get into
  creating reports in your projects

29
Basic Strategy for Complex Access Reports

• WORK BACKWARDS - Manually create the final
  table or query needed so that the report can be
  based on it
• Sometimes I use Excel to mock up final report
• Figure out what series of queries are needed to
  arrive at the final table or query
• Build the component queries
• Automate the series of queries if necessary
• Macros
• VBA
• Stored queries
• SQL in code (well start learning this next week)

30
An Advanced Report

• The Call Center fact table contains 10 days of
  call data (10/3/99-10/12/99)
• We want to create a form that allows the user to
  choose a starting date and ending date for the
  report. However, lets start by ignoring this
  complication and just do it for the whole fact
  table.
• The report should include
• A table listing the number of calls by customer
  by application type for that time period
• A table or bar chart (tricky) showing the average
  number of calls by day of week over the time
  period specified

31

• Main report has no record source
• Hard coded dates right now
• This subreport is based on a crosstab query.
  Problems?
• This subreport is based on an aggregate query.
• Queries currently have no date criteria

32
A Series of Queries to Fix Nulls in Top Subreport

• Create a make table query based on crosstab query
  tblSubReport_CustApp
• Create series of update queries to fill in any
  nulls in tblSubReport_CustApp with zeroes
• Base subreport on tblSubReport_CustApp
• Create macro or use VBA to automate the above
  sequence of queries
• Run macro or VBA from a Form

33
ATiB 307 Advanced Reporting Assignment
Basic Requirements

• Finish the report to the shown two slides back
  using the call log data
• As shown in class, youll need two subreports
• The first subreport will require a series of
  queries to create its record source. Make sure
  all null values get updated with zeroes.
• Create a simple Form with a button that launches
  a macor that runs the queries needed for the
  subreports and then displays the Report in Print
  Preview mode.
• Notice that this report is for the all the data,
  i.e. it does NOT do any date restrictions. Thats
  OK for the Basic Requirements.

Extra Credit

• Figure out how you could let the user enter a
  beginning date and ending date for the report on
  the Form and (easy)
• Then have your report report the same data but
  only for that date range. (moderately difficult)
• Modify the second subreport so that it also
  reports the average number of calls by day of
  week (very hard, worthy of Wizard status). Be
  careful about the sample size for the averages!