grab distinct values from a bunch of fields in other tables, andstore in a new table

[Yue Zhao]

Hi,

In my access database, I have a few tables (tbl1, tbl2, ...) storing
Medication names.
Now, to avoid entering same medication in different ways, I am planing
to create a table tbl_Lib to store a standard name of every medication
from tbl1, tbl2, ...
Then, this tbl_Lib will work as a medication name library.

Now, I am wondering if there is an easy way to import the existing
medication name into this new table tbl_Lib.
The medications to import are now stored in the following fields:
tbl1.Med1
tbl1.Med2
tbl1.Med3
tbl2.Medication

Thank you for suggestions!

Joy

 

[dbahooker]

yeah this is called 'DImensional Modeling'

If you were using SQL Server and Query Analyzer you could do almost
all of this in a single call--

it's called 'psuedo-dynamic SQL' as illustrated in 'SQL Server 7
Secrets'

 

[Brendan Reynolds]

You can use a union query such as the following to retrieve the data from
the existing columns and tables ...

SELECT Med1
FROM Table1
UNION SELECT Med2
FROM Table1
UNION SELECT Medication
FROM Table2;

Then create an append query using the union query as its source to append
the data to the new table ..

INSERT INTO MyNewTable ( Med1 )
SELECT quniTest.Med1
FROM quniTest;