populating one list box from multiple tables. Can it be done?

Morning all! :)

I have a database with multiple linked tables.

I have created a search form with one txt box, for entering search
criteria, and a listbox for each of the tables to isplay the results
from that table.

These list boxes work well, but they are now cluttering the screen.

My ideal scenario would be one listbox where the results specific
fields from all the tables are displayed.

i.e. the users would see something like this on the search form.

Search Criteria : [Statue of Liberty]

"Your Search Returned the following Results"


Listbox Headers: matchingfieldcontent tableName,

Statue of Liberty tblStatues
Statue
tblLandmarks
Statue5 tblLandmarks
Statue of Liberty tblImages

and so on, displaying all records which can then be double clicked to
open the record.

I have looked at some examples of additem method, but everything i
have seen seems to point towards only 1 table being used to search.
Also the tables linked to the database are not related, so i cannot
create a query based on those tables. (and yes i know about
normalisation etc, but these are all inherited tables and i do not
have organisational permissions to do anything with them but link them
in! :)

I am simply looking for a way to search through specific fields in
each table with a text string and populate the listbox with the
results.

There must be a way to get the listbox to display results from
multiple tables?

Help is, as always, really appreciated

Re: populating one list box from multiple tables. Can it be done?

On Oct 18, 5:23 am, redpears...@hotmail.com wrote:
> Morning all! :)
>
> I have a database with multiple linked tables.
>
> I have created a search form with one txt box, for entering search
> criteria, and a listbox for each of the tables to isplay the results
> from that table.
>
> These list boxes work well, but they are now cluttering the screen.
>
> My ideal scenario would be one listbox where the results specific
> fields from all the tables are displayed.
>
> i.e. the users would see something like this on the search form.
>
> Search Criteria : [Statue of Liberty]
>
> "Your Search Returned the following Results"
>
> Listbox Headers: matchingfieldcontent tableName,
>
> Statue of Liberty tblStatues
> Statue
> tblLandmarks
> Statue5 tblLandmarks
> Statue of Liberty tblImages
>
> and so on, displaying all records which can then be double clicked to
> open the record.
>
> I have looked at some examples of additem method, but everything i
> have seen seems to point towards only 1 table being used to search.
> Also the tables linked to the database are not related, so i cannot
> create a query based on those tables. (and yes i know about
> normalisation etc, but these are all inherited tables and i do not
> have organisational permissions to do anything with them but link them
> in! :)
>
> I am simply looking for a way to search through specific fields in
> each table with a text string and populate the listbox with the
> results.
>
> There must be a way to get the listbox to display results from
> multiple tables?
>
> Help is, as always, really appreciated

Yes there is. The rowsource can be any SQL recordset. SQL will allow
you to define complicated relationships between tables and even create
user-defined fields on the fly. What you want, is a UNION query.
That being said, what you are attempting to do may be very
complicated. Start with the end: what would the resultant listbox
look like? What would the field titles be? How would the different
fields be joined? It can return one ID field; which field will it
return? The ID field can be used to look up any selected record, but
which table will it look in? Will it look in one table, and if not
found, look in the other?

Your example table structure is confusing. There are three tables;
tblStatues, tblLandmarks, and tblImages. But what are their fields?
If the fieldnames are not the same, then you will have to use the AS
predicate to rename them, if they will be joined in the same column.

The basic idea is:
SELECT [Id1] AS [Id2], [Description1] AS [Description2] FROM tblOne
UNION ( SELECT [Id2] , [Description2] FROM tblTwo)
That would leave you two fields, ID2 and Description2, in the
resultant recordset. Look up UNION in the Access help to get a better
understanding of this predicate.

Re: populating one list box from multiple tables. Can it be done?

On 18 Oct, 15:08, OldPro wrote:
> On Oct 18, 5:23 am, redpears...@hotmail.com wrote:
>
>
>
>
>
> > Morning all! :)
>
> > I have a database with multiple linked tables.
>
> > I have created a search form with one txt box, for entering search
> > criteria, and a listbox for each of the tables to isplay the results
> > from that table.
>
> > These list boxes work well, but they are now cluttering the screen.
>
> > My ideal scenario would be one listbox where the results specific
> > fields from all the tables are displayed.
>
> > i.e. the users would see something like this on the search form.
>
> > Search Criteria : [Statue of Liberty]
>
> > "Your Search Returned the following Results"
>
> > Listbox Headers: matchingfieldcontent tableName,
>
> > Statue of Liberty tblStatues
> > Statue
> > tblLandmarks
> > Statue5 tblLandmarks
> > Statue of Liberty tblImages
>
> > and so on, displaying all records which can then be double clicked to
> > open the record.
>
> > I have looked at some examples of additem method, but everything i
> > have seen seems to point towards only 1 table being used to search.
> > Also the tables linked to the database are not related, so i cannot
> > create a query based on those tables. (and yes i know about
> > normalisation etc, but these are all inherited tables and i do not
> > have organisational permissions to do anything with them but link them
> > in! :)
>
> > I am simply looking for a way to search through specific fields in
> > each table with a text string and populate the listbox with the
> > results.
>
> > There must be a way to get the listbox to display results from
> > multiple tables?
>
> > Help is, as always, really appreciated
>
> Yes there is. The rowsource can be any SQL recordset. SQL will allow
> you to define complicated relationships between tables and even create
> user-defined fields on the fly. What you want, is a UNION query.
> That being said, what you are attempting to do may be very
> complicated. Start with the end: what would the resultant listbox
> look like? What would the field titles be? How would the different
> fields be joined? It can return one ID field; which field will it
> return? The ID field can be used to look up any selected record, but
> which table will it look in? Will it look in one table, and if not
> found, look in the other?
>
> Your example table structure is confusing. There are three tables;
> tblStatues, tblLandmarks, and tblImages. But what are their fields?
> If the fieldnames are not the same, then you will have to use the AS
> predicate to rename them, if they will be joined in the same column.
>
> The basic idea is:
> SELECT [Id1] AS [Id2], [Description1] AS [Description2] FROM tblOne
> UNION ( SELECT [Id2] , [Description2] FROM tblTwo)
> That would leave you two fields, ID2 and Description2, in the
> resultant recordset. Look up UNION in the Access help to get a better
> understanding of this predicate.- Hide quoted text -
>
> - Show quoted text -

Thank you, thank you and thank you again! :)

As always its that one elusive word that you haven't come across that
throws you completely, in this case 'UNION'

I'm definately on the right track now, i'll try it out. (no doubt
returning swiftly, in the event of a problem

Thanks