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

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
0
Yue
5/16/2007 4:56:42 PM
access 16762 articles. 3 followers. Follow

2 Replies
752 Views

Similar Articles

[PageSpeed] 6

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'




On May 16, 9:56 am, Yue Zhao <yuez...@bu.edu> wrote:
> 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


0
dbahooker
5/16/2007 10:34:00 PM
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;

-- 
Brendan Reynolds

"Yue Zhao" <yuezhao@bu.edu> wrote in message 
news:ec3zrs9lHHA.2596@TK2MSFTNGP06.phx.gbl...
> 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



0
Brendan
5/17/2007 11:08:34 AM
Reply:

Similar Artilces:

copying formulas, but not values
I have a spreadsheet where I need to continue to add new rows. I want to be able to copy a row with all the formulas intact, but none of the values that may be in the row being copied. For example, if I have my cursor on row 1, which may already contain values, I want to be able to copy Row 1 to Row 2 with the formuals, but not values that may already be in Row 1. Here is my VBA code. It copies and inserts a new row and the formulas, but it will also include any values. Any suggestions? Sub Insert_Row() Dim rnRow As Range Application.ScreenUpdating = True Set rnRow = ActiveCell rnR...

Linking Combo box to pivot table?
Hi All, I have a pivot table which has teams on the left, months on the top and a count of the calls in the data area. What I would love is that if I could have a combo box on a worksheet which when I select a team from its list it only shows me that teams data in the pivot table. Is this possible please?? -- Adam ----------- Windows 98 + Office Pro 97 You could move Team to the page area of the pivot table. Select a team, and the pivot table will only show its data. Adam wrote: > Hi All, > > I have a pivot table which has teams on the left, months on the top and a > c...

Migration Wizard
Hello, I red this at Microsoft: "When you use the Migration Wizard to migrate mailboxes, single instance storage is lost for the migrated data in the target information store" Is there a way to fix this after the migration in the target information store? Thanks, George George, There is not a way to establish Since Instance Storage (SIS) after the mailboxes have been migrated to the target store. -- JamesA@online.microsoft.com This posting is provided "AS IS" with no warranties, and confers no rights. Note: Please do not send email directly to this alias. This a...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Tables #3
Does anyone know what the ESI_Location_XREF table is or what product it is associated with? Sue, The ESI_Location_XREF table (ESI Location Cross-Reference, dbo.ESI40300) is a part of vSync's EDI for Dynamics -- the SOP portion. You can find more information about vSync's products at http://www.vsync.com Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Sue" wrote: > Does anyone know what the ESI_Location_XREF table is or what p...

count of records in a table in external database
Hi, I would like to get the record count of a table in an external database (using the name of the table in the loop of the current database). What I've tried (doesn't work): SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" & rs!Name & "]") The procedure (**** is where the problem line is): ------------------------------ Public Sub AppendTableRecords() Dim strSelectAllTables As String strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name not like 'MSys%';"...

# value in spreadsheet
When i copy spreadsheets and build a master worksheet # value shows up in any cell that doesn't have a number or zero in it. I know there is a way to fix this but can't seem to find it---can anyone help? Does the cell just turn to #value! Or is it the result of a formula? If it's a formula that's being copied, what's the formula? ron cooley wrote: > > When i copy spreadsheets and build a master worksheet # > value shows up in any cell that doesn't have a number or > zero in it. I know there is a way to fix this but can't > seem to find it-...

New user in need of assistance Re: excel pop-ups, forms, +more!!
Hello to all that read this! I have been asked to complete a mini project and have no idea how to make it happen in excel! I require a pop-up to ask me to enter a date for items to be ordered, what the items are, and also a date for when they are required by. Once that has been entered, this needs to populate cells in excel. Working from the dates supplied in those cells, a pop-up is required when it is time for an item to be ordered to remind a user to do so. I would also require a pop-up indicating when an item is late for being ordered i.e it has passed its order-by date. And fin...

