How do I copy a table to a new table?

[johansf via AccessMonster.com]

I want to copy some fields from one table to another table when they have
been updated.

I found below sql and put it in a the query but its is not working.

UPDATE Contacts RIGHT JOIN Extra contacts ON Contacts.Contact ID = Extra
contacts.Contact ID
SET Contacts.Contact ID = [Extra contacts]![Contact ID],
Contacts.HD ID = [Extra Contact ID]![HD ID],
Contacts.Primary contact = [Extra Contact ID]![Primary contact],
Contacts.First Name = [Extra Contact ID]![First Name],
Contacts.Last Name = [Extra Contact ID]![Last Name],
Contacts.OwnerID = [Extra Contact ID]![OwnerID],
Contacts.Country = [Extra Contact ID]![Country],
Contacts.Title = [Extra Contact ID]![Title],
Contacts.Work Phone = [Extra Contact ID]![Work Phone],
Contacts.Mobile Phone = [Extra Contact ID]![Mobile Phone],
Contacts.Email Name = [Extra Contact ID]![Email Name]


I get an error message:

syntax error (missing operator) in query expression 'Contacts.Contact ID =
Extra contacts.Contact ID'

 

[Golfinray]

Read the help files on update and append queries. One of those should do it
for you.

 

[John Spencer]

Your table name and field names have spaces in them, therefore you are
REQUIRED to use square brackets around the table name and field names.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.


UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.Contact ID = [Extra contacts].[Contact ID]

SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contact ID]![HD ID],
Contacts.[Primary contac]t = [Extra Contact ID]![Primary contact],
Contacts.[First Name] = [Extra Contact ID]![First Name],
Contacts.[Last Name] = [Extra Contact ID]![Last Name],
Contacts.OwnerID = [Extra Contact ID]![OwnerID],
Contacts.Country = [Extra Contact ID]![Country],
Contacts.Title = [Extra Contact ID]![Title],
Contacts.[Work Phone] = [Extra Contact ID]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contact ID]![Mobile Phone],
Contacts.[Email Name] = [Extra Contact ID]![Email Name]

BY the way, I think Access will create new records for you with this query
where there is no matching record in Contacts. Is that what you want?


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[johansf via AccessMonster.com]

Thanks a lot John.

Only I have one extra question. Why is it asking all the parameter values of
my fields if I run the query?

Your table name and field names have spaces in them, therefore you are
REQUIRED to use square brackets around the table name and field names.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.Contact ID = [Extra contacts].[Contact ID]

SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contact ID]![HD ID],
Contacts.[Primary contac]t = [Extra Contact ID]![Primary contact],
Contacts.[First Name] = [Extra Contact ID]![First Name],
Contacts.[Last Name] = [Extra Contact ID]![Last Name],
Contacts.OwnerID = [Extra Contact ID]![OwnerID],
Contacts.Country = [Extra Contact ID]![Country],
Contacts.Title = [Extra Contact ID]![Title],
Contacts.[Work Phone] = [Extra Contact ID]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contact ID]![Mobile Phone],
Contacts.[Email Name] = [Extra Contact ID]![Email Name]

BY the way, I think Access will create new records for you with this query
where there is no matching record in Contacts. Is that what you want?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I want to copy some fields from one table to another table when they have
been updated.

[quoted text clipped - 19 lines]

syntax error (missing operator) in query expression 'Contacts.Contact ID =
Extra contacts.Contact ID'

 

[John Spencer]

If you are getting parameter prompts then the query is not recognizing
the table or field names.

Looking at the posted SQL I see why. In one place you called the table
Extra Contacts, but then in the update portion you refer to the table as
Extra Contact ID.

UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.[Contact ID] = [Extra contacts].[Contact ID]
SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contacts]![HD ID],
Contacts.[Primary contact] = [Extra Contacts]![Primary contact],
Contacts.[First Name] = [Extra Contacts]![First Name],
Contacts.[Last Name] = [Extra Contacts]![Last Name],
Contacts.OwnerID = [Extra Contacts]![OwnerID],
Contacts.Country = [Extra Contacts]![Country],
Contacts.Title = [Extra Contacts]![Title],
Contacts.[Work Phone] = [Extra Contacts]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contacts]![Mobile Phone],
Contacts.[Email Name] = [Extra Contacts]![Email Name]


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks a lot John.

