Query Append or Update

  • Thread starter Thread starter gjameson via AccessMonster.com
  • Start date Start date
G

gjameson via AccessMonster.com

I am trying to copy the contents of 2 tables into another table. This will
be a one time thing.

What I have is a table with customer data - Customer

2 other tables GS Config and GS EVC.
Each of these table have about 1200 records. All three tables have a primary
key that is the same between each table. I want to take the data from the 2
tables (GS Config and GS EVC) and put it into the main table (Customer)just
addding the additional fields , not records, without adding
additional/duplicate records. I added the same fields/definitions that I want
to import to Customer for each of the other tables.

So far I have been only been able to get the info to append to the end of the
table, creating another 1200 or so records. What am I missing here?


Thanks in advance for any help.

Gerald
 
gjameson via AccessMonster.com said:
I am trying to copy the contents of 2 tables into another table. This will
be a one time thing.

What I have is a table with customer data - Customer

2 other tables GS Config and GS EVC.
Each of these table have about 1200 records. All three tables have a
primary
key that is the same between each table. I want to take the data from the
2
tables (GS Config and GS EVC) and put it into the main table
(Customer)just
addding the additional fields , not records, without adding
additional/duplicate records. I added the same fields/definitions that I
want
to import to Customer for each of the other tables.

So far I have been only been able to get the info to append to the end of
the
table, creating another 1200 or so records. What am I missing here?
Click to expand...

In Access SQL (different from standard SQL) if there's no exact match of
keys,
an update query will append the data to the existing table otherwise it will
update
existing records. Are your keys of the same data type?

Tom Lake
 
Yes they are both numeric, do they have to have the same field name?

Tom said:
I am trying to copy the contents of 2 tables into another table. This will
be a one time thing.
Click to expand...
[quoted text clipped - 16 lines]
the
table, creating another 1200 or so records. What am I missing here?
Click to expand...

In Access SQL (different from standard SQL) if there's no exact match of
keys,
an update query will append the data to the existing table otherwise it will
update
existing records. Are your keys of the same data type?

Tom Lake
Click to expand...
 
Post the query you are using.

I would use TWO queries to do this, just for safety.

The query should look something like the following.
UPDATE Customer Inner Join GSConfig
On Customer.IDField = GsConfig.IdField
Set Customer.SomeField = Gs.SomeField
 
Here is my query now.

UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Working].UIBMINV_SERIAL_NUM SET [GS Config Working].UIBMINV_SERIAL_NUM =
"Customer", [GS Config Working].UCBPREM_STREET_NUMBER = "Customer", [GS
Config Working].UCBPREM_STREET_NAME = "Customer", [GS Config Working].
UCBPREM_CODE = "Customer", [GS Config Working].UCRSERV_CUST_CODE = "Customer",
[GS Config Working].UIBMINV_MODEL = "Customer", [GS Config Working].
UIBMINV_CODE = "Customer", [GS Config Working].UCRSERV_SRAT_CODE = "Customer",
[GS Config Working].UCRSERV_DATE_INSTALLED = "Customer", [GS Config Working].
UCRSERV_STATUS_IND = "Customer";

Also getting key violations now. :)

John said:
Post the query you are using.

I would use TWO queries to do this, just for safety.

The query should look something like the following.
UPDATE Customer Inner Join GSConfig
On Customer.IDField = GsConfig.IdField
Set Customer.SomeField = Gs.SomeField
I am trying to copy the contents of 2 tables into another table. This will
be a one time thing.
Click to expand...
[quoted text clipped - 20 lines]
Gerald
Click to expand...
Click to expand...
 
