Avoid Sumof, Avgof etc in new table

  • Thread starter Thread starter David
  • Start date Start date
D

David

When creating a table from a make table query- always get the sumof as part
of the new column heading- other than by changing the name of the column in
the first query- can I stop access changing the column name? i.e leave out
the "sumof" but still do the calculation?
 
Not that I'm aware of.

The only way I am aware of is to change the alias of the column in the query.

SELECT GroupID, Sum(yourTable.[SomeField]) as FieldAlias
FROM yourTable

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
When creating a table from a make table query- always get the sumof as part
of the new column heading- other than by changing the name of the column in
the first query- can I stop access changing the column name? i.e leave out
the "sumof" but still do the calculation?
Click to expand...

I'd question whether *either* a MakeTable query OR storing calculated data is
appropriate in any case!

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 as a
calculated field in a Query; you can base reports, forms, etc. on that query.
 
John,

Although I generally agree, there are times when creating the temporary
table, or creating a table to store aggregate data is appropriate.

I worked on an application not to long ago that had a number of reports that
were each based on the same aggregate query (actually this was a series of
queries). This query took several minutes to run, but since the users were
only concerned with the information as of the close of the previous business
day, I was able to significantly improve the time it took the users to
generate these reports by populating an aggregate table first thing each
morning.

--
Dale

email address is invalid
Please reply to newsgroup only.
 
John,

Although I generally agree, there are times when creating the temporary
table, or creating a table to store aggregate data is appropriate.
Click to expand...

Oh, I agree... it *can* be necessary. I tend to scream, kick and struggle as
I'm being dragged to the keyboard to do so (or at least require a
*demonstrated*, not assumed, good reason to violate the principle that
calculations should not be stored).
 
Back
Top