Add two Columns to a table from another table

It has been a couple of months since I have worked with access and I can't 
remeber the right way to put the critera. I want to add two columns from 
table A to table B where the machine name is equal to each other.

I know I have to use an append query
I just can't figuer out how to set the critera to place the correct data in 
the right place. Both tables have been imported and both have a column called 
machine name. I want to add two columns from table A to table B where the 
machine names are the same.

Please help me out.

Thanks very much,
Greg
0
Utf
6/19/2007 1:44:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
670 Views

Similar Articles

[PageSpeed] 34

It isn't clear whether you are adding new columns to a second table, or
adding the data from one table to another.

In either case, it sounds like you'd end up with two copies of the same
data.  If so, which table's data is the correct data?  I ask because having
redundant data (same fact stored more than one place) can suffer
synchronization problems (a change to one that doesn't get reflected in the
other).

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"GregB" <GregB@discussions.microsoft.com> wrote in message
news:CBDE9D9B-71A5-4C2A-885D-B112ACD1636B@microsoft.com...
> It has been a couple of months since I have worked with access and I can't
> remeber the right way to put the critera. I want to add two columns from
> table A to table B where the machine name is equal to each other.
>
> I know I have to use an append query
> I just can't figuer out how to set the critera to place the correct data
in
> the right place. Both tables have been imported and both have a column
called
> machine name. I want to add two columns from table A to table B where the
> machine names are the same.
>
> Please help me out.
>
> Thanks very much,
> Greg

0
Jeff
6/19/2007 1:53:27 PM
ok, let me try to clarify

I have two reports that I generated in Excel. I imported them into two 
tables in Access

In table A there is Machine Name, company, location
In table B there is Machine Name, user, specs

I want to add company and location to table B Where the machine names are 
the same

And then I will export the file backinto an excel file

Does that clarify things?

Thanks for the responce



"Jeff Boyce" wrote:

> It isn't clear whether you are adding new columns to a second table, or
> adding the data from one table to another.
> 
> In either case, it sounds like you'd end up with two copies of the same
> data.  If so, which table's data is the correct data?  I ask because having
> redundant data (same fact stored more than one place) can suffer
> synchronization problems (a change to one that doesn't get reflected in the
> other).
> 
> -- 
> Regards
> 
> Jeff Boyce
> www.InformationFutures.net
> 
> Microsoft Office/Access MVP
> http://mvp.support.microsoft.com/
> 
> Microsoft IT Academy Program Mentor
> http://microsoftitacademy.com/
> 
> "GregB" <GregB@discussions.microsoft.com> wrote in message
> news:CBDE9D9B-71A5-4C2A-885D-B112ACD1636B@microsoft.com...
> > It has been a couple of months since I have worked with access and I can't
> > remeber the right way to put the critera. I want to add two columns from
> > table A to table B where the machine name is equal to each other.
> >
> > I know I have to use an append query
> > I just can't figuer out how to set the critera to place the correct data
> in
> > the right place. Both tables have been imported and both have a column
> called
> > machine name. I want to add two columns from table A to table B where the
> > machine names are the same.
> >
> > Please help me out.
> >
> > Thanks very much,
> > Greg
> 
> 
0
Utf
6/19/2007 2:01:17 PM
Ok thanks, I forgot I could set a relation in a query and was to caught up on 
trying to come up with an expression in the critera field

THanks a lot

"Sprinks" wrote:

> Greg,
> 
> An Append query adds new *records* to a table.  Since you want to match 
> records from the two tables having the same machine name, you need an Update 
> query.
> 
> First, if you haven't done so already, add fields to TableB of the same 
> field type as in Table A.  An Update query changes the value of pre-existing 
> fields but won't add a new field.  The query then updates the value of each 
> Table B column from the other table's corresponding value, joined by the 
> MachineName:
> 
> UPDATE TableA INNER JOIN TableB ON TableA.ID = TableB.ID SET TableB.Column1 
> = [TableA].[Column1], TableB.Column2 = [TableA].[Column2];
> 
> Hope that helps.
> Sprinks
> 
> "GregB" wrote:
> 
> > It has been a couple of months since I have worked with access and I can't 
> > remeber the right way to put the critera. I want to add two columns from 
> > table A to table B where the machine name is equal to each other.
> > 
> > I know I have to use an append query
> > I just can't figuer out how to set the critera to place the correct data in 
> > the right place. Both tables have been imported and both have a column called 
> > machine name. I want to add two columns from table A to table B where the 
> > machine names are the same.
> > 
> > Please help me out.
> > 
> > Thanks very much,
> > Greg
0
Utf
6/19/2007 2:35:01 PM
Unlike Excel, you don't need to "add columns to tables" to get a report in 
Access.  It appears that "MachineName" is a field in common between your two 
tables.  If so, use a query to join the two tables, select the fields you 
want to see, and "export" the query to Excel.  It is actually the data that 
gets exported.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"GregB" <GregB@discussions.microsoft.com> wrote in message 
news:3B1F06FD-2377-4D27-8098-22878D9A2BC6@microsoft.com...
> ok, let me try to clarify
>
> I have two reports that I generated in Excel. I imported them into two
> tables in Access
>
> In table A there is Machine Name, company, location
> In table B there is Machine Name, user, specs
>
> I want to add company and location to table B Where the machine names are
> the same
>
> And then I will export the file backinto an excel file
>
> Does that clarify things?
>
> Thanks for the responce
>
>
>
> "Jeff Boyce" wrote:
>
>> It isn't clear whether you are adding new columns to a second table, or
>> adding the data from one table to another.
>>
>> In either case, it sounds like you'd end up with two copies of the same
>> data.  If so, which table's data is the correct data?  I ask because 
>> having
>> redundant data (same fact stored more than one place) can suffer
>> synchronization problems (a change to one that doesn't get reflected in 
>> the
>> other).
>>
>> -- 
>> Regards
>>
>> Jeff Boyce
>> www.InformationFutures.net
>>
>> Microsoft Office/Access MVP
>> http://mvp.support.microsoft.com/
>>
>> Microsoft IT Academy Program Mentor
>> http://microsoftitacademy.com/
>>
>> "GregB" <GregB@discussions.microsoft.com> wrote in message
>> news:CBDE9D9B-71A5-4C2A-885D-B112ACD1636B@microsoft.com...
>> > It has been a couple of months since I have worked with access and I 
>> > can't
>> > remeber the right way to put the critera. I want to add two columns 
>> > from
>> > table A to table B where the machine name is equal to each other.
>> >
>> > I know I have to use an append query
>> > I just can't figuer out how to set the critera to place the correct 
>> > data
>> in
>> > the right place. Both tables have been imported and both have a column
>> called
>> > machine name. I want to add two columns from table A to table B where 
>> > the
>> > machine names are the same.
>> >
>> > Please help me out.
>> >
>> > Thanks very much,
>> > Greg
>>
>> 


0
Jeff
6/19/2007 3:41:35 PM
Reply:

Similar Artilces:

Dynamics 7.0 tables in SQL server 2000
I need to know what should be the content of each of the tables with names starting with "GL" in SQL Server 2000 when using 7.0. Is there a reference that I can go to in Customersource that tells me this information? Thanks. -- Dave Christman System Developer There is an online reference: Tools >> Resource Descriptions >> Tables. Also there is a SDK avaailable on the Great Plains CD's. "Dave Christman" wrote: > I need to know what should be the content of each of the tables with names > starting with "GL" in SQL Server 2000 when...

add contents of one column
I have a budget that I want to automatically add the contents of one column as I'm working on the sheet. I am new to this and I need all of the helo that I can get. In B1: =SUM(A:A) -- HTH RP (remove nothere from the email address if mailing direct) "Pam" <Pam@discussions.microsoft.com> wrote in message news:FC5F8EE6-99D4-4327-B452-D34E441CA743@microsoft.com... > I have a budget that I want to automatically add the contents of one column > as I'm working on the sheet. I am new to this and I need all of the helo that > I can get. Assume your numbers ...

