Remove the "Sumof" Prefix from Column Headings

[ppete]

Hello,

I was just wondering is there a way to stop Access prefixing column headings with "Sumof" or "AvgOf" when creating queries that sum / average certain columns.

I know you get around this by (say the field is called "Sales") typing Sales:Sales but I find this a bit tedious.

Do I ask too much? Am I being Mr Pernickety? I just feel that if I've created the query I don't need to be told that a certain column is the "Sumof" Sales or "Avgof" something else. I put the bloody "sum" bit in!!! And I've a good memory!

Sorry about that.

Any help would be appreciated.

~Ppete

 

[TessB]

My dear Pernickety Pete,

It seems to me that you already know how to do this but are unhappy with the solution. I know of no other way, nor can I imagine a simpler solution that the existing one. But I must thank you for making me laugh while thinking about a poor chap whose life in Access is so perfect that a column heading in a query seems bothersome to him. LOL! I suppose there is no name on your mailbox at home either eh? I mean, you bought the house, of COURSE the mailbox is yours too right? LOL. I'm sorry, I know I am giving you a hard time. I wish for you that all your problems are as annoying as this one.

Respectfully,
Tess

 

[ppete]

Hi Tess,

Thanks for the reply. I'm glad you at least can find some comedy in my tragedy. .

I can't believe this doesn't bug other access users though. I suppose I should just resign myself to having to retype the names of fields that are already there. I'll just have to take lots of calming deep breaths while I'm doing so.

In the meantime, I'll see if I can come up with some proper problems for you to ponder...

All the best,

Pete

 

[cogent1]

OI!TESS! NO!

Tess

Don't you dare pick on Pete! This guy is enduring mental agony of a high order. Imagine all the torment of having to retype five or six field names every day! Have you no heart?

And, since you ask, we don't have names on our mailboxes here. We have "letterboxes", a misnomer for holes cut into our front doors into which letters are put. We have numbers on our houses (try it). Or some of us do. Maybe ppete doesn't. Perhaps we could start a new thread on the subject.....

 

[ppete]

All right, all right.

This is me waving the white flag on this one (even though I did have to do the renaming thing on 24 fields once!!!).

I probably shouldn't mention that I find creating forms a bit finicky and that I don't like the way the number format in queries defaults to general rather than standard and that I find it a bit frustrating that there's no way to change a field name throughout a database other than going in and changing every table and query, report and form.

Incidentally, I got a puncture in my car today. (I know, bummer). And when I looked at the spare, I discovered (well I knew really but I was counting on the intervention of some friendly elves) that it is punctured too. I only mention this to illustrate the fact that I'm not that organised a person.

All the best,

Pete

 

[cogent1]

O yes you can! Change field names I mean. Provided you buy a recent copy of Access.... or perhaps you've spent all yer cash on a puncture repair outfit?

 

[TessB]

Let me get this straight.... your kind nation expects their postmen to walk all the way up to the porch, with their heavy letter bags on their shoulders... possibly even have to manage up steps... to reach the door... and then have to attempt to slide handfuls of bills, magazines, sales flyers, catalogues, and parcels through a hole in your door? And then when you come home from work, you open the door and the mail is in your way?

Hmm... I have watched with delight your legislature politely argue points whilst complimenting their opposition. I have heard your countrymen take deliberate care in speaking, making certain that every 'T' is pronounced utterly clear for their listeners. In a nation as kind and considerate as yours, I suppose I am shocked at the hardship you place on your postmen. (Ours are known for stress as well and they don't have to carry to the door!) I would dare say that your postmen would be quite jealous of Pete's arduos daily task of controlling column headings.

By the way, we have numbers on our homes as well.

With deepest humility,
Tess

 

[cogent1]

Tess, your humility does you, and your nation, great credit.

Yes, that is what our postmen have to endure, plus savage dogs and seductive overtures from bored housewives. They wreak revenge by stealing our credit cards and disembowelling our letters. It's not all cakes and ale here...

Glad you took up my suggestion about house numbers....

 

[Rich]

We don't give our postmen "directions" either!

 

[Ally]

And when you live in a block of flats, they have to traipse all the way up several flights of stairs to post our letters etc. Who'd be a post-person eh?

... But our roads aren't generally as long as the 10mile long straits in the US!

 

[TessB]

Yes, I understand you prefer circular "roundabouts" to linear travel....

 

[Rich]

The roundabouts are for us motorcyclists to have fun around the town.

 

[ColinEssex]

Yes Rich - except when the poxy lorry drivers spill diesel all round it.

Col
ps - Honda 650 Deauville - white with 'police' stripes on it, makes the motorists get out the way!!!

 

[Rich]

Nice bike, shame about the plastic though
Tess seems a good laugh, do you think she'd benefit from a visit to a "real" pup to drink real Ale?

 

[ppete]

Sorry to interrupt the motorcycling postman debate but which version of access allows you to change field names through-out a database? I work with Access 2000. Is there any add-ons that I could avail of that would do this for me?

Ps. It appears US postal workers have it easy. They've no excuse to go around murdering people indiscriminately.

~Pete

 

[Rich]

There is a find and replace utility built by Rick Fisher, I don't have the link but I'm sure it's been posted here before. The search facility might find it for you

 

[cogent1]

access xp automatically updates all field references when you change a name.

 

[Lyn Mac]

Access 2000 has this feature. In the Menu Bar go to Tool/Options then click the "General" tab. In the upper right portion of the form there is a rectangle with Name Autocorrect and below that are three check boxes with the first box = Track name autocorrect info.

WARNING !!!

MS has acknowledge this as a BUG. This makes your Access Reports revert to default settings next time you open your report. So USE THIS FEATURE ONLY if you have downloaded the FIX from the MS KnowledgeBase.


HTH,

Lyn

 

[HGMonaro]

Re: My dear Pernickety Pete,

It seems to me that you already know how to do this but are unhappy with the solution. I know of no other way, nor can I imagine a simpler solution that the existing one.

I know this is an old discussion but since this thread popped up 1st in google when I was trying to find if there's a way around this "SumOf" nonsense. I agree with the OP, I don't need to know it's a sum, that's what I specified in the query. And, if I only had to rename 5-6 fields I wouldn't be here looking for an answer. Try doing 20 or 30, it gets tedious real quick! However, a simple setting in the database that stopped adding these prefixes would surely be simple to add and we could all be happy. I'll continue my Year 2014 search... something (hopefully) might have changed since this was asked!

 

[HGMonaro]

and 1min later I find a work-around...

it was copy SQL into editor, do replace "SumOf" with nothing..

copy back

done!

yippeee