Query Append or Update

  • Thread starter gjameson via AccessMonster.com
  • 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
 
T

Tom Lake

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
 
G

gjameson via AccessMonster.com

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...
 
J

John Spencer

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
 
G

gjameson via AccessMonster.com

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...
 
G

gjameson via AccessMonster.com

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...
 
J

John Spencer

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...
 
G

gjameson via AccessMonster.com

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...
 
G

gjameson via AccessMonster.com

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...
 
Top