transpose field titles syntax help needed

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

How can I make this query transpose the Run_From and Run_To field titles.

SELECT DISTINCT B.Run_No, A.Run_From, A.Run_From_Postcode, B.Run_To,
B.Run_To_Postcode
FROM tbl_Runs AS B INNER JOIN tbl_Runs AS A ON A.Run_No=B.Run_No+1;

I have tried to put in an 'AS' clause. eg:

SELECT DISTINCT B.Run_No, A.Run_From AS Run_To, A.Run_From_Postcode,
B.Run_To AS Run_From,...

but it doesn't work, the query works the way I want, except I just want the
field/column headings to change names, not data. So that:

Run No Run_From Run_From_Postocde Run To Run to Postocode

becomes:

Run No Run_To Run_To_Postocde Run From Run From Postocode
 
Using aliases is the way to do this, so the "aliased" query that you posted
should work. Tell us what is not working with respect to the results or
error messages.
 
Hi Ken,

Yes, the alias query I posted does work for the data, but I want to simply
change the Column Names around, but crucialy without changing the data, so
for example:

This query:

SELECT DISTINCT B.Run_No, A.Run_From, A.Run_From_Postcode, B.Run_To,
B.Run_To_Postcode
FROM tbl_Runs AS B INNER JOIN tbl_Runs AS A ON A.Run_No=B.Run_No+1;

Produces this:

Run No Run From Run From Postcode Run To Run To Postcode
1 SOUTH SQUARE N1 JOHNSON SQUARE N1
2 CENTRAL STATION EC1 PALACE SQUARE WC1N
3 LAMB WALK SE1 POLSEN ROAD SE1


But I want it to change column header names; like this (the data stays put,
just the headers change:

Run No Run To Run To Postcode Run From Run From Postcode
1 SOUTH SQUARE N1 JOHNSON SQUARE N1
2 CENTRAL STATION EC1 PALACE SQUARE WC1N
3 LAMB WALK SE1 POLSEN ROAD SE1


I can do it with 2 queries, but was hoping to do it in one query.
 
Forgive me, but I am not understanding what the problem is. Using the
aliases gives you the desired result, no? So is the issue that you want one
query to give you both results? I need more explanation to fully understand
what you are wanting to achieve, sorry.
 
HI Ken,

I can see from your point of view that this seems like I am asking for the
result of my own answer; but in case you have not noticed, if you look again
at the first table headers and the seond table's example, you will see that
the [From Run] and [Run_To] field titles have swapped places with the
[Run_To] and [Postcode_To].

This has only happened because I manually typed the table like this. I want
to be able to do it in the actual query but do not know where/what syntax to
use.

for example, if i make this change to the SQL:

SELECT DISTINCT B.Run_No,
A.Run_From AS Run_To,
B.Run_To AS Run_From
FROM tbl_Runs AS B INNER JOIN tbl_Runs AS A ON A.Run_No=B.Run_No+1;

The 'AS' aliases titles do not change.




If this still makes no sense to you, then forget this request for a moment,
and Imagine (based on the first table) that I wanted the field titles
[Run_From] and [Postcode_From] to say [Apples] and [Skyscraper] instead.

How would I do that in the one query example I posted?.
 
Hi Ken,

Going with your great knowledge, experience and sense that 'nothing was
wrong' with using the alias feature, I figured it must be something else...

So I searched around the queries section and found this:

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Which was the root cause of my problem; which has now been resolved. Thanks
so much for your help Ken, it is much appreciated. (also special mention to
Marsh from the other thread)

regards

Eric
 
Glad you found a solution, although the link that you provided doesn't go to
a specific newsgroup post so I am not sure what the solution is. < smile >

Was this issue related to a caption in a datasheet view?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

..
 
Ken,

Yes it was. search for 'Field aliases not being returned properly in A2007'
in the Query newsgroup.

Duane Hookom supplied the answer. I looked at the field properties in the
table that was feeding my query, and removed the caption descriptions which
cured the problem. Brilliant knowledge you MVP provide, which is greatly
appreciated form this quarter.

thanks

Eric
 
Back
Top