Table Default Values
Is it possible to set the default value of a field in the table to either a value entered into a message box or a value entered into another table? I'm importing data from a text file into an existing table. I have been manually changing the default value for the report date field each time I import a new report, but since I won't be doing the importing anymore, would like to automate or simplify this process. Any suggestions? Hi, If you want the current date you can just use =Date(). If some other date, you can do something like this: Public Function Lo...

Nulls in Access 2007 Fields
Here is my issue: I have to generate a report which breaks down specific information, specifically a count derived from when the information was created. I have accomplished this. My problem is when I run a specific query for one piece of information it returns what I am looking for. When I run a different query with the same information looking for the other data I need - it does not give me anything. Now the problem is there is nothing for the database to pull up because the answer is zero, but I can not get Access to give me a zero in the field to show there were no new enrollie...

Recording a new loan in my checkbook ledger
This is a very simple question. I've got Microsoft Money Plus for 2008. I have gotten a new loan and it was deposited into my checking account. The loan is from the same bank as my checking account. How do I record that in MS Money? Do I just put in my bank's name, or what? I know this question is very simplistic, but what I'm concerned about is not confusing myself, at least, with what I see in MS Money when I review the ledger there. Rod It's not clear what part of this activity you want to record in your checkbook ledger. The receipt/deposit of the loan proce...

Counting the number of unique different values in a set
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi everyone, I need to count the number of different values in rows of data. <br><br>For example, in 100 rows of data about Gender, there are only two values, F and M, so I would like the function to return 2, even if I select 100 rows all with data. <br><br>Another example: <br> Given the following list: <br><br>apple <br> orange <br> berry <br> berry <br> apple <br> banana <br> apple <br><br>The function would ret...

Matching numeric values in XPath
I use Xpath to retrieve nodes in a document like : doc.SelectSingleNode("/properties/layer[@width='0.2']"); - however this is not very robust as I'm only query on text. What if width='0.200' instead of width='0.2', then the above Xpath line wont match the node in question. Is there a way to query for the value instead of the text? Best regards Jesper. Jesper Denmark wrote: > I use Xpath to retrieve nodes in a document like : > > doc.SelectSingleNode("/properties/layer[@width='0.2']"); > > - however this is not v...

grab a variable
i have a report that is currently using some code to grab a variable from a form for display on the report..problem is that the underlying data changes when there is more than one page to display, but it gets the same data from the same displayed form. hope this exaplination makes sense and somebody knows what i need to do to fix. tia, mcnewsxp i fixed by moving the code to the detail section. ...

automatically calculate table values via formulas in sheet
Hi, I am stuck with a challenge, maybe someone can help. I am creating a table with the outcomes of various calculations. I got a worksheet with 2 variable inputs (A1 and A2) and the outcome presented in A7. Now I need to create a table with the possible A1 values above and the possible A2 values on the left. As there are over 100 options for A1 and over 300 for a2 you can imagine I don't want to type all these values in A1 and A2. So I am wondering, there must be a function to calculate the outcomes A7 in this table using the formula's existing in my sheet. Does anybody know how th...

Restart automatic table numbering in Annex
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I use the automatic numbering of Tables and Figures when creating captions. This generally works ok (although I sometimes get numbering out of sequence when more than one figure on a page), but I've run into a problem. <br><br>I have multiple tables in the main part of the report that I want numbered in the normal way. I also have multiple tables in the annex to the report (which is part of the same document) and I would like the numbering of these tables to start from 1 again (perhaps A1, A2 e...

Matching data in two tables
This is probably pretty rudimentary but I'm doing an Update Query in 2007 and I am having an issue with my criteria. I have two tables: tbl_1 contains a column with a serial number (14,000 rows) . tbl_2 contains a column with user description information that includes the serial numbers I want to match. (45,000 rows) tbl_1 field: 673567 tbl_2 field: FTN 673567 333, Doe John, W, X, Here is my query in SQL: UPDATE tbl_2, tbl_1 SET tbl_2.[Compare Match] = "Match" WHERE (((tbl_2.Descriptor) Like '*' & [tbl_1]![Serial Number] & '*')); I ...

