Please ... union query -> new column ?

[marco02]

Hi,

I'm trying to gather info from two tables in a way that the info from the 1st table will occupy the first three rows and info from the 2nd table the next rows (say 4,5,6).

It has to be Visual Basic coded because the database is +10000 records and changes regurlarily.

Those tables are related by a Link field which I use to match data from the two tables

Tbl 1
Id | a | b | c | link To tbl2 (matches the Id of tbl2)
1 | w | w | w | 2
2 | x | x | x | 1


Tbl 2
Id | a | b | c |
1 | y | y | y |
2 | z | z | z |

I would like the resulting query to show me (for example)
Result Tbl
| w | w | w | z | z | z |

How shall I code this thing ?
Union queries outputs the data like that
| w | w | w |
| z | z | z |

Which I don't want .... so can unioin query paste the result in following columns ? is there a "transpose" method in access ?

Thanks for you help!

marco

 

[GoDawgs]

Seems like you could just set up a regular query and link Table2ID to the Link to Table 2 field. Then just Select all 6 columns to be shown in the query...so basically something like this:

SELECT Tbl2ID, Tbl1.A, Tbl1.B, Tbl1.C, Tbl2.A, Tbl2.B, Tbl2.C
FROM Tbl1, Tbl2
WHERE Tbl2ID = Tbl1.[Link to Table 2]

don't know if there's something more complicated involved that would make this ineffective, but this would work for your example I believe. Hope that helps.

Kevin