Renaming Column Header Question

[thenoisydrum]

Hello,
Long time no post......
Right, I have a question. Sometimes when pulling information from a table using a design query we need to rename those unhelpful field names into something more descriptive. To do this we simply type our desired column header name before the actual column header name and add a : and a space.

So 152POINT can be displayed as ROP MAX by doing this -
ROP MAX: 152POINT in the query design.

OK? We're all happy with that.....

I am selecting 3 fields from one table and renaming each one to what I like;
STCODE becomes STCode
REORD_NORM becomes ROP
TQUANTITY becomes Stock

I run the query and it all works handsomely.

I then pull this query into another query (linked by STCode) and it displays the Stock field header as the actual query name that it is pulled from followed by a full stop and then the field name.

So rather than simply saying Stock, it says BR Build5.Stock

Why is that? I have come across this before in other queries. I understand if I was pulling in stock from more than one place it would confuse Access but I am not in this case?
So what is it's problem?

 

[plog]

It does this because it doesn't know your intentions. It plans for the case that you may add another datasource (query or table) that has a field with the exact same name as that field. So it essentially uses the fields whole name: TableName.FieldName

If you want clean, crisp, minimal SQL, then you should write it yourself and not use the query builder screen.

 

[thenoisydrum]

It does this because it doesn't know your intentions. It plans for the case that you may add another datasource (query or table) that has a field with the exact same name as that field. So it essentially uses the fields whole name: TableName.FieldName

If you want clean, crisp, minimal SQL, then you should write it yourself and not use the query builder screen.

But why does it not do the same on the other 2 fields that I brought into the query?

 

[MSAccessRookie]

Hello,
Long time no post......
Right, I have a question. Sometimes when pulling information from a table using a design query we need to rename those unhelpful field names into something more descriptive. To do this we simply type our desired column header name before the actual column header name and add a : and a space.

So 152POINT can be displayed as ROP MAX by doing this -
ROP MAX: 152POINT in the query design.

OK? We're all happy with that.....

I am selecting 3 fields from one table and renaming each one to what I like;
STCODE becomes STCode
REORD_NORM becomes ROP
TQUANTITY becomes Stock

I run the query and it all works handsomely.

I then pull this query into another query (linked by STCode) and it displays the Stock field header as the actual query name that it is pulled from followed by a full stop and then the field name.

So rather than simply saying Stock, it says BR Build5.Stock

Why is that? I have come across this before in other queries. I understand if I was pulling in stock from more than one place it would confuse Access but I am not in this case?
So what is it's problem?

In the Second Query, is your First Query joined to another Query (or Table) that also has a Field named Stock? If so, Access will let the First Field that it encounters retain its original name, and any others with the same name will be renamed to include the Table or Query that contains them. Let us know more if this is not the case.

-- Rookie

 

[thenoisydrum]

In the Second Query, is your First Query joined to another Query (or Table) that also has a Field named Stock? If so, Access will let the First Field that it encounters retain its original name, and any others with the same name will be renamed to include the Table or Query that contains them. Let us know more if this is not the case.

-- Rookie

There's actually more like 6 queries but same theory still applies. I understand what you are saying but there is no 'earlier' use of a field called Stock. In fact the actual field is called TQUANTITY and I simply rename it as stock in the final query.
I can however tell you that there are fields in an earlier query pulled from a table called Stock - could that be it?

I can't rename the stock table as that is a live link to a system table called stock. I have however got around the problem by renaming TQUANTITY as Stk.

Thanks for your help