aurion
asked on
Column alias
Is it is possible to use data from one table as a column alias. Lets say I have a table Products with columns Id, Name Dim and DimText. I want to do something like this..
---
SELECT Name, Dim as [Products.DimText]
FROM Products
WHERE Id=2
---
This is not exactly what I want to do, but it illustrates the problem I have. I want to use the value stored in DimText as the column alias for the column Dim. Is this possible to do with SQL or do I have to do it using VBA?
/ aurion
---
SELECT Name, Dim as [Products.DimText]
FROM Products
WHERE Id=2
---
This is not exactly what I want to do, but it illustrates the problem I have. I want to use the value stored in DimText as the column alias for the column Dim. Is this possible to do with SQL or do I have to do it using VBA?
/ aurion
Actually, this wouldn't make sense...you give an alias for a column. This alias is given only once for the query. So what would you expect to see there if you have more than one record in your table? A random pick from a Dimtext out of the records selected?
So the answer is no you can't do this, because it doesn't make sense. Maybe you should look into a so-called 'crosstable query' where the contents of a field are used as column headers. But it depends on your needs if this is suitable for you.
Cheers,
Erwin
So the answer is no you can't do this, because it doesn't make sense. Maybe you should look into a so-called 'crosstable query' where the contents of a field are used as column headers. But it depends on your needs if this is suitable for you.
Cheers,
Erwin
Are you trying to update the Dim Text column from Dim column?
If so you can do:
Update Products Set Dim = [Products.DimText]
WHERE Id=2
Update Products Set Dim = [Products.DimText]
WHERE Id=2
Erwin is correct, of course ... think about it ... you're trying to reference an object (your "naming" column) before it's actually created ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cwood-wm-com!
You have confirmed my suspicions that I have to do it using two queries. Also, when I wrote the question I should have explained what I was trying to do a little better. The reason I didn't do this was to focus on the problem I had. Obviously this cauesd some confusion, so I'll explain now instead.
Actually I have two tables (Products and ProductTypes). They are joined together using column 'Type' in the Products table. The 'DimText' column is actually placed in the ProductType table. Modifying the sql in the original question I would have written
---
SELECT a.Name, a.Dim as [b.DimText]
FROM Products AS a JOIN ProductTypes AS b ON a.Type=b.Id
where a.Type=2
---
As you can see, this is a complete different question. Let's say you have a product type 'Pipes'. The DimText property for that type would be 'Diameter', whereas another type could have a different name for the Dim column. Now I want to list these products by type, each of the types having its own alias for the Dim column.
Doing like this, I can list all products of one type on a form, using type specific naming of the Dim column.
Hope this clears it out for you, Thanks again!
/ aurion
You have confirmed my suspicions that I have to do it using two queries. Also, when I wrote the question I should have explained what I was trying to do a little better. The reason I didn't do this was to focus on the problem I had. Obviously this cauesd some confusion, so I'll explain now instead.
Actually I have two tables (Products and ProductTypes). They are joined together using column 'Type' in the Products table. The 'DimText' column is actually placed in the ProductType table. Modifying the sql in the original question I would have written
---
SELECT a.Name, a.Dim as [b.DimText]
FROM Products AS a JOIN ProductTypes AS b ON a.Type=b.Id
where a.Type=2
---
As you can see, this is a complete different question. Let's say you have a product type 'Pipes'. The DimText property for that type would be 'Diameter', whereas another type could have a different name for the Dim column. Now I want to list these products by type, each of the types having its own alias for the Dim column.
Doing like this, I can list all products of one type on a form, using type specific naming of the Dim column.
Hope this clears it out for you, Thanks again!
/ aurion
Nic