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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yue Zhao - 16 May 2007 17:56 GMT

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

Reply to this Message

dbahooker@hotmail.com - 16 May 2007 23:34 GMT

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'

> Hi,
>
[quoted text clipped - 16 lines]
>
> Joy

Reply to this Message

Brendan Reynolds - 17 May 2007 12:08 GMT

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;

Signature

Brendan Reynolds

> Hi,
>
[quoted text clipped - 16 lines]
>
> Joy

Reply to this Message