Storing results in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably a simple question, but I am drawing a blank right now.

I would like to store the results of the sum and count functions in a query
of one table to a seperate table for further manipulation.

How do I do this?
 
In query design view, choose Make Table on the Query menu.
Access asks for the name of the table.
When you run the query, it creates the table.

If you want to add records to an existing table instead of making a new one,
choose Append instead of Make Table.
 
Pat said:
I would like to store the results of the sum and count functions in a query
of one table to a seperate table for further manipulation.
Click to expand...

You probably don't want to create a table for the results :) Use a
derived table in SQL code

Example (Northwind)

SELECT EmployeeOrders.orders_per_employee,
Employees.LastName, Employees.FirstName
FROM Employees INNER JOIN
(
SELECT EmployeeID AS EmployeeID, COUNT(*) AS orders_per_employee
FROM Orders
GROUP BY EmployeeID
) AS EmployeeOrders
ON Employees.EmployeeID = EmployeeOrders.EmployeeID
-- EmployeeOrders is the derived table being used for further
manipulation
 
This is probably a simple question, but I am drawing a blank right now.

I would like to store the results of the sum and count functions in a query
of one table to a seperate table for further manipulation.

How do I do this?
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you want to in fact store the derived data *and change it*, so that
the sums and counts no longer reflect the content of your original
tables, you can use a MakeTable query. Otherwise, simply base your
further manipulations directly on your totals query; you can base a
Query on another Query, just as easily as building it on a Table.

John W. Vinson[MVP]
 
Back
Top