Append Additional Data Into New Database

[hspgapsemc]

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[Golfinray]

All an append query does is add extra rows of data. If you want to add the
data to the existing data, use an update query.

 

[KARL DEWEY]

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:

 

[hspgapsemc]

Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[Golfinray]

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[hspgapsemc]

I've tried again running the updates one at a time, but it still does not work.

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[KARL DEWEY]

Open you query in design view and then SQL View, highlight all, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

I've tried again running the updates one at a time, but it still does not work.

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You


:

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[hspgapsemc]

UPDATE [2009 HED Electronic Data Version 4] INNER JOIN
Gapsemc2007datato2009hep ON [2009 HED Electronic Data Version 4].[FICE
Number] = Gapsemc2007datato2009hep.[FICE Number] SET [2009 HED Electronic
Data Version 4].[GAPSEMC Carnegie Class] =
[gapsemc2007datato2009hep].[gapsemc carnegie class];

Open you query in design view and then SQL View, highlight all, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

I've tried again running the updates one at a time, but it still does not work.

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

:


Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You


:

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[hspgapsemc]

Here is the SQL view of my quary. Thanks.

UPDATE [2009 HED Electronic Data Version 4] INNER JOIN
Gapsemc2007datato2009hep ON [2009 HED Electronic Data Version 4].[FICE
Number] = Gapsemc2007datato2009hep.[FICE Number] SET [2009 HED Electronic
Data Version 4].[GAPSEMC Carnegie Class] =
[gapsemc2007datato2009hep].[gapsemc carnegie class];

Open you query in design view and then SQL View, highlight all, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

I've tried again running the updates one at a time, but it still does not work.

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

:


Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You


:

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[KARL DEWEY]

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

You must have multiple records with the same [FICE Number] and that makes
for a Cartesian effect.
If all [gapsemc2007datato2009hep].[FICE Number] have the same
[gapsemc2007datato2009hep].[gapsemc carnegie class] you should not have a
problem.
You can check by running a totals query on fields
[gapsemc2007datato2009hep].[FICE Number] and
[gapsemc2007datato2009hep].[gapsemc carnegie class] followed by a totals
query grouping on [gapsemc2007datato2009hep].[FICE Number] and counting
[gapsemc2007datato2009hep].[gapsemc carnegie class] with criteria >1 to see
if a single [gapsemc2007datato2009hep].[FICE Number] have multiple
[gapsemc2007datato2009hep].[gapsemc carnegie class].
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

--
KARL DEWEY
Build a little - Test a little

Here is the SQL view of my quary. Thanks.

UPDATE [2009 HED Electronic Data Version 4] INNER JOIN
Gapsemc2007datato2009hep ON [2009 HED Electronic Data Version 4].[FICE
Number] = Gapsemc2007datato2009hep.[FICE Number] SET [2009 HED Electronic
Data Version 4].[GAPSEMC Carnegie Class] =
[gapsemc2007datato2009hep].[gapsemc carnegie class];

Open you query in design view and then SQL View, highlight all, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

I've tried again running the updates one at a time, but it still does not work.

:

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

:


Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You


:

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[hspgapsemc]

There are multiple records that have the same FICE number. There are also
multiple records that have the same Carnegie Class, although they may have
different FICE numbers. For any one FICE number, the Canegie class is the
same for each record. I'm trying to update the appropriate Carnegie class to
the appropriate FICE number from the old database to the new database
without having to re-enter the data onto the new database manually from
scratch. Can this even be done?

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

You must have multiple records with the same [FICE Number] and that makes
for a Cartesian effect.
If all [gapsemc2007datato2009hep].[FICE Number] have the same
[gapsemc2007datato2009hep].[gapsemc carnegie class] you should not have a
problem.
You can check by running a totals query on fields
[gapsemc2007datato2009hep].[FICE Number] and
[gapsemc2007datato2009hep].[gapsemc carnegie class] followed by a totals
query grouping on [gapsemc2007datato2009hep].[FICE Number] and counting
[gapsemc2007datato2009hep].[gapsemc carnegie class] with criteria >1 to see
if a single [gapsemc2007datato2009hep].[FICE Number] have multiple
[gapsemc2007datato2009hep].[gapsemc carnegie class].
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

--
KARL DEWEY
Build a little - Test a little

Here is the SQL view of my quary. Thanks.

UPDATE [2009 HED Electronic Data Version 4] INNER JOIN
Gapsemc2007datato2009hep ON [2009 HED Electronic Data Version 4].[FICE
Number] = Gapsemc2007datato2009hep.[FICE Number] SET [2009 HED Electronic
Data Version 4].[GAPSEMC Carnegie Class] =
[gapsemc2007datato2009hep].[gapsemc carnegie class];

Open you query in design view and then SQL View, highlight all, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

I've tried again running the updates one at a time, but it still does not work.

:

Try to just update one field and see if it will work. Sometimes that works
better for me. Trying to do them all at once there is too big an opportunity
to make an error.

:


Thank you for your response. I realize I should have used the word update
instead of append and I am using a copy of my original file to run these
tests.

I have about 85,000 base records. When I test my update query attempts,
access says it will update over 2,500,000 records. Of course, I don't run
the query.

I have tried different joins but none seem to work. There should be a way I
can accomplish what I want to do.

Thank You


:

It ain't an append. It is an update you need.
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
In a select query join the two tables on the account number, change the
query type to Append, put the old table field names in the Update To row of
the corresponding new table fields.
Looks like this --
Field: Field1 Field2 .... Field5
Table: NewTbl NewTbl ... NewTbl
Update To: [OldTbl].[Field1] [OldTbl].[Field2] ... [OldTbl].[Field5]
Criteria:
or:
--
KARL DEWEY
Build a little - Test a little


:

I have a database from an outside source that over time I have added 5
additional fields of data. Each record from the outside source has an
account number, but the same account number appears on more than one record.

I have received the new update from my outside source and want to add the 5
additional fields of data from my old database into the new database. The
new database continues the use of account numbers on each record.

I created a separate table from the old database containing my 5 fields of
data and the account number. I want to append the 5 fields of data into my
new table into the matching account number records.

When I try and run the append query all I get is x number of additional
rows. In other words, the data is just added to the end of the table.

I have tried different joins using the account number as the join field --
but it doesn't work. I know there is a way to do this, I'm just not getting
it. Thanks.

 

[KARL DEWEY]

For any one FICE number, the Canegie class is the same for each record
Good!
Backup the database and then do a Compact and Repair.

Use the totals query grouping on fields [gapsemc2007datato2009hep].[FICE
Number] and [gapsemc2007datato2009hep].[gapsemc carnegie class] and inner
join it to your Gapsemc2007datato2009hep.[FICE Number].

Doing this will eliminate the Cartesian effect.

 

[hspgapsemc]

Thank you!!! It worked.

Good!
Backup the database and then do a Compact and Repair.

Use the totals query grouping on fields [gapsemc2007datato2009hep].[FICE
Number] and [gapsemc2007datato2009hep].[gapsemc carnegie class] and inner
join it to your Gapsemc2007datato2009hep.[FICE Number].

Doing this will eliminate the Cartesian effect.