modify the Type of Field in a Table From another MDB through a Command button
Hello to all! I hope can help me. By technical questions that would be to me very long to explain, the following necessity arises to me: I need To modify the Type of Field in a Table From another MDB through a Command button. The field at issue would happen to be "Number" to "TEXT". Is this possible one? I wait for alternatives. Thanks in advance, and sorry for my poor english. Dreamer. - Hi Is it not possible to open the other database and simply open the table in design view and then change the field. It seems very complex to do the codeing what will most pro...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Add a server. Dumb-but necessary-question
I'm planning to add a Exch 2003 server to an existing Exch 2000 org and - after testing with a few mailboxes, move the system folders, etc - I'm planning to retire the 2000 server. My mx record points to a WAN ip address which is translated by a Cisco Pix 506e to allow port 25 to the private ip address of my existing Exchange 2000 server. Here's the stupid question (obviously I don't have much admin experience with multi-Exchange server orgs): How does mail destined for a mailbox on the new Exch 2003 server get to that server? Port 25 is open to the original server and t...

How do I protect my comments from being edited by another user?
I regularly send excel worksheets to a co-worker via e-mail and then she sends it out to others within the company (sales force). There are occasions when she will need to revise it and send it back to me. How do I protect my comments so that they cannot be edited, or can I? I received a worksheet back today that the comments I had entered were changed. These comments are my back-up and I do not want them changed. Can anybody help? highlight the whole sheet format cells / protection / uncheck locked highlight the cells you want protected format cells / protection / check locked tool...

problem with pivot table
Hello all: I am trying to make a pivot table from my date. My data includes Names, Dates (which I formatted to month, "m" in the format cells option)and Amount due. In designing the pt, I have "Name" in the row field, "Date" in the columnn field and "Amount due" in the Data field. I want my data to be grouped as follows: Month 1 2 3 4 Name $100 $20 $30 $20 Instead I am getting more than 1 month column i.e. Month 1 1 2 2 2 3 Name $80 $20 $30 $20 Do you know why...

Payroll w/h tables
When can we expect new payroll withholding tables for Ohio? In December our gov announced a late year change repealing a prior payroll credit previously announced. We have been told that the withholding tables are not correct yet GP/Microsoft has not released new tables yet. When can these be expected since we are practically done with January now??? Thanks. According the information I have, there are no pending changes for OH. You may need to contact Support about this issue. -- Charles Allen, MVP "INC" wrote: > When can we expect new payroll withh...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Add a Word Document as a Tab in an Excel Document
I work on several documents that require both worksheets and written reports - being able to add a word document as a new tab in Excel would be a neat way of integrating the two into one file for storing/printing/emailing. Maybe... http://www.pcmag.com/article2/0,4149,5224,00.asp "Office Binder: Gone but Not Really" PC Magazine article, January 29, 2002 by M. David Stone on using Binder in Office XP Jim Cone San Francisco, USA "GoDamN" <GoDamN@discussions.microsoft.com> wrote in message news:5F814119-2FA1-4BA6-92EB-C524C8C3820C@microsoft.com... > I work on s...

CF to Row after CF is applied to Column
Can someone please help me? I've read through the postings and the help files but still can't seem to figure out how to do this. I've applied conditional formatting to the J column of my spreadsheet so that if there's any text listed the cell turns yellow. What I'd like is if not only the cell but the row, from A:O turned yellow also. How do you do that? I'm using 07. Any help in English walking me through the steps or a hint toward the appropriate post would be greatly appreciated. Cuz I aint figrin it out on myown! Thanks in advance! --Dax...

Why does MS Money sometimes add items up in split categories?!
I've used MS Money for many years. One of my biggest problems with it is that sometimes, for reasons I have NEVER been able to figure out, when entering the Split mode for itemizing a transaction, it will stop subtracting from the total entered, but will instead reset the total of the amount to $0.00 and add things up. Why does it do that?! I'm using MS Money Deluxe 2006, and it STILL does that sometimes. Rod I **think** it depends on whether you specified the transaction total when entering/editing this transaction. I.e., if you are editing/entering a transaction that star...

Requery one subform from another subform
Hi Groupies My Main form (frmProjectMaster) contains 2 levels of subforms. The first subform is frmCompartmentsWizard. frmCompartmentsWizard contains fsubBlocksWizard and fsubReefersWizard. On fsubBlocksWizard, there is a combo box called strStockCode that gets its data from tblReefers. tblReefers happens to be the data source for fsubReefersWizard. What I need to happen is this: When a new record is added in fsubReefersWizard, I need the combo box on fsubBlocksWizard to requery so that the new data shows up. I need to stay in fsubReefersWizard. I am having trouble figuring ou...

