Union fields in the order they appear in the table

E

efandango

I have a table that I want to normalize. The SQL below delivers the results
in Alpha Order. But I want them to appear in field order, just like the order
in the SQL below. Can this be done?, or have I created an untameable monster?



SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [tbl_Points] UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [tbl_Points]
UNION SELECT Run_point_List_ID, [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]
FROM [tbl_Points] ORDER BY [Run_point_List_ID];
 
A

Allen Browne

Access won't cope with that many UNIONs.

Create a table (named tblTarget for this example) with fields:
ID AutoNumber (primary key)
[Run_point_List_ID]
[Run_No]
[Run_Point_Venue]
[Run_Point_Address]
The_Source Text

Exeucte an Append query like this:
SELECT INTO tblTarget
([Run_point_List_ID],
[Run_No],
[Run_Point_Venue],
[Run_Point_Address],
TheSource)
SELECT [Run_point_List_ID],
[Run_No],
[Run_Point_Venue_A],
[Run_Point_Address_A],
'A' AS TheSource
FROM [tbl_Points];

Create similar queries for B through X2.
The target table will then contain all the records, and you can sort them on
TheSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
I have a table that I want to normalize. The SQL below delivers the results
in Alpha Order. But I want them to appear in field order, just like the
order
in the SQL below. Can this be done?, or have I created an untameable
monster?



SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [tbl_Points] UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [tbl_Points]
UNION SELECT Run_point_List_ID, [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]
FROM [tbl_Points] ORDER BY [Run_point_List_ID];
Click to expand...
 
E

efandango

Allen, when I use your SQL, I get this error message:

The SELECT statement includes a reserved word or an argument name that is
mispelled or missing, or the punctuation is incorrect.


I have created the table: tblTarget
ID Autonumber (PK)
Run_point_List_ID (Number)
Run_No (Text)
Run_Point_Venue (Text)
Run_Point_Address (Text)
The_Source (Text)

I noticed that you used 'The_Source' for the table, but the SQL used
'TheSource'. I made them consistent, but still get the error.

On another note. How do you mean "Access won't cope with that many UNIONs.".
The original UNION SQL i posted took all SELECT lines and created two
significant columns, Run_Point_Venue and Run_Point_Address (along with
columns for Run_No, Run_Point_List__ID) which is what I wanted. But it simply
put them in Alpha order, rather than the order that I did the SELECTS in, eg:

A
B
C
....





Allen Browne said:
Access won't cope with that many UNIONs.

Create a table (named tblTarget for this example) with fields:
ID AutoNumber (primary key)
[Run_point_List_ID]
[Run_No]
[Run_Point_Venue]
[Run_Point_Address]
The_Source Text

Exeucte an Append query like this:
SELECT INTO tblTarget
([Run_point_List_ID],
[Run_No],
[Run_Point_Venue],
[Run_Point_Address],
TheSource)
SELECT [Run_point_List_ID],
[Run_No],
[Run_Point_Venue_A],
[Run_Point_Address_A],
'A' AS TheSource
FROM [tbl_Points];

Create similar queries for B through X2.
The target table will then contain all the records, and you can sort them on
TheSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
I have a table that I want to normalize. The SQL below delivers the results
in Alpha Order. But I want them to appear in field order, just like the
order
in the SQL below. Can this be done?, or have I created an untameable
monster?



SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [tbl_Points]UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [tbl_Points] UNION
SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [tbl_Points]
UNION SELECT Run_point_List_ID, [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]
FROM [tbl_Points] ORDER BY [Run_point_List_ID];
Click to expand...
Click to expand...
 
A

Allen Browne

If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.

My recollection is that JET can't combine too many UNIONs, but that's just
from memory.

If you are still stuck, post the new SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Allen, when I use your SQL, I get this error message:

The SELECT statement includes a reserved word or an argument name that is
mispelled or missing, or the punctuation is incorrect.


I have created the table: tblTarget
ID Autonumber (PK)
Run_point_List_ID (Number)
Run_No (Text)
Run_Point_Venue (Text)
Run_Point_Address (Text)
The_Source (Text)

I noticed that you used 'The_Source' for the table, but the SQL used
'TheSource'. I made them consistent, but still get the error.

