Consolidate Your Access Data
If you use Access on your desktop, you're probably concerned about the size of the
database file. In a previous article, we discussed using
Compact And Repair
to shrink
the size of the database without changing the structure of the database or losing
any data.
In this article, we're going to talk about actually removing data from the database
when individual rows are no longer relevant. For instance, you may run historical
reports showing customer buying trends, but the actual line items on invoices are
just not important. What's more, the details take up a lot of space and a great
deal of computing resources each and every time you want to see consolidated results.
We'll look at three steps in the process of shrinking our Access database:
- Create a summary query
- Push the results into another table holding only summary results
- Delete the source data
Create The Summary
Creating a summary of your data is simple. Select the fields to report just as you
would for a normal SELECT query and then click the Sigma symbol shown below circled
in red.
This will add a row into the query builder called Total and will place the phase
"Group By" into each cell in the row. Each column marked Group By will become a
header, sub-header, sub-sub-header (you get the picture). If you click a row you'll
get the drop down list you see below containing summarizing functions like Sum,
Average, Min and Max. Each column marked with a summarizing function will not become
a header, but data within that header.
What the query will get us is the sum of sales by month and by customer name. Instead
of thousands of rows of data, we should wind up with a few dozen.
Put The Summary Into Another Table
Once you've looked at your data and verified it you're ready to
place it into another table. Open your query again in Design View and look at the
tool bar. Just near the Sigma button you used to create your summary query you'll
see a drop down icon that allows you to change the type of query you're creating.
Click append query and you'll have the option to place the summary data into a table
in the existing database, or, if you prefer, into another database containing only
summary data. Be warned - if the database does not yet exist, you must create it
first and the first time you send summary data to it, use the Make-Table query.
After the database and recieving table exist, you may use Append query to add data
to it.
Delete The Details (carefully!)
Check out your results table and be sure you have the consolidated
data present and accounted for. Now go back to the table containing the details
and decide what gets deleted.
Create a new query that displays the data to be deleted. Use date criteria and any
other criteria (like Customer Name) to be sure that only the data you want to delete
is included. Now click the button you used to turn your summary query into an append
query but this time choose Delete Query. Errr,
do be careful,
there's no undo here. You should do a database backup before running a delete query;
when the data's gone, it's gone!
When the details have been deleted, you'll have to do a Compact And Repair to reclaim
the disk space. You should notice a considerable improvement in any other queries
you run against the table with customer transaction details.
To show historical details, you'll need to write one query that reports the already-summarized
data from one table and
UNION it with
the on-the-fly-summarized results. Even with the additional step, your queries and
associated reports should run much faster.