how to jump to the next column with "enter" in excel?
Hi, The "tab" key is not an option? "dragos" <dragos@discussions.microsoft.com> wrote in message news:076B9F2B-AE6B-4DCD-96ED-6B5AAE35DC4B@microsoft.com... > ...

Add-ins #5
Hello, I have several macros that use the SumByColors function created by: http://www.cpearson.com/excel/colors.htm This allows me to sum by color. I have set this up as an Add-in used by several individuals. When I run one of my macros that uses this function and then save the file to my network drive the file continues to link to my C: drive where the Add-in is stored. When other users (who also have the Add-in installed on their C: drive) access this file from our network drive the formulas for the SumByColor function is still linked to my C: drive. How can I set up the macro or...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

Help using Tables
Hi guys! I need some help. I have a table that has a percentage range. Like this. Less than 90% 0 90.0% 90.9% $ 2,625 91.0% 91.9% $ 3,413 92.0% 92.9% $ 4,200 93.0% 93.9% $ 4,988 94.0% 94.9% $ 5,775 95.0% 95.9% $ 6,563 96.0% 96.9% $ 7,350 97.0% 97.9% $ 8,138 98.0% 98.9% $ 8,925 99.0% 99.9% $ 9,713 100.0% 100.9% $ 10,500 101...

league table #4
Sorry peeps email is smurfsix@hotmail.com ...

How To Add Rows and Cells in The Table Using IHTMLTable
Hello Everyone, I am currently working on the DHTML Dialog Based Application. I have been stucked due to a problem, which is that my HTML Page contains a HTML Table element and a button, i want my application to add rows and cells on the click event of that button. I have captured the event of the button but i am unable to add rows and cells in the table. Can anyone tell me how to acheive that by using the IHTMLTable interface. Thanks in advance. From, Ghazanfar Ali. "Ghazanfar Ali" <ghazanfarali17@hotmail.com> wrote in message news:uMq8CiCjFHA.1416@TK2MSFTNGP09.phx.gb...

how to merge data from multiple columns to one column #2
I have first, middle, and last names in A, B, and C. I want to merge everything into A. Ho do I do that? In column D use the formula =A1&" "&B1&" "&C1 The " " would put in your spaces in between the names. Then go to column D & do Edit Copy, move to column A & do Edit Paste. Then you should be able to delete columns B, C & D. Hope that helps! >-----Original Message----- >I have first, middle, and last names in A, B, and C. I want to merge >everything into A. Ho do I do that? >. > =CONCATENATE (text1,text2,....

Grouping Same Data in column
Hi Guys, Using: Excel 2000 Issue: Is there a way to create groupings based on same values down particular column. Example: I have phone numbers listed down column L. But some of th numbers appear more than once. Can I automatically create groups base on the same phone numbers so it would be easier on the eye to follow. Hope this makes sense. TIA Jonas :cool -- Message posted from http://www.ExcelForum.com You could sort the list by the phone numbers, and use conditional formatting to highlight the rows that contain a duplicate phone number: Select a cell in the list Choose Dat...

Views using linked tables
Hi! On my own sqlDB, I need to link tables form other sqlDB in the same machine and then, create views that combine fields of my own tables and linked tables. Please give some suggestions. Thanks LAM LAM (luism0@(arroba)yahoo.es) writes: > On my own sqlDB, I need to link tables form other sqlDB in the same > machine and then, create views that combine fields of my own tables and > linked tables. Please give some suggestions. CREATE VIEW myview AS SELECT ... FROM localtable JOIN otherdb.dbo.othertable ON That is, you refer to the tables in...

How to get combo box selected index changed event in another page from the existing page in winforms
Hi, I am working on windows forms project. I have two winforms (window1 and window2). I have a combobox called cmbEmail in window1 and Textbox called txtName in window2. I want to retrieve some information into the txtName in window2 when I selected something from cmbEmail in window1. If I write code to retrieve something into the txtName (which is in window2) in cmbEmail SelectedIndexchanged event in window1, I will get an error of txtName doesn't exist in this page. Can anybody help me to solve this problem.. Thanks and Regards Naresh "Naresh" &...