On another note. How do you mean "Access won't cope with that many
UNIONs.".
The original UNION SQL i posted took all SELECT lines and created two
significant columns, Run_Point_Venue and Run_Point_Address (along with
columns for Run_No, Run_Point_List__ID) which is what I wanted. But it
simply
put them in Alpha order, rather than the order that I did the SELECTS in,
eg:

A
B
C
...





Allen Browne said:
Access won't cope with that many UNIONs.

Create a table (named tblTarget for this example) with fields:
ID AutoNumber (primary key)
[Run_point_List_ID]
[Run_No]
[Run_Point_Venue]
[Run_Point_Address]
The_Source Text

Exeucte an Append query like this:
SELECT INTO tblTarget
([Run_point_List_ID],
[Run_No],
[Run_Point_Venue],
[Run_Point_Address],
TheSource)
SELECT [Run_point_List_ID],
[Run_No],
[Run_Point_Venue_A],
[Run_Point_Address_A],
'A' AS TheSource
FROM [tbl_Points];

Create similar queries for B through X2.
The target table will then contain all the records, and you can sort them
on
TheSource.

efandango said:
I have a table that I want to normalize. The SQL below delivers the
results
in Alpha Order. But I want them to appear in field order, just like the
order
in the SQL below. Can this be done?, or have I created an untameable
monster?
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Allen,

This is the SQL I am using:

SELECT INTO tblTarget
([Run_point_List_ID],
[Run_No],
[Run_Point_Venue],
[Run_Point_Address],
The_Source)
SELECT [Run_point_List_ID],
[Run_No],
[Run_Point_Venue_A],
[Run_Point_Address_A],
'A' AS The_Source
FROM [tbl_Points];

Below is my original SQL: (Which will combine all SELECTS and deliver them
into the 2 required columns, [Run_Point_Venue_X1], [Run_Point_Address_X1] and
also Run_No and Run_point_List_ID. But it insists on putting each set of 18
records into alpha order.

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points] UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [Tbl_Points] UNION

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

UNION SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]FROM [Tbl_Points];

I am not sure how to do this instruction:

If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.


Allen Browne said:
If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.

My recollection is that JET can't combine too many UNIONs, but that's just
from memory.

If you are still stuck, post the new SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Allen, when I use your SQL, I get this error message:

The SELECT statement includes a reserved word or an argument name that is
mispelled or missing, or the punctuation is incorrect.


I have created the table: tblTarget
ID Autonumber (PK)
Run_point_List_ID (Number)
Run_No (Text)
Run_Point_Venue (Text)
Run_Point_Address (Text)
The_Source (Text)

I noticed that you used 'The_Source' for the table, but the SQL used
'TheSource'. I made them consistent, but still get the error.

On another note. How do you mean "Access won't cope with that many
UNIONs.".
The original UNION SQL i posted took all SELECT lines and created two
significant columns, Run_Point_Venue and Run_Point_Address (along with
columns for Run_No, Run_Point_List__ID) which is what I wanted. But it
simply
put them in Alpha order, rather than the order that I did the SELECTS in,
eg:

A
B
C
...





Allen Browne said:
Access won't cope with that many UNIONs.

Create a table (named tblTarget for this example) with fields:
ID AutoNumber (primary key)
[Run_point_List_ID]
[Run_No]
[Run_Point_Venue]
[Run_Point_Address]
The_Source Text

Exeucte an Append query like this:
SELECT INTO tblTarget
([Run_point_List_ID],
[Run_No],
[Run_Point_Venue],
[Run_Point_Address],
TheSource)
SELECT [Run_point_List_ID],
[Run_No],
[Run_Point_Venue_A],
[Run_Point_Address_A],
'A' AS TheSource
FROM [tbl_Points];

Create similar queries for B through X2.
The target table will then contain all the records, and you can sort them
on
TheSource.

I have a table that I want to normalize. The SQL below delivers the
results
in Alpha Order. But I want them to appear in field order, just like the
order
in the SQL below. Can this be done?, or have I created an untameable
monster?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1], 1 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A], 2 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B], 3 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT ...
....
ORDER BY TheSource;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Below is my original SQL: (Which will combine all SELECTS and deliver them
into the 2 required columns, [Run_Point_Venue_X1], [Run_Point_Address_X1]
and
also Run_No and Run_point_List_ID. But it insists on putting each set of
18
records into alpha order.

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points] UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [Tbl_Points] UNION

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

