Subtotal Queries within

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bran - 09 Nov 2005 17:02 GMT

Somebody surely has run into this - I have a query that runs a report for
open orders based on current date, and identifies Wk/Mo/Qtr. I need to
separate out the values for wk/mo/qtr into separate queries and subtotal,
then write a make-table query that will put subtotal values into the table in
columns. What I cannot figure out how to do is the subtotals in the queries.
This is so simple to do in Excel, it seems it should be fairly simple in
Access - and I don't want to use reports. Can anyone help? I've searched
everywhere I can think of for this and asked all my expert
friends/co-workers. FYI, I'm not familiar with the sequel side, I use the
design tabs to build my queries... :(

Reply to this Message

John Vinson - 09 Nov 2005 18:15 GMT

>Somebody surely has run into this - I have a query that runs a report for
>open orders based on current date, and identifies Wk/Mo/Qtr. I need to
>separate out the values for wk/mo/qtr into separate queries and subtotal,
>then write a make-table query that will put subtotal values into the table in
>columns. What I cannot figure out how to do is the subtotals in the queries.

No. You don't want to store the subtotals at all.

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.

> This is so simple to do in Excel, it seems it should be fairly simple in
>Access - and I don't want to use reports. Can anyone help? I've searched
>everywhere I can think of for this and asked all my expert
>friends/co-workers. FYI, I'm not familiar with the sequel side, I use the
>design tabs to build my queries... :(

Excel is a spreadsheet, a good one. Access is a relational database.
THEY ARE DIFFERENT. You can use Totals queries (use the Greek Sigma
icon on the toolbar) to get your totals, and display the results of
these subtotals on a Form (for onscreen use) or Report (for printing).
It's neither necessary nor beneficial to store the subtotals in a
table, except in very particular and unusual circumstances.

John W. Vinson[MVP]

Reply to this Message

bran - 10 Nov 2005 00:28 GMT

I have a query which runs the data - I want a table that will auto-update to
new data (i.e. next value calculations rather than specifics) as the query is
run. I need "dummies" to be able to run this report and print it on a
spreadsheet.

Does anyone know how to calculate by "change in value"

> >Somebody surely has run into this - I have a query that runs a report for
> >open orders based on current date, and identifies Wk/Mo/Qtr. I need to
[quoted text clipped - 30 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 10 Nov 2005 04:50 GMT

>I have a query which runs the data - I want a table that will auto-update to
>new data (i.e. next value calculations rather than specifics) as the query is
>run. I need "dummies" to be able to run this report and print it on a
>spreadsheet.
>
>Does anyone know how to calculate by "change in value"

It would appear that you are assuming that data must be in a table to
run a report, or to print it on a spreadsheet.

Your assumption is WRONG.

It is NOT necessary to create a new table, or to copy data into a
table, to print it on a report.

A Report can be - and in fact almost always will be - based on a
Query; this Query can contain calculated fields.

If I am misunderstanding your request please explain.

John W. Vinson[MVP]

Reply to this Message