Changed my query to this
UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Working].UIBMINV_SERIAL_NUM SET [GS Config Working].UIBMINV_SERIAL_NUM =
"Customer", [GS Config Working].UCBPREM_STREET_NUMBER = "[Customer].
UCBPREM_STREET_NUMBER", [GS Config Working].UCBPREM_STREET_NAME = "[Customer].
UCBPREM_STREET_NAME", [GS Config Working].UCBPREM_CODE = "[Customer].
UCBPREM_CODE", [GS Config Working].UCRSERV_CUST_CODE = "[Customer].
UCRSERV_CUST_CODE", [GS Config Working].UIBMINV_MODEL = "[Customer].
UIBMINV_MODEL", [GS Config Working].UIBMINV_CODE = "[Customer].UIBMINV_CODE",
[GS Config Working].UCRSERV_SRAT_CODE = "[Customer].UCRSERV_SRAT_CODE", [GS
Config Working].UCRSERV_DATE_INSTALLED = "[Customer].UCRSERV_DATE_INSTALLED",
[GS Config Working].UCRSERV_STATUS_IND = "[Customer].UCRSERV_STATUS_IND";

Still getting key violations.
Here is my query now.

UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Working].UIBMINV_SERIAL_NUM SET [GS Config Working].UIBMINV_SERIAL_NUM =
"Customer", [GS Config Working].UCBPREM_STREET_NUMBER = "Customer", [GS
Config Working].UCBPREM_STREET_NAME = "Customer", [GS Config Working].
UCBPREM_CODE = "Customer", [GS Config Working].UCRSERV_CUST_CODE = "Customer",
[GS Config Working].UIBMINV_MODEL = "Customer", [GS Config Working].
UIBMINV_CODE = "Customer", [GS Config Working].UCRSERV_SRAT_CODE = "Customer",
[GS Config Working].UCRSERV_DATE_INSTALLED = "Customer", [GS Config Working].
UCRSERV_STATUS_IND = "Customer";

Also getting key violations now. :)
Post the query you are using.
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
In the SET clause of the SQL
--REMOVE the double quotes around the field and table names and enclose them
in brackets in the format [TableName].[FieldName]
--Don't update the key field UBIMINB_Serial_Num (you already have that value
in the table since you are matching on it in the join clause.

UPDATE Customer INNER JOIN [GS Config Working]
ON Customer.ID = [GS Config Working].[UIBMINV_SERIAL_NUM ]
SET [GS Config Working].UCBPREM_STREET_NUMBER =
[Customer].[UCBPREM_STREET_NUMBER]
, [GS Config Working].UCBPREM_STREET_NAME = [Customer].[UCBPREM_STREET_NAME]
, [GS Config Working].UCBPREM_CODE = [Customer].[UCBPREM_CODE]
, [GS Config Working].UCRSERV_CUST_CODE = [Customer].[UCRSERV_CUST_CODE]
, [GS Config Working].UIBMINV_MODEL = [Customer].[UIBMINV_MODEL]
, [GS Config Working].UIBMINV_CODE = [Customer].[UIBMINV_CODE]
, GS Config Working].UCRSERV_SRAT_CODE = [Customer].[UCRSERV_SRAT_CODE]
, [GS Config Working].UCRSERV_DATE_INSTALLED =
[Customer].[UCRSERV_DATE_INSTALLED]
, [GS Config Working].UCRSERV_STATUS_IND = [Customer].[UCRSERV_STATUS_IND]

gjameson via AccessMonster.com said:
Changed my query to this
UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Working].UIBMINV_SERIAL_NUM SET [GS Config Working].UIBMINV_SERIAL_NUM =
"Customer", [GS Config Working].UCBPREM_STREET_NUMBER = "[Customer].
UCBPREM_STREET_NUMBER", [GS Config Working].UCBPREM_STREET_NAME =
"[Customer].
UCBPREM_STREET_NAME", [GS Config Working].UCBPREM_CODE = "[Customer].
UCBPREM_CODE", [GS Config Working].UCRSERV_CUST_CODE = "[Customer].
UCRSERV_CUST_CODE", [GS Config Working].UIBMINV_MODEL = "[Customer].
UIBMINV_MODEL", [GS Config Working].UIBMINV_CODE =
"[Customer].UIBMINV_CODE",
[GS Config Working].UCRSERV_SRAT_CODE = "[Customer].UCRSERV_SRAT_CODE",
[GS
Config Working].UCRSERV_DATE_INSTALLED =
"[Customer].UCRSERV_DATE_INSTALLED",
[GS Config Working].UCRSERV_STATUS_IND = "[Customer].UCRSERV_STATUS_IND";

