Listbox showing data from 2 tables in one column (1 Viewer)

[Dansam]

Something I was thinking about (just to make my interface easier)
I'd like to create a listbox with data from 2 tables (tblOne and tblTwo)
tblOne has fields HName (short text) and HThemeID (number, used to link to a master table in a relationship)
tblTwo has fields OName (short text) and OThemeID (number, used to link to a master table in a relationship)
I'd like to see HName and OName in a single column with HThemeID and OThemeID in a second column
I've looked around but can't see a way of doing it; is it possible?
EDIT
To explain further I'll provide data

INPUTS
tblOne

HName	HThemeID
Hobby1	1
Hobby2	2

tblTwo

OName	OThemeID
Occ1	1
Occ2	2

RESULT
Listbox

Name	ThemeID
Hobby1	1
Hobby2	2
Occ1	1
Occ2	2

In essence the ID column is not being used for anything in the list box (it's not indexed or unique or autonumber or primary key), it's just another field with a value.

 

[Ranman256]

make your query with the 4 fields,
put the query as the source of the listbox
set the listbox columns = 4
bind the listbox col. to the field you want. Default=1

 

[Dansam]

make your query with the 4 fields,
put the query as the source of the listbox
set the listbox columns = 4
bind the listbox col. to the field you want. Default=1

Thanks but that just creates a 4 column list, and too many entries! I'll edit the OP and see if I can explain myself more clearly

 

[MajP]

Union query

Select Hname as H_O_Name, HThemeID as ThemeID from tblOne
Union
Select Oname, OThemeID from tblTwo

Although that table structure seems strange. Some sample data could help explain.

 

[Gasman]

I'd say union query as well, but how do you differentiate between same ID = 1 or 2 in your example?

 

[Dansam]

I'd say union query as well, but how do you differentiate between same ID = 1 or 2 in your example?

The HThemeID and OThemeID are fields used to link the one-to-many relationships, they're double values in a number field in the repective tables

 

[Gasman]

I realise that, but if I choose Hobby2 how do you determine which table to use, the ID is the same as Occ2? Wouldn't you need a third column to identify that?

 

[Dansam]

I realise that, but if I choose Hobby2 how do you determine which table to use, the ID is the same as Occ2? Wouldn't you need a third column to identify that?

The ID field here is not being used for anything in the list box, it is just for display.

 

[MajP]

The ID field here is not being used for anything in the list box, it is just for display

That seems kind of strange, but it is your requirement. Not sure what anyone would do with that since you do not know the table from where it came. You can add that, but still not sure what it gives you.

Select Hname AS H_O_Name, HThemeID, AS ThemeID, "TblOne" AS Table_Name from tblOne
Union
Select Oname, OThemeID, "TblTwo" from tblTwo

Or since this relates to a Theme in another table you could join these tables first on the theme table and bring in something maybe more meaningful like the theme Name (assuming that exists)

 

[Dansam]

That seems kind of strange, but it is your requirement. Not sure what anyone would do with that since you do not know the table from where it came. You can add that, but still not sure what it gives you.

Select Hname AS H_O_Name, HThemeID, AS ThemeID, "TblOne" AS Table_Name from tblOne
Union
Select Oname, OThemeID, "TblTwo" from tblTwo

Or since this relates to a Theme in another table you could join these tables first on the theme table and bring in something maybe more meaningful like the theme Name (assuming that exists)

So currently the theme ID in both tables is set to 0, what I'm aiming to do is select items in the list box and run an update, relating the list entries to a theme in a master table.

 

[MajP]

Then you may need what I posted in #9 at a minimum so you know what table it came from, unless the update just needs values from the theme table for that themeid.

 

[Dansam]

Then you may need what I posted in #9 at a minimum so you know what table it came from, unless the update just needs values from the theme table for that themeid.

Thanks MajP, that works a treat, and now all I have to do is write the code to do the update...

 

[Pat Hartman]

People are trying to help you do what you asked but what is the point of merging two sets of data in one combo? How will you be able to make use of the data if you don't know where it came from? And the more important question is why do you have two tables to hold the same data? And if the data isn't the same, why do you want to combine it. I can have a table of friends and a table of pets. I can use a union query to produce a RowSource for a combo that lists both my friends and my pets but what can I use it for?

 

[Dansam]

People are trying to help you do what you asked but what is the point of merging two sets of data in one combo? How will you be able to make use of the data if you don't know where it came from? And the more important question is why do you have two tables to hold the same data? And if the data isn't the same, why do you want to combine it. I can have a table of friends and a table of pets. I can use a union query to produce a RowSource for a combo that lists both my friends and my pets but what can I use it for?

Thanks @Pat Hartman for the feedback.
I have a table 'Themes' and want to add skills and hobbies to the theme (e.g. Travel Theme could have skills Mechanic, Panel Beater, Pilot and interests Vintage Cars, Sailing)
I wanted a simple interface to list the skills and interests and update them accordingly. Having achieved that, found that the update is not as simple or achievable.
Luckily I'm big enough to admit that a good idea is not always the right idea!
"I've never failed, just found a thousand ways to not do it right"

 

[Gasman]

So you would have them all in one table and another field that indicates whether Occupation or Hobby?

 

[Dansam]

So here's the structure:

In the form I' like a user to select a hobby or occupation (or more than one of each), click a button and the OccupationThemeID or HobbyThemeID field is updated in thier respective tables, so a Theme can have multiple hobbies or occupations associated with it.
Now this may be my wishful thinking and it can't be done this way in which case I will split the listbox into two and do it that way!
Thanks for any help that could be offered.

 

[Pat Hartman]

If you want to treat hobbies and occupations as the same entity, then as has already been suggested, put them in the same table with an attribute that allows you to separate them when you need to work with the categories separately. You are making this harder than it needs to be by insisting on separate tables.

In the real world, a skill for one person might be a hobby for another so there should logically be only a single table. The attribute in the real world would be in the junction table where you connect "programming" to Suzy as a skill but to Joe as a hobby. Programming is both so even the suggestion to add the attribute to the "talents" table is incorrect. Knitting is a hobby when you are making scarves for yourself and friends. However it is a skill if you are making items to sell.

 

[Dansam]

Thanks @Pat Hartman, I changed the design so that skills and interests are now in a single table and that makes it much cleaner. Sometimes I get so close to these things that I don't think about simple design changes that will fix the problem!

 

[Pat Hartman]

You're welcome. Are you following the advice to put the skill/interest designation in the junction table also? Instead of just using skill/interest, you might consider a numeric skill "level". It really depends what you are using this information for.

 

[Dansam]

You're welcome. Are you following the advice to put the skill/interest designation in the junction table also? Instead of just using skill/interest, you might consider a numeric skill "level". It really depends what you are using this information for.

Thanks Pat, yes I have a skills proficiency table and the function was working, just running test data through the DB to see if I need to rebuild that