How do I pull data from multiple tables into one table in Access?

Question

I have multiple tables that contain tasks according to their category. Each table represents that category. What I need to do is create a 'Master' table that then is able to pull data from ALL the other tables in a dropdown box. For example:

like this, however this is only one table that I'm able to accomplish this from through the use of the 'lookup and relationship' wizard. How do I enable this for data from multiple tables?

Little More Clarification:

Each subtable has a unique 'Task ID':

Category 1 = 1000 series

Category 2 = 2000 series

Category 3 = 3000 series

....

I need the ability to create unique ID for each of those categories that are then updated, and able to be selected from a different table.

Answer 1

Again, the answer to your problem is a query! And it's the following you need:

SELECT Name FROM FirstTable
UNION
SELECT Name FROM SecondTable
UNION
SELECT Name FROM ThirdTable

etc.

This will give you a set of records from the column name that were gathered by the query from the FirstTable, SecondTable and ThirdTable.

You can then use the lookup wizard in table design to select the query after you've saved it to populate your drop down menu.

---

You can run a query in Access by doing the following:

1. Go to the "Create" tab and click "Query Design"
   

2. Just close the window that appears which asks you to select tables, we don't need that.

3. Go to the "Design" tab and click the button with the arrow until you get a textual input screen. (By default, it says SELECT;).
   

4. Delete the default text and paste the above query.

5. Click "Run".
   

6. Save it by hitting Ctrl + S while the Query's tab is the active tab.

Answer 2

You'll need a query that concatenates data from all three tables that you want available in the drop down. Then base the drop down on that query using the relationship/lookup wizard.