Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

enter image description here

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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"
    enter image description here

  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;).
    enter image description here

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

  5. Click "Run".
    enter image description here

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

share|improve this answer
    
Very good :) ... –  William Hilsum May 26 '11 at 20:55
2  
@Wil Bless thee for thy kind words oh mi lord :D –  BloodPhilia May 26 '11 at 20:58
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.