John,

ultimately Your solution was great, I made adjustments (due to my lack of
foresight), but bottom line, you answered my call for help (I was truly
stumped), and it worked. Yes, I will lose half the first or last records, but
that doesn't matter.

Here is what I now have:

SELECT A.Run_Point_List_ID, A.Run_No,

A.Run_point_Venue_X2,
A.Run_point_Address_X2,
A.Run_Point_Venue_I,
A.Run_Point_Address_I,
A.Run_Point_Venue_J,
A.Run_Point_Address_J,
A.Run_Point_Venue_K,
A.Run_Point_Address_K,
A.Run_Point_Venue_L,
A.Run_Point_Address_L,
A.Run_Point_Venue_M,
A.Run_Point_Address_M,
A.Run_Point_Venue_N,
A.Run_Point_Address_N,
A.Run_Point_Venue_O,
A.Run_Point_Address_O,
A.Run_Point_Venue_P,
A.Run_Point_Address_P,

B.Run_point_Venue_X1,
B.Run_point_Address_X1,
B.Run_Point_Venue_A,
B.Run_Point_Address_A,
B.Run_Point_Venue_B,
B.Run_Point_Address_B,
B.Run_Point_Venue_C,
B.Run_Point_Address_C,
B.Run_Point_Venue_D,
B.Run_Point_Address_D,
B.Run_Point_Venue_E,
B.Run_Point_Address_E,
B.Run_Point_Venue_F,
B.Run_Point_Address_F,
B.Run_Point_Venue_G,
B.Run_Point_Address_G,
B.Run_Point_Venue_H,
B.Run_Point_Address_H

FROM tbl_Points AS A INNER JOIN tbl_Points AS B
ON B.Run_Point_List_ID=A.Run_Point_List_ID+1;

Which is ok, but what I would realy like if possible is for the 2nd half
fields be renamed to the first half, and vice-versa. so that ultimately
Run_Point_Venue_I becomes Run_Point_Venue_A
Run_Point_Venue_J becomes Run_Point_Venue_B
Run_Point_Venue_K becomes Run_Point_Venue_C
Run_Point_Venue_L becomes Run_Point_Venue_D
and so on...

the reason for this is that I have lots of predefined forms & reports that
used the old/original field orders which is going to take a long time to copy
and refomat, In an Ideal world it would be great to use the same Forms &
Reports and not have to maintain two different copies of each, instead just
perhaps having a button, or some other mechanism to switch the SQL source.

John, (after some sage advice from John W. Vinson, I have changed the table
somewhat and was wondering...

Can you help with this?...

I want to find a similar solution thing to the previous problem, but the key
difference here is that the 18 sets of fields are not seperated into 18
seperate fields, but have been normalized/consolidated into 1 field for Venue
and 1 for Address, but each set is held 'in order' by a [OrderSeq] field that
has a maximum of 18 per set of addresses.

The similar solution being I want to take the last 9 records (of each
OrderSeq) and marry them up with the first 9 records of the next set of 18.

For example:

Run_No 1 (last 9)
Run_No 2 (first 9)
and so on...

My table: (tbl_Points) looks like this:

Run_No    (Master Field)
Point_ID (Primary Key)
OrderSeq (Number) orders the address list sequence)
Run_point_Venue (Text
Run_point_Address (Text)

From a user's point of view, the records are broken down into sets of 18,
denoted by a master field, Run_No. They are essentially ordered by the
[OrderSeq] field.

Sample records go like this:

Run_No    Point_ID    OrderSeq    Run_point_Venue    Run_point_Address
        1       
1    377    2    De Vere Hotel    1 Aldwych
1    377    3    London Shelton Hotel    Chancel Street
1    377    4    Costello Palace Hotel    Lavington Street
1    377    ...18    Hilton Hotel Tower Bridge    Savoy Court
               
2    378    1    Bruebaker Hotel    10 John Adam Street
2    378    2    Parkside Hotel N4    Cheney Road
2    378    3    Elena Hotel    Leicester Square
2    378    ...18    Hilton Tower Bridge Hotel    Savoy Place
               
3    379    1    Celstion Hotel    18 Northumberland Avenue
3    379    2    Pembury Hotel    Drury Lane
3    379    3    Eric Hotel    Lennox Road
3    379    ...18    Holiday Inn Express SE1    Seven Sisters Road