ROW SOURCE QUERY IN LISTBOX 1

[Poteen]

I'm in a quandry and hope that someone out there can help!!

A query from a single table in the listbox rowsource property is relatively straight forward. However I cantwork out how to query from two tables at the same time.
eg:
SIMPLE QUERY (no problem):
SELECT [Table1].[Field1], [Table1].[Field2]FROM [Table1]
This will populate a 2-column listbox.

COMPOUND QUERY (BIG PROBLEM!!!!!):
I want to SELECT Field1 from Table 1 and SELECT Field3 from Table 3

I keep seeing fluent use of JOIN, INNER JOIN, UNION etc but I don't understand how they might be used in this context.
Can anyone help?
John

 

[vbajock]

Can you join two tables together using the query grid? Easiest way to do it would be to build your query in in the grid, view the SQL that is created (Click View,Sql View from the grid) and cut and paste the SQL either directly into the Rowsource property of the control or by coding it using ctlname.rowsource="SELECT blah blah". You can then look the syntax over and get a better idea of how the joins work.

 

[Norris68]

Access is a bit sketchy in it's implementation of the UNION operator. UNION will 'glue' two query result sets together providing both return the same number of fields and of the same types. The query you want would be:

SELECT Field1 FROM Table1 UNION SELECT Field3 FROM Table3

Failing that, you could cobble it together by populating the list box via a code function. See example code in the MS KB article

http://support.microsoft.com/default.aspx?scid=kb;en-us;210290

 

[Poteen]

vbajock: Thanks but I'm trying to avoid that method.

norris: Bingo! It's even easier than I hoped. I'm going to try separate WHERE's as well. Good one!!
Cheers, thank you and a pink star for short & efficient!!
John