UNION SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]FROM [Tbl_Points];

I am not sure how to do this instruction:

If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.
Click to expand...
 
E

efandango

Allen When I Run this SQL below, I get:

The number of columns in the two selected tables or queries of a union query
do not match.

The SQL:

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1], 1 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A], 2 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B], 3 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C], 4 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D], 5 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E], 6 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F], 7 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G], 8 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H], 9 AS TheSource FROM [Tbl_Points] UNION ALL

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2], 10 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I], 11 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J], 12 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K], 13 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L], 14 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M], 15 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N], 16 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O], 17 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P], 18 AS TheSource FROM [Tbl_Points]

UNION ALL

SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

ORDER BY TheSource;





Allen Browne said:
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1], 1 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A], 2 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B], 3 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT ...
....
ORDER BY TheSource;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Below is my original SQL: (Which will combine all SELECTS and deliver them
into the 2 required columns, [Run_Point_Venue_X1], [Run_Point_Address_X1]
and
also Run_No and Run_point_List_ID. But it insists on putting each set of
18
records into alpha order.

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points] UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [Tbl_Points] UNION

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

UNION SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]FROM [Tbl_Points];

I am not sure how to do this instruction:

If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.
Click to expand...
Click to expand...
 
J

John W. Vinson

I have a table that I want to normalize. The SQL below delivers the results
in Alpha Order. But I want them to appear in field order, just like the order
in the SQL below.
Click to expand...

If you want the *ORDER OF RECORDS* in the output table to be in field order -
or in any controllable order at all! - you're misunderstanding how tables
work.

Tables *have no order*. They're an unordered "heap" of data! You really
shouldn't even care what the order of records in the output table might be.

If you want the order incorporated in your new table, include a calculated
field in the UNION query (and in your output table):

SELECT [Run_point_List_ID], [Run_No], (1) AS Run_Point_Number,
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [tbl_Points]
WHERE Run_Point_Address_A IS NOT NULL
UNION ALL
SELECT [Run_point_List_ID], [Run_No], (2) AS Run_Point_Number,
[Run_Point_Venue_B], [Run_Point_Address_B] FROM [tbl_Points]
WHERE Run_Point_Address_B IS NOT NULL
UNION ALL
<etc>

I'm suggesting adding a Run_Point_Number field values 1 through 18 (I'm not
sure what your Run_Point_Address_X1 and X2 are!!) to give you a field which
can be used in a Query for sorting. If you want to see the records in order,
populate the table based on the UNION query; be sure that there is a nonunique
Index on each field that you want to sort; and base your forms and reports on
this sorted query.
 
E

efandango

John,

This is What I have at present:

Run_point_List_ID Run_No Run_point_Venue_X1 Run_point_Address_X1 Run_point_Venue_A Run_point_Address_A Run_point_Venue_B Run_point_Address_B Run_point_Venue_C Run_point_Address_C
377 1 De Vere Hotel 1 Aldwych London Shelton Hotel Chancel Street Costello
Palace Hotel Lavington Street Hilton Hotel Tower Bridge Savoy Court
378 2 Bruebaker Hotel 10 John Adam Street Parkside Hotel N4 Cheney
Road Elena Hotel Leicester Square Hilton Tower Bridge Hotel Savoy Place
379 3 Celstion Hotel 18 Northumberland Avenue Pembury Hotel Drury Lane Eric
Hotel Lennox Road Holiday Inn Express SE1 Seven Sisters Road

And taking your earlier advice from the other thread about normalizing my
table; I am trying to achieve this without having to manually reorganise over
5,000 field contents. This is what I am trying to arrive at.

Run_point_List_ID Run_No Run_point_Venue Run_point_Address

377 1 De Vere Hotel 1 Aldwych
377 1 London Shelton Hotel Chancel Street
377 1 Costello Palace Hotel Lavington Street
377 1 Hilton Hotel Tower Bridge Savoy Court

