Take out SumOFMyFieldName from Query

T

TKM

I have a few queries I am exporting to Excel. Is there a way I can automate
or perminatly change the the field name to only show the field name and not
the SUMOF portion that is at the beginning of my calculated fields? If so how
would I go about this?
 
J

Jerry Whittle

Three ways:

1. Open the query in design view. Change "MyFieldName" to what you want to
see like "MyFieldNames: MyFieldName". Notice that it needs to be slightly
different than the actual field name. Also the colon ( : ) needs to be
between what you want to see exported and the actual field name.

2. While in query design mode right click on the MyFieldName column. Bring
up Properties. Put what you want to see in the Caption properly. I don't like
this one as it can cause problems troubleshooting problems later one.

3. In SQL view change Sum(MyFieldName) AS SumOFMyFieldName to
Sum(MyFieldName) AS MyFieldNames. Actually this is very similar to #1.
 
J

John Spencer

Interestingly enough, if you have both the table and field name in the
aggregate function you can set the alias to the field name.
(Tested in Access 2000)

In the query grid, try
Field: MyFieldName: MyFieldName
Table: MyTableName
Total: Sum

This SQL statement works
SELECT Sum(MyTableName.MyFieldName) as MyFieldName
FROM MyTableName

This SQL errors with a circular reference
SELECT Sum(MyFieldName) as MyFieldName
FROM MyTableName
 
J

Jerry Whittle

Hi John,

Very interesting! Even more interesting is that Oracle doesn't need the
table name. The following runs just fine in Oracle 10g.

select sum(pers_seq_sg) as pers_seq_sg
from pers ;
 
Top