Handle missing values
Hi! I have a data set that sometimes contains missing values (#MISSING!). I want to make graphs of the data and I want the graph to just skip the missing values. How can I do that? Most thankful for any help! The cells say "#MISSING!"?? How unfortunate. Either delete all the #MISSING! entries in the table (to leave blank cells), replace them with #N/A! (works best for line and XY charts), or build a table linked to the first with formulas like =IF(ISNUMBER(A1),A1,NA()) where NA() produces #N/A in the cells. Use this second table as the chart source data. - Jon ------- Jon P...

How do I reference values from 200 worksheets onto a summary sheet
I'm using Excel 2003. I have a workbook with over 200 worksheets. (Each tab named for an individual in a group.) I'm trying to set up a summary sheet which needs total values from each of the individual worksheets. I'm using a macro which creates a Table of Contents, then sorts the sheets and the list, providing a link to each sheet. I expect new names to be added so the macro will have to be rerun occasionally. A separate macro places the sheet name in cell A1 of each sheet in case that can be of any use. Is there a way to get the total values from each sheet onto th...

Parameter Make Table Queries
I have an excel spreadsheet that establishes a link to an Access 2003 DB. I need to be able to run a pre-saved make-table query with 4 parameters that is stored in my DB, and then use excel to run a pre- saved crosstab query off of this table and a few other queries. The results of this crosstab will ultimately be pulled into Excel. I tried changing the make-table into a select and modifying the cross- tab, but because of the parameters and complexity of the cross-tab, it won't work. The first problem is: I "dummed" down my make-table query to eliminate all parameters. Upon ...

formula does not recognize value
i have a workbook that calculates exp dates. When the date lands on a saturday or sunday, I want it to bump out to the following monday. How can I do this? --- Message posted from http://www.ExcelForum.com/ Make your formula consider the WEEKDAY result of its own calculation and add 1 ot 2 according to that. HTH. Best wishes Harald "Vato Loco >" <<Vato.Loco.16gz0q@excelforum-nospam.com> skrev i melding news:Vato.Loco.16gz0q@excelforum-nospam.com... > i have a workbook that calculates exp dates. When the date lands on a > saturday or sunday, I want it to bump ...

Recordsets-Old and New
I am trying to create two recordsets from a single form, one when the form is first opened and a second after updates (not entry) are entered. The first recordset, rsOpen, is defined on the form_open event: sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID Set rsOpen = CurrentDb.OpenRecordset(sNewVal) ''' get orginal values The second I would like to define when a Submit button is clicked: Me.Dirty = False sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID Set rsSubmit = CurrentDb.OpenRec...

Static Chart using Pivot Table and other data
Using Excel 2000, I am trying to create a Static Chart where the source data is a pivot table and data from a couple of adjacent columns. In the past I have been able to do this. My latest chart is automatically creating a Pivot Chart without the adjacent data. How do I prevent this automation? There are instructions on Jon Peltier's site, for creating a normal chart from PivotTable data: http://peltiertech.com/Excel/Pivots/pivotcharts.htm Will wrote: > Using Excel 2000, I am trying to create a Static Chart where the source data is a pivot table and data from a couple of adjacent...

Keeping Column Widths the same in a pivot table when pages increase
I've created a pivot table and because of the length of the characters in the page, the column width keeps changing. How do I keep the column width the same? Set the pivot table to preserve formatting: On the pivot toolbar, choose PivotTable>Table Options Add a check mark to 'Preserve formatting', click OK Remove the check mark from AutoFormat Table Click OK cailotto@sbcglobal.net wrote: > I've created a pivot table and because of the length of the characters > in the page, the column width keeps changing. How do I keep the column > width the s...

Message POP of new email at web
i m willing to have a setup like ..if i recieve a email at my address thats ali@mydom.com account of exchange server......what i want to do is , if i recieve any email on my accout of exchange ali@mydom.com, i shell get a messege in my yahoo accout that i have recived an email. Wating for the valueble advices. Regards, ...