Query within a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ,
I'm quite new to microsoft access program especially access query. Could you
explain to me what type of query should i use to get my output table. At the
moment, I have no idea how to do this task.

These are my original table and output table.
Table 1
LinkNo FromNode ToNode BusRouteNo BusRoute2 BusRoute3
1 200 201 A1 B1 B4
2 156 675 d2 E4 H6

My output table should be in this format:
LinkNo FromNode ToNode BusRouteNo
1 200 201 A1
1 200 201 B1
1 200 201 B4
2 156 675 d2
2 156 675 E4
2 156 675 H6


thanks,
ggobee
 
ggobee said:
Hi ,
I'm quite new to microsoft access program especially access query. Could you
explain to me what type of query should i use to get my output table. At the
moment, I have no idea how to do this task.

These are my original table and output table.
Table 1
LinkNo FromNode ToNode BusRouteNo BusRoute2 BusRoute3
1 200 201 A1 B1 B4
2 156 675 d2 E4 H6

My output table should be in this format:
LinkNo FromNode ToNode BusRouteNo
1 200 201 A1
1 200 201 B1
1 200 201 B4
2 156 675 d2
2 156 675 E4
2 156 675 H6


thanks,
ggobee
Click to expand...
Hi ggobee,
Try this
SELECT LinkNo, FromNode, ToNode, BusRouteNo FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute2 FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute3 FROM Table1

and this is the query to create the new table

SELECT * INTO OutputTable
FROM
(
SELECT LinkNo, FromNode, ToNode, BusRouteNo FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute2 FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute3 FROM Table1
)

Bye
 
If there are a limited number of columns involved, you can use a Union query to
return the results. You cannot update the results of a Union query.

You can NOT build a UNION query in the query grid, you must do so in the SQL window.
--New Query
--Select your table
--Select LinkNo, FromNode, ToNode and BusrouteNo
--Select View: SQL from the menu bar
--You will see something like the following

SELECT LinkNo, FromNode, ToNode, BusRouteNo
FROM [YourTableName];

--Remove the semicolon

--Change the query so it looks like the following
SELECT LinkNo, FromNode, ToNode, BusRouteNo
FROM [YourTableName]
UNION
SELECT LinkNo, FromNode, ToNode, BusRoute2
FROM [YourTableName]
UNION
SELECT LinkNo, FromNode, ToNode, BusRoute3
FROM [YourTableName]
ORDER BY LinkNo, BusRouteNo, FromNode
 
thanks a lot

John Spencer said:
If there are a limited number of columns involved, you can use a Union query to
return the results. You cannot update the results of a Union query.

You can NOT build a UNION query in the query grid, you must do so in the SQL window.
--New Query
--Select your table
--Select LinkNo, FromNode, ToNode and BusrouteNo
--Select View: SQL from the menu bar
--You will see something like the following

SELECT LinkNo, FromNode, ToNode, BusRouteNo
FROM [YourTableName];

--Remove the semicolon

--Change the query so it looks like the following
SELECT LinkNo, FromNode, ToNode, BusRouteNo
FROM [YourTableName]
UNION
SELECT LinkNo, FromNode, ToNode, BusRoute2
FROM [YourTableName]
UNION
SELECT LinkNo, FromNode, ToNode, BusRoute3
FROM [YourTableName]
ORDER BY LinkNo, BusRouteNo, FromNode





Hi ,
I'm quite new to microsoft access program especially access query. Could you
explain to me what type of query should i use to get my output table. At the
moment, I have no idea how to do this task.

These are my original table and output table.
Table 1
LinkNo FromNode ToNode BusRouteNo BusRoute2 BusRoute3
1 200 201 A1 B1 B4
2 156 675 d2 E4 H6

My output table should be in this format:
LinkNo FromNode ToNode BusRouteNo
1 200 201 A1
1 200 201 B1
1 200 201 B4
2 156 675 d2
2 156 675 E4
2 156 675 H6

thanks,
ggobee
Click to expand...
Click to expand...
 
thanks a lot.

Cinzia said:
Hi ggobee,
Try this
SELECT LinkNo, FromNode, ToNode, BusRouteNo FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute2 FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute3 FROM Table1

and this is the query to create the new table

SELECT * INTO OutputTable
FROM
(
SELECT LinkNo, FromNode, ToNode, BusRouteNo FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute2 FROM Table1
UNION ALL SELECT LinkNo, FromNode, ToNode, BusRoute3 FROM Table1
)

Bye
Click to expand...
 
Back
Top