Only I have one extra question. Why is it asking all the parameter values of
my fields if I run the query?

Your table name and field names have spaces in them, therefore you are
REQUIRED to use square brackets around the table name and field names.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.Contact ID = [Extra contacts].[Contact ID]

SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contact ID]![HD ID],
Contacts.[Primary contac]t = [Extra Contact ID]![Primary contact],
Contacts.[First Name] = [Extra Contact ID]![First Name],
Contacts.[Last Name] = [Extra Contact ID]![Last Name],
Contacts.OwnerID = [Extra Contact ID]![OwnerID],
Contacts.Country = [Extra Contact ID]![Country],
Contacts.Title = [Extra Contact ID]![Title],
Contacts.[Work Phone] = [Extra Contact ID]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contact ID]![Mobile Phone],
Contacts.[Email Name] = [Extra Contact ID]![Email Name]

BY the way, I think Access will create new records for you with this query
where there is no matching record in Contacts. Is that what you want?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I want to copy some fields from one table to another table when they have
been updated.

[quoted text clipped - 19 lines]

syntax error (missing operator) in query expression 'Contacts.Contact ID =
Extra contacts.Contact ID'

 

[John W. Vinson/MVP]

If you are getting parameter prompts then the query is not recognizing
the table or field names.

Looking at the posted SQL I see why. In one place you called the table
Extra Contacts, but then in the update portion you refer to the table as
Extra Contact ID.

UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.[Contact ID] = [Extra contacts].[Contact ID]
SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contacts]![HD ID],
Contacts.[Primary contact] = [Extra Contacts]![Primary contact],
Contacts.[First Name] = [Extra Contacts]![First Name],
Contacts.[Last Name] = [Extra Contacts]![Last Name],
Contacts.OwnerID = [Extra Contacts]![OwnerID],
Contacts.Country = [Extra Contacts]![Country],
Contacts.Title = [Extra Contacts]![Title],
Contacts.[Work Phone] = [Extra Contacts]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contacts]![Mobile Phone],
Contacts.[Email Name] = [Extra Contacts]![Email Name]

John, shouldn't this SQL be using . as a delimiter rather than ! ?
I've used the bang delimiter for form and control references, but not
for fieldnames in tables. Does it work here as well?

 

[John Spencer]

Yes, I would normally use the period. It will work with the bang in
most cases. I was just too lazy to make one more correction to the SQL
statement.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

If you are getting parameter prompts then the query is not recognizing
the table or field names.

Looking at the posted SQL I see why. In one place you called the table
Extra Contacts, but then in the update portion you refer to the table as
Extra Contact ID.

UPDATE Contacts RIGHT JOIN [Extra contacts]
ON Contacts.[Contact ID] = [Extra contacts].[Contact ID]
SET Contacts.[Contact ID] = [Extra contacts]![Contact ID],
Contacts.[HD ID] = [Extra Contacts]![HD ID],
Contacts.[Primary contact] = [Extra Contacts]![Primary contact],
Contacts.[First Name] = [Extra Contacts]![First Name],
Contacts.[Last Name] = [Extra Contacts]![Last Name],
Contacts.OwnerID = [Extra Contacts]![OwnerID],
Contacts.Country = [Extra Contacts]![Country],
Contacts.Title = [Extra Contacts]![Title],
Contacts.[Work Phone] = [Extra Contacts]![Work Phone],
Contacts.[Mobile Phone] = [Extra Contacts]![Mobile Phone],
Contacts.[Email Name] = [Extra Contacts]![Email Name]

John, shouldn't this SQL be using . as a delimiter rather than ! ?
I've used the bang delimiter for form and control references, but not
for fieldnames in tables. Does it work here as well?

 

[John W. Vinson/MVP]

Yes, I would normally use the period. It will work with the bang in
most cases. I was just too lazy to make one more correction to the SQL
statement.

<g> I'll have to try the ! syntax... didn't realize it would work!