query to combine fields from two different taBLE

[Indep99]

Hi,
I have two tables
Table A has programs
Table B has projects
I want to create a query that will list all of the programs and projects in one column, is there a way to do this?

Thanks!

 

[pbaldy]

SELECT Programs
FROM TableA
UNION ALL
SELECT Projects
FROM TableB

substituting the actual names of course.

 

[Indep99]

Thank you but it says query needs at least one destination field

 

[pbaldy]

What is the SQL of your query?

 

[Indep99]

oh, I wasn't using sql, I was just typing it in the field section under design view

 

[pbaldy]

A UNION query has to be done in SQL view, so whatever you have it isn't what I suggested. With your query in design view, on the toolbar/ribbon you should have an icon on the far left that lets you switch to SQL view (design view is just a GUI to create SQL). Go to that view and try what I suggested, or post what your SQL is.

 

[Indep99]

great thanks tha tworked

 

[pbaldy]

No problemo.

 

[Indep99]

SELECT [projectname] From [tbl_projects] Union All Select [programname] From [tbl_programs]
Select [Subject] From [tbl_projects] Union All Select [Title] From [tbl_programs]

one would work without the other but if I use both, i get syntax error in FROM clause

 

[pbaldy]

You can only have one SELECT clause without a UNION. Are you trying to put all 4 tables together? That would be more UNION statements:

SELECT [projectname] From [tbl_projects]
Union All
Select [programname] From [tbl_programs]
UNION ALL
Select [Subject] From [tbl_projects]
Union All
Select [Title] From [tbl_programs]

 

[Indep99]

sorry, I should have been clearer
I am trying to have 2 columns that will each combine 2 fields from 2 different tables

 

[pbaldy]

Might help to see some sample data. It sounds like you need two UNION queries and then a third query to join them, but for that to happen there would have to be a field that related them.