move field into another table 1

[mazdaman]

Is there a way of moving a field from one table to another ?
i.e. (in short)
Table 1= surname, telephone, address
Table 2=email,firstname

I want to move all the 'firstname' field contents from table 2 ( 11,000 records)into table 1 but aligned to the right surname etc. They have a common field 'PersonID).

Cheers

 

[dhookom]

If PersonID is the primary key in at least one table then you should be able to add a firstname field in table1 and create an update query with both tables and set Table1.FirstName = Table2.FirstName.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BNPMike]

aligned to the right surname

It is useful to remember relations do not have position either in row order or column order. I know Access does, but you don't need to be tainted by that. Put the new field whereever you like in the table and then display the name by creating a query which positions and manipulates the base data to give you the desired output.

 

[mazdaman]

cheers dhookom ! Can you help me with the update query not sure what to do - Im into ASP and need to do this for a projetc. Thank You

Thanks BNPMike - im writing ASP based insert queries and this exercise will make this easier. Cheers

 

[dhookom]

How about answering my query about primary keys as well as providing your table and field names?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A starting point:
SELECT DISTINCT A.*, B.firstname
FROM [Table 1] AS A INNER JOIN [Table 2] AS B ON A.PersonID = B.PersonID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mazdaman]

hi dhookom - the primary key is PersonID in the recieving table (table 1 ) ...

Thank you PHV - having a look at your idea, cheers

 

[dhookom]

PersonID seems to have been missing from your first posting. Also, you don't have a PersonID field (or other foreign key field) in table2.

How do you expect us to give you directions when we don't know where you are and it isn't clear where you are going?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mazdaman]

Sorry dhhookom - here are the full fields for both tables. Just thought I would summerise it.. here goes
-------------------------------------------------------
Table (table 1)= addressbook (the table I want to add 'Forename' to)
addressbook.PersonID =(primaray key, auto number)
addressbook.Surname
addressbook.intials
addressbook.Sex
addressbook.ServiceNo

---------------------------------------------------------
Table (table 2) = [addressbook extra]
[addressbook extra].PersonID(primaray key, auto number)
[addressbook extra].Forename
[addressbook extra].HomeTel
[addressbook extra].CS
[addressbook extra].YM
[addressbook extra].YMRS
---------------------------------------------------------
There is a one to one relationship between the PersonIDs.

As you can see I would like the 'Forename' moved into Table 1
Cheers ....

 

[dhookom]

Add a ForeName field to AddressBook and then create a query with a sql view of:
UPDATE addressbook INNER JOIN [addressbook extra] ON addressbook.PersonID = [addressbook extra].PersonID SET addressbook.ForeName = [addressbook extra].[ForeName];


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[steven290]

keep it simple. create the forename field then

update table1,table2
set table1.forename=table2.forename
where table1.id=table2.id

 

[mazdaman]

Thanks Chaps .
dhookom ... your code worked fantastic, your my hero.

steven290 ... cheers for your help - this seemed to give a little trouble because there where no joins - dont know why, Access told me. Thank anyway

Thanks guys