Solved Combo Box rowsource from four different tables (1 Viewer)

[mafhobb]

Hi.
I have inherited a database that has several different tables (tblelectricians, tblplumbers, tblcarpenters,tblgardeners) and each of these tables has a field which includes names (electricianname, plumbername, carpentername, gardenername)
I am building a form with a combobox and as rowsource, I need all those names. I do not mind the order, I just need them all to appear on this combobox so they can be selected.
How do I do this?
mafhobb

 

[Gasman]

Use a union query whilst you fix the issue.
You should have one table with all those names and an additional field that describes their occupation.

What happens when you start adding new occupations?

 

[mafhobb]

The Union Query did it nicely.
Thank you very much!
mafhob

 

[Pat Hartman]

@Gasman 's question wasn't rhetorical. What happens if you have to add a new occupation? The design of the tables is incorrect and will force you to add a new table if you add a new trade. Rather than using the band aid of the Union Query, you should seriously consider fixing the problem as was suggested.

All the workers belong in a single table. Then you need a Trade table to list the trade categories. And you need a third table to associate a worker with a trade. The third table is the key to the process and not understanding this relationship is what caused your predecessor to make this rooky mistake. The third table allows Joe to work either as a plumber or a carpenter.

tblWorkers
WorkerID
FirstName
LastName
....etc

tblTrades
TradeID
TradeName

tblWorkerTrades
WorkerTradesID
WorkerID
TradeID
CertificationDT
...etc

 

[mafhobb]

Hi Pat.
I agree with you...If I get to the point of having to do a new db version, then I'll try to redo this as you suggest.
Thanks

 

[Pat Hartman]

You're welcome