Still getting key violations.
Here is my query now.

UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Working].UIBMINV_SERIAL_NUM SET [GS Config Working].UIBMINV_SERIAL_NUM =
"Customer", [GS Config Working].UCBPREM_STREET_NUMBER = "Customer", [GS
Config Working].UCBPREM_STREET_NAME = "Customer", [GS Config Working].
UCBPREM_CODE = "Customer", [GS Config Working].UCRSERV_CUST_CODE =
"Customer",
[GS Config Working].UIBMINV_MODEL = "Customer", [GS Config Working].
UIBMINV_CODE = "Customer", [GS Config Working].UCRSERV_SRAT_CODE =
"Customer",
[GS Config Working].UCRSERV_DATE_INSTALLED = "Customer", [GS Config
Working].
UCRSERV_STATUS_IND = "Customer";

Also getting key violations now. :)
Post the query you are using.
Click to expand...
[quoted text clipped - 10 lines]
Gerald
Click to expand...
Click to expand...
Click to expand...
 
I tried your suggestion. It does not fail on key violation now, butthe data
from GS Config Working did not make it into Customer and also he data in GS
Config Working disappered from the table.?

John said:
In the SET clause of the SQL
--REMOVE the double quotes around the field and table names and enclose them
in brackets in the format [TableName].[FieldName]
--Don't update the key field UBIMINB_Serial_Num (you already have that value
in the table since you are matching on it in the join clause.

UPDATE Customer INNER JOIN [GS Config Working]
ON Customer.ID = [GS Config Working].[UIBMINV_SERIAL_NUM ]
SET [GS Config Working].UCBPREM_STREET_NUMBER =
[Customer].[UCBPREM_STREET_NUMBER]
, [GS Config Working].UCBPREM_STREET_NAME = [Customer].[UCBPREM_STREET_NAME]
, [GS Config Working].UCBPREM_CODE = [Customer].[UCBPREM_CODE]
, [GS Config Working].UCRSERV_CUST_CODE = [Customer].[UCRSERV_CUST_CODE]
, [GS Config Working].UIBMINV_MODEL = [Customer].[UIBMINV_MODEL]
, [GS Config Working].UIBMINV_CODE = [Customer].[UIBMINV_CODE]
, GS Config Working].UCRSERV_SRAT_CODE = [Customer].[UCRSERV_SRAT_CODE]
, [GS Config Working].UCRSERV_DATE_INSTALLED =
[Customer].[UCRSERV_DATE_INSTALLED]
, [GS Config Working].UCRSERV_STATUS_IND = [Customer].[UCRSERV_STATUS_IND]
Changed my query to this
UPDATE Customer INNER JOIN [GS Config Working] ON Customer.ID = [GS Config
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
Thank you John! After studing this a little more all I had to do was switch
GS Config Working and Customer in my statements. Works great! Thanks all for
the help!

Gerald
I tried your suggestion. It does not fail on key violation now, butthe data
from GS Config Working did not make it into Customer and also he data in GS
Config Working disappered from the table.?
In the SET clause of the SQL
--REMOVE the double quotes around the field and table names and enclose them
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Query Issue 0
Query Needed 5
Append but no table? 2
Append query without duplicate rows 3
VBA - retrieve data on last create named range from previous inserted named range 0
data append & overwrite by 1 step 4
Access 2010 append query to SQL is very slow 0
append query question in Access97 1

Back
Top