378 2 Bruebaker Hotel 10 John Adam Street
378 2 Parkside Hotel N4 Cheney Road
378 2 Elena Hotel Leicester Square
378 2 Hilton Tower Bridge Hotel Savoy Place

379 3 Celstion Hotel 18 Northumberland Avenue
379 3 Pembury Hotel Drury Lane
379 3 Eric Hotel Lennox Road
379 3 Holiday Inn Express SE1 Seven Sisters Road


I understand what you mean about the fields being in any controllable order,
and am trying to make good a fundemental mistake. But as you can see from the
first table above, the order is fixed and is vital that the field contents
stay in the order they are relative to each Run_No.

I tried your SQL sample, like this:

SELECT [Run_point_List_ID], [Run_No], (1) AS Run_Point_Number,
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [tbl_Points]
WHERE Run_Point_Address_A IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (2) AS Run_Point_Number,
[Run_Point_Venue_B], [Run_Point_Address_B] FROM [tbl_Points]
WHERE Run_Point_Address_B IS NOT NULL
UNION ALL

UNION ALL SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [Tbl_Points]

ORDER BY Run_Point_Number;

But I get an error saying the DB engine cannot find the Obkect, make sure
you spell it correctly or that its path is correct. I am in the darke here
with SQL, and do not know where I am going wrong. I would appreciate some
help on this.





John W. Vinson said:
I have a table that I want to normalize. The SQL below delivers the results
in Alpha Order. But I want them to appear in field order, just like the order
in the SQL below.
Click to expand...

If you want the *ORDER OF RECORDS* in the output table to be in field order -
or in any controllable order at all! - you're misunderstanding how tables
work.

Tables *have no order*. They're an unordered "heap" of data! You really
shouldn't even care what the order of records in the output table might be.

If you want the order incorporated in your new table, include a calculated
field in the UNION query (and in your output table):

SELECT [Run_point_List_ID], [Run_No], (1) AS Run_Point_Number,
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [tbl_Points]
WHERE Run_Point_Address_A IS NOT NULL
UNION ALL
SELECT [Run_point_List_ID], [Run_No], (2) AS Run_Point_Number,
[Run_Point_Venue_B], [Run_Point_Address_B] FROM [tbl_Points]
WHERE Run_Point_Address_B IS NOT NULL
UNION ALL
<etc>

I'm suggesting adding a Run_Point_Number field values 1 through 18 (I'm not
sure what your Run_Point_Address_X1 and X2 are!!) to give you a field which
can be used in a Query for sorting. If you want to see the records in order,
populate the table based on the UNION query; be sure that there is a nonunique
Index on each field that you want to sort; and base your forms and reports on
this sorted query.
Click to expand...
 
J

John Spencer

Your last query in the UNION lacks a field - 19 as TheSource.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Allen When I Run this SQL below, I get:

The number of columns in the two selected tables or queries of a union query
do not match.

The SQL:

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1], 1 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A], 2 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B], 3 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C], 4 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D], 5 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E], 6 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F], 7 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G], 8 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H], 9 AS TheSource FROM [Tbl_Points] UNION ALL

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2], 10 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I], 11 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J], 12 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K], 13 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L], 14 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M], 15 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N], 16 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O], 17 AS TheSource FROM [Tbl_Points] UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P], 18 AS TheSource FROM [Tbl_Points]

UNION ALL

SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

ORDER BY TheSource;





Allen Browne said:
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1], 1 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A], 2 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B], 3 AS TheSource
FROM [Tbl_Points]
UNION ALL
SELECT ...
....
ORDER BY TheSource;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Below is my original SQL: (Which will combine all SELECTS and deliver them
into the 2 required columns, [Run_Point_Venue_X1], [Run_Point_Address_X1]
and
also Run_No and Run_point_List_ID. But it insists on putting each set of
18
records into alpha order.

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points] UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_A],
[Run_Point_Address_A] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_B],
[Run_Point_Address_B] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_C],
[Run_Point_Address_C] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_D],
[Run_Point_Address_D] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_E],
[Run_Point_Address_E] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_F],
[Run_Point_Address_F] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_G],
[Run_Point_Address_G] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_H],
[Run_Point_Address_H] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X2],
[Run_Point_Address_X2] FROM [Tbl_Points] UNION

SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_I],
[Run_Point_Address_I] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_J],
[Run_Point_Address_J] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_K],
[Run_Point_Address_K] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_L],
[Run_Point_Address_L] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_M],
[Run_Point_Address_M] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_N],
[Run_Point_Address_N] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_O],
[Run_Point_Address_O] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_P],
[Run_Point_Address_P] FROM [Tbl_Points]UNION
SELECT [Run_point_List_ID],[Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1] FROM [Tbl_Points]

UNION SELECT [Run_point_List_ID], [Run_No], [Run_Point_Venue_X1],
[Run_Point_Address_X1]FROM [Tbl_Points];

I am not sure how to do this instruction:

If your original query worked, you could use add:
'A' AS TheSource
etct to each one, and then ORDER BY TheSource.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

ORDER BY Run_Point_Number;

But I get an error saying the DB engine cannot find the Obkect, make sure
you spell it correctly or that its path is correct. I am in the darke here
with SQL, and do not know where I am going wrong. I would appreciate some
help on this.
Click to expand...

I'd try changing this line to

ORDER BY 3;

to order by the third field in the query. It may be choking on the name of the
field.
 
E

efandango

John,

even if i change the ORDER by to 3 or any other number, I still get the same
message.

If I remove the whole line, I still the error.
 
E

efandango

John,

to be specific, the error message say's:

The Microsoft Office Access engine could not find the object ".

I'm not sure what this ". means?
 
J

John W. Vinson

John,

to be specific, the error message say's:

The Microsoft Office Access engine could not find the object ".

I'm not sure what this ". means?
Click to expand...

It's probably the object '' (with a grammatical period after it).

Perhaps you could post the actual SQL that is generating this error. Maybe you
have an extra semicolon somewhere.
 
E

efandango

John,

This is the SQL that I am using. It is based on your SQL suggestion from 6
posts back.

SQL:

SELECT [Run_point_List_ID], [Run_No], (1) AS Run_Point_Number,
[Run_point_Venue_A], [Run_point_Address_A] FROM [tbl_Points] WHERE
Run_Point_Address_A IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (2) AS Run_Point_Number,
[Run_point_Venue_B], [Run_point_Address_B] FROM [tbl_Points] WHERE
Run_Point_Address_B IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (3) AS Run_Point_Number,
[Run_point_Venue_C], [Run_point_Address_C] FROM [tbl_Points] WHERE
Run_Point_Address_C IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (4) AS Run_Point_Number,
[Run_point_Venue_D], [Run_point_Address_D] FROM [tbl_Points] WHERE
Run_Point_Address_D IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (5) AS Run_Point_Number,
[Run_point_Venue_E], [Run_point_Address_E] FROM [tbl_Points] WHERE
Run_Point_Address_E IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (6) AS Run_Point_Number,
[Run_point_Venue_F], [Run_point_Address_F] FROM [tbl_Points] WHERE
Run_Point_Address_F IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (7) AS Run_Point_Number,
[Run_point_Venue_G], [Run_point_Address_G] FROM [tbl_Points] WHERE
Run_Point_Address_G IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (8) AS Run_Point_Number,
[Run_point_Venue_H], [Run_point_Address_H] FROM [tbl_Points] WHERE
Run_Point_Address_H IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (9) AS Run_Point_Number,
[Run_point_Venue_I], [Run_point_Address_I] FROM [tbl_Points] WHERE
Run_Point_Address_I IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (10) AS Run_Point_Number,
[Run_point_Venue_J], [Run_point_Address_J] FROM [tbl_Points] WHERE
Run_Point_Address_J IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (11) AS Run_Point_Number,
[Run_point_Venue_K], [Run_point_Address_K] FROM [tbl_Points] WHERE
Run_Point_Address_K IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (12) AS Run_Point_Number,
[Run_point_Venue_L], [Run_point_Address_L] FROM [tbl_Points] WHERE
Run_Point_Address_L IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (13) AS Run_Point_Number,
[Run_point_Venue_M], [Run_point_Address_M] FROM [tbl_Points] WHERE
Run_Point_Address_M IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (14) AS Run_Point_Number,
[Run_point_Venue_N], [Run_point_Address_N] FROM [tbl_Points] WHERE
Run_Point_Address_N IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (15) AS Run_Point_Number,
[Run_point_Venue_O], [Run_point_Address_O] FROM [tbl_Points] WHERE
Run_Point_Address_O IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (16) AS Run_Point_Number,
[Run_point_Venue_P], [Run_point_Address_P] FROM [tbl_Points] WHERE
Run_Point_Address_P IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (17) AS Run_Point_Number,
[Run_point_Venue_X1], [Run_point_Address_X1] FROM [tbl_Points] WHERE
Run_Point_Address_X1 IS NOT NULL
UNION ALL

SELECT [Run_point_List_ID], [Run_No], (18) AS Run_Point_Number,
[Run_point_Venue_X2], [Run_point_Address_X2] FROM [tbl_Points] WHERE
Run_Point_Address_X2 IS NOT NULL
UNION ALL


UNION ALL SELECT [Run_point_List_ID], [Run_No], [Run_Point_Number],
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [Tbl_Points];
 
J

John W. Vinson

SELECT [Run_point_List_ID], [Run_No], (18) AS Run_Point_Number,
[Run_point_Venue_X2], [Run_point_Address_X2] FROM [tbl_Points] WHERE
Run_Point_Address_X2 IS NOT NULL
UNION ALL


UNION ALL SELECT [Run_point_List_ID], [Run_No], [Run_Point_Number],
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [Tbl_Points];
Click to expand...

What's this last UNION clause? Haven't you already got Run_Point_Address_A?
Does tbl_Points in fact HAVE a field named Run_Point_Number?
 
E

efandango

John,

1. The last UNION clause is my novice inexperience retaining some legacy sql
from the previous SQL that this is based on. I was doing a series of trial
and error attempts and that SQL was the last effort before you asked mew to
paste my SQL.


2. No, I don't have a field named Run_Point_Number?, again my inexperience
got me thinking as you prescribed it in the (#) AS Run_Point_Number clause, I
thought I would need to Call it for my output table. It's pretty obvious to
me that I am out of my depth when it comes to SQL.

Not matter; because I have spent the whole day cutting and pasting a ver
large number of fields and their contents across into various seperate tables
in order to have a normalized table structure for my database MKII... thanks
anyway for your help (and patience...) it is much appreciated.




John W. Vinson said:
SELECT [Run_point_List_ID], [Run_No], (18) AS Run_Point_Number,
[Run_point_Venue_X2], [Run_point_Address_X2] FROM [tbl_Points] WHERE
Run_Point_Address_X2 IS NOT NULL
UNION ALL


UNION ALL SELECT [Run_point_List_ID], [Run_No], [Run_Point_Number],
[Run_Point_Venue_A], [Run_Point_Address_A] FROM [Tbl_Points];
Click to expand...

What's this last UNION clause? Haven't you already got Run_Point_Address_A?
Does tbl_Points in fact HAVE a field named Run_Point_Number?
Click to expand...
 
J

John W. Vinson

John,

1. The last UNION clause is my novice inexperience retaining some legacy sql
from the previous SQL that this is based on. I was doing a series of trial
and error attempts and that SQL was the last effort before you asked mew to
paste my SQL.
Click to expand...

ah... sorry about that! Hate to think how many times I've done the same sort
of thing.
2. No, I don't have a field named Run_Point_Number?, again my inexperience
got me thinking as you prescribed it in the (#) AS Run_Point_Number clause, I
thought I would need to Call it for my output table. It's pretty obvious to
me that I am out of my depth when it comes to SQL.
Click to expand...

The UNION query should NOT contain (or even refer to!) your output table.
Sorry I didn't make that clear - your UNION query would be based on the
wide-flat table and you would then create an Append query based on the UNION
query.
Not matter; because I have spent the whole day cutting and pasting a ver
large number of fields and their contents across into various seperate tables
in order to have a normalized table structure for my database MKII... thanks
anyway for your help (and patience...) it is much appreciated.
Click to expand...

owwwwww.... my sympathies! Hope you got it all in (and can run some Totals
queries to count records to see if it *is* all in).
 
Top