How do I update specific records in a Access table with data from.

I have received some field updates to some of the records in my primary
database. There are too many to key by hand. I want to know if there is an
update query or some other function that will allow me to update the fields.

The main table has over 110,000 records and I need to update fields in over
10,000 of the records.

Any suggestions would be very much appreciated.

Thanks,

Kerney

If there are specific criteria in each of the records that need to be
updated, you can run an update query (create a new query in design view, and
go to the menu bar, select Query Update Query). You can specify the
criteria, and then specify the field values to be changed.

Mike


"Kerney" wrote in message
...
I have received some field updates to some of the records in my primary
database. There are too many to key by hand. I want to know if there is
an
update query or some other function that will allow me to update the
fields.

The main table has over 110,000 records and I need to update fields in
over
10,000 of the records.

Any suggestions would be very much appreciated.

Thanks,

Kerney

On Mon, 24 Jan 2005 16:23:02 -0800, Kerney
wrote:

I have received some field updates to some of the records in my primary
database. There are too many to key by hand. I want to know if there is an
update query or some other function that will allow me to update the fields.

Of course. Access wouldn't be much of a database without action
queries!

You will, of course, need some way to link the table of updates (I'll
call it [Updates]) to the table to be updated ([Master]) in a way
which uniquely identifies the record to be updated. Hopefully this is
by the Primary Key of the target table.

If you do have such a join, create a Query joining the two tables.
Change it to an Update query, and put

[Updates].[Fieldname]

on the Update To row under the corresponding Fieldname of the Master
table.

THe SQL would be

UPDATE [Master] INNER JOIN [Updates]
ON [Master].[linkingfield] = [Updates].[Linkingfield]
SET [Master].[Fieldname] = [Updates].[Fieldname],
[Master].[anotherfield] = [Updates].[anotherfield],
.... etc

The names of the fields need not match, and even the datatypes might
not - it's pretty clever about automatically recasting fields to a new
datatype if necessary.

John W. Vinson[MVP]

Hi John,

Okay, when I run view, I see exactly what I want the main table to be
updated with but when I "run" the Update Query, it asks me to input the value
I want to update the table with. I don't know what I have wrong but it must
simple.

Here are a few facts:

The two table are linked by three separate field joins. In my case Injury
date, Store Number and Trans number. This gets me a unique match.

In the query, I pulled all the fields from the Master table and then changed
the "Table:" reference to the second table for the fields I want to update on
the master. When I ran the select query, I saw exactly what I wanted to
update the Master.

I then changed the query type to Update and went to the fields I wanted to
update and added [Updates].[Fieldname] (where the field name was my
fieldname). When I viewed the update query, I saw the results I wanted but
when I hit "Run" it asked me for input.

I then added the Master table name in front of the fieldname in the update
field (i.e., [Updates].[Master Table]![Fieldname]) and viewed the query and
it was fine but again, when I "Run" the query, I am asked for input.

What am I doing wrong?

Any help would really be appreciated.

Thanks,

Kerney

"John Vinson" wrote:

On Mon, 24 Jan 2005 16:23:02 -0800, Kerney
wrote:

I have received some field updates to some of the records in my primary
database. There are too many to key by hand. I want to know if there is an
update query or some other function that will allow me to update the fields.

Of course. Access wouldn't be much of a database without action
queries!

You will, of course, need some way to link the table of updates (I'll
call it [Updates]) to the table to be updated ([Master]) in a way
which uniquely identifies the record to be updated. Hopefully this is
by the Primary Key of the target table.

If you do have such a join, create a Query joining the two tables.
Change it to an Update query, and put

[Updates].[Fieldname]

on the Update To row under the corresponding Fieldname of the Master
table.

THe SQL would be

UPDATE [Master] INNER JOIN [Updates]
ON [Master].[linkingfield] = [Updates].[Linkingfield]
SET [Master].[Fieldname] = [Updates].[Fieldname],
[Master].[anotherfield] = [Updates].[anotherfield],
.... etc

The names of the fields need not match, and even the datatypes might
not - it's pretty clever about automatically recasting fields to a new
datatype if necessary.

John W. Vinson[MVP]

On Tue, 25 Jan 2005 11:13:03 -0800, Kerney
wrote:

In the query, I pulled all the fields from the Master table and then changed
the "Table:" reference to the second table for the fields I want to update on
the master. When I ran the select query, I saw exactly what I wanted to
update the Master.

I then changed the query type to Update and went to the fields I wanted to
update and added [Updates].[Fieldname] (where the field name was my
fieldname). When I viewed the update query, I saw the results I wanted but
when I hit "Run" it asked me for input.

I then added the Master table name in front of the fieldname in the update
field (i.e., [Updates].[Master Table]![Fieldname]) and viewed the query and
it was fine but again, when I "Run" the query, I am asked for input.

Please open the Query in SQL view and post it here. I think you may
have the joins wrong, but without seeing the query itself I can't
really tell.

John W. Vinson[MVP]

Hi John,

The "Open with 12-14-04 Modifications" is the Master table and the "Open Non
Comppay 011105 Updates" is the Update table.

It is pretty big but here it is:

UPDATE [Open with 12-14-04 Modifications] INNER JOIN [Open Non Comppay
011105 Updates] ON ([Open with 12-14-04 Modifications].FillDate = [Open Non
Comppay 011105 Updates].FILLDATE) AND ([Open with 12-14-04
Modifications].NCPDP = [Open Non Comppay 011105 Updates].NCPDP) AND ([Open
with 12-14-04 Modifications].RxNumber = [Open Non Comppay 011105
Updates].RXNUMBER) SET [Open Non Comppay 011105 Updates].CorrectClaim =
[Updates].[Open with 12-14-04 Modifications]![CorrectClaim], [Open Non
Comppay 011105 Updates].NYSIFUnit = [Updates].[Open with 12-14-04
Modifications]![NYSIFUnit], [Open Non Comppay 011105 Updates].[Non-COMPPAY] =
[Updates].[Open with 12-14-04 Modifications]![Non-Comppay], [Open Non Comppay
011105 Updates].COMPPAYApport = [Updates].[Open with 12-14-04
Modifications]![ComppayApport], [Open Non Comppay 011105 Updates].[Appt Date
1 Prior To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 1
Prior To Flag], [Open Non Comppay 011105 Updates].AportDate1 =
[Updates].[Open with 12-14-04 Modifications]![AportDate1], [Open Non Comppay
011105 Updates].InjuryDt1 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt1], [Open Non Comppay 011105 Updates].Carrier1 =
[Updates].[Open with 12-14-04 Modifications]![Carrier1], [Open Non Comppay
011105 Updates].CarrierCase1 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase1], [Open Non Comppay 011105 Updates].Percent1 =
[Updates].[Open with 12-14-04 Modifications]![Percent1], [Open Non Comppay
011105 Updates].WCBNo1 = [Updates].[Open with 12-14-04
Modifications]![WCBNo1], [Open Non Comppay 011105 Updates].[Appt Date 2 Prior
To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 2 Prior To
Flag], [Open Non Comppay 011105 Updates].AportDate2 = [Updates].[Open with
12-14-04 Modifications]![AportDate2], [Open Non Comppay 011105
Updates].InjuryDt2 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt2], [Open Non Comppay 011105 Updates].Carrier2 =
[Updates].[Open with 12-14-04 Modifications]![Carrier2], [Open Non Comppay
011105 Updates].CarrierCase2 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase2], [Open Non Comppay 011105 Updates].Percent2 =
[Updates].[Open with 12-14-04 Modifications]![Percent2], [Open Non Comppay
011105 Updates].WCBNo2 = [Updates].[Open with 12-14-04
Modifications]![WCBNo2], [Open Non Comppay 011105 Updates].[Appt Date 3 Prior
To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 3 Prior To
Flag], [Open Non Comppay 011105 Updates].AportDate3 = [Updates].[Open with
12-14-04 Modifications]![AportDate3], [Open Non Comppay 011105
Updates].InjuryDt3 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt3], [Open Non Comppay 011105 Updates].Carrier3 =
[Updates].[Open with 12-14-04 Modifications]![Carrier3], [Open Non Comppay
011105 Updates].CarrierCase3 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase3], [Open Non Comppay 011105 Updates].Percent3 =
[Updates].[Open with 12-14-04 Modifications]![Percent3], [Open Non Comppay
011105 Updates].WCBNo3 = [Updates].[Open with 12-14-04
Modifications]![WCBNo3];


I really appreciate the help!

Thanks,

Kerney



"John Vinson" wrote:

On Tue, 25 Jan 2005 11:13:03 -0800, Kerney
wrote:

In the query, I pulled all the fields from the Master table and then changed
the "Table:" reference to the second table for the fields I want to update on
the master. When I ran the select query, I saw exactly what I wanted to
update the Master.

I then changed the query type to Update and went to the fields I wanted to
update and added [Updates].[Fieldname] (where the field name was my
fieldname). When I viewed the update query, I saw the results I wanted but
when I hit "Run" it asked me for input.

I then added the Master table name in front of the fieldname in the update
field (i.e., [Updates].[Master Table]![Fieldname]) and viewed the query and
it was fine but again, when I "Run" the query, I am asked for input.

Please open the Query in SQL view and post it here. I think you may
have the joins wrong, but without seeing the query itself I can't
really tell.

John W. Vinson[MVP]

Hi John,

Here is the SQL. The "Open with 12-14-04 Modifications" is the master table
and the "Open Non Comppay 011105 Updates" is the update table.

UPDATE [Open with 12-14-04 Modifications] INNER JOIN [Open Non Comppay
011105 Updates] ON ([Open with 12-14-04 Modifications].FillDate = [Open Non
Comppay 011105 Updates].FILLDATE) AND ([Open with 12-14-04
Modifications].NCPDP = [Open Non Comppay 011105 Updates].NCPDP) AND ([Open
with 12-14-04 Modifications].RxNumber = [Open Non Comppay 011105
Updates].RXNUMBER) SET [Open Non Comppay 011105 Updates].CorrectClaim =
[Updates].[Open with 12-14-04 Modifications]![CorrectClaim], [Open Non
Comppay 011105 Updates].NYSIFUnit = [Updates].[Open with 12-14-04
Modifications]![NYSIFUnit], [Open Non Comppay 011105 Updates].[Non-COMPPAY] =
[Updates].[Open with 12-14-04 Modifications]![Non-Comppay], [Open Non Comppay
011105 Updates].COMPPAYApport = [Updates].[Open with 12-14-04
Modifications]![ComppayApport], [Open Non Comppay 011105 Updates].[Appt Date
1 Prior To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 1
Prior To Flag], [Open Non Comppay 011105 Updates].AportDate1 =
[Updates].[Open with 12-14-04 Modifications]![AportDate1], [Open Non Comppay
011105 Updates].InjuryDt1 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt1], [Open Non Comppay 011105 Updates].Carrier1 =
[Updates].[Open with 12-14-04 Modifications]![Carrier1], [Open Non Comppay
011105 Updates].CarrierCase1 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase1], [Open Non Comppay 011105 Updates].Percent1 =
[Updates].[Open with 12-14-04 Modifications]![Percent1], [Open Non Comppay
011105 Updates].WCBNo1 = [Updates].[Open with 12-14-04
Modifications]![WCBNo1], [Open Non Comppay 011105 Updates].[Appt Date 2 Prior
To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 2 Prior To
Flag], [Open Non Comppay 011105 Updates].AportDate2 = [Updates].[Open with
12-14-04 Modifications]![AportDate2], [Open Non Comppay 011105
Updates].InjuryDt2 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt2], [Open Non Comppay 011105 Updates].Carrier2 =
[Updates].[Open with 12-14-04 Modifications]![Carrier2], [Open Non Comppay
011105 Updates].CarrierCase2 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase2], [Open Non Comppay 011105 Updates].Percent2 =
[Updates].[Open with 12-14-04 Modifications]![Percent2], [Open Non Comppay
011105 Updates].WCBNo2 = [Updates].[Open with 12-14-04
Modifications]![WCBNo2], [Open Non Comppay 011105 Updates].[Appt Date 3 Prior
To Flag] = [Updates].[Open with 12-14-04 Modifications]![Appt Date 3 Prior To
Flag], [Open Non Comppay 011105 Updates].AportDate3 = [Updates].[Open with
12-14-04 Modifications]![AportDate3], [Open Non Comppay 011105
Updates].InjuryDt3 = [Updates].[Open with 12-14-04
Modifications]![InjuryDt3], [Open Non Comppay 011105 Updates].Carrier3 =
[Updates].[Open with 12-14-04 Modifications]![Carrier3], [Open Non Comppay
011105 Updates].CarrierCase3 = [Updates].[Open with 12-14-04
Modifications]![CarrierCase3], [Open Non Comppay 011105 Updates].Percent3 =
[Updates].[Open with 12-14-04 Modifications]![Percent3], [Open Non Comppay
011105 Updates].WCBNo3 = [Updates].[Open with 12-14-04
Modifications]![WCBNo3];

I really appreciate your help!

Thanks,

Kerney

"John Vinson" wrote:

On Tue, 25 Jan 2005 11:13:03 -0800, Kerney
wrote:

In the query, I pulled all the fields from the Master table and then changed
the "Table:" reference to the second table for the fields I want to update on
the master. When I ran the select query, I saw exactly what I wanted to
update the Master.

I then changed the query type to Update and went to the fields I wanted to
update and added [Updates].[Fieldname] (where the field name was my
fieldname). When I viewed the update query, I saw the results I wanted but
when I hit "Run" it asked me for input.

I then added the Master table name in front of the fieldname in the update
field (i.e., [Updates].[Master Table]![Fieldname]) and viewed the query and
it was fine but again, when I "Run" the query, I am asked for input.

Please open the Query in SQL view and post it here. I think you may
have the joins wrong, but without seeing the query itself I can't
really tell.

John W. Vinson[MVP]

"John Vinson" wrote:

On Tue, 25 Jan 2005 11:13:03 -0800, Kerney
wrote:

In the query, I pulled all the fields from the Master table and then changed
the "Table:" reference to the second table for the fields I want to update on
the master. When I ran the select query, I saw exactly what I wanted to
update the Master.

I then changed the query type to Update and went to the fields I wanted to
update and added [Updates].[Fieldname] (where the field name was my
fieldname). When I viewed the update query, I saw the results I wanted but
when I hit "Run" it asked me for input.

I then added the Master table name in front of the fieldname in the update
field (i.e., [Updates].[Master Table]![Fieldname]) and viewed the query and
it was fine but again, when I "Run" the query, I am asked for input.

Please open the Query in SQL view and post it here. I think you may
have the joins wrong, but without seeing the query itself I can't
really tell.

John W. Vinson[MVP]

On Tue, 25 Jan 2005 13:31:10 -0800, Kerney
wrote:

Hi John,

Here is the SQL. The "Open with 12-14-04 Modifications" is the master table
and the "Open Non Comppay 011105 Updates" is the update table.

yeouch! wide table! Ok, let's parse this out.

AHA. There's a nonexistant table named [Updates] which has sneaked
into the query, and a lot of ! where you should have . marks. And you
have the query set so that you're updating the Updates table from the
master table instead of vice versa. Try:

UPDATE [Open with 12-14-04 Modifications]
INNER JOIN [Open Non Comppay 011105 Updates]
ON ([Open with 12-14-04 Modifications].FillDate =
[Open Non Comppay 011105 Updates].FILLDATE)
AND ([Open with 12-14-04 Modifications].NCPDP =
[Open Non Comppay 011105 Updates].NCPDP)
AND ([Open with 12-14-04 Modifications].RxNumber =
[Open Non Comppay 011105 Updates].RXNUMBER)
SET
[Open with 12-14-04 Modifications].[CorrectClaim] =
[Open Non Comppay 011105 Updates].CorrectClaim,
[Open with 12-14-04 Modifications].[NYSIFUnit] =
[Open Non Comppay 011105 Updates].NYSIFUnit,

etc. etc.

In the query grid, you should have only those fields that you want to
update in [Open with 12-14-04 Modifications] included in the Fields
row; the Update To row should contain

[Open Non Comppay 011105 Updates].[CorrectClaim]

or whatever the corresponding field might be. There should not be
anything named [Updates] anywhere since that was *my* example, not
your tablename.

John W. Vinson[MVP]

Hi John,

Okay, I got the tables and fields fix the way you suggested and it worked.
I thought. It asked me if I wanted to update the rows and I said yes. The
result was - it deleted the data out of my Open Non Comppay 011105 Updates
table using the blank fields from my master table Open with 12-14-04
Modifications. I guess it was backwards.

I reversed the references and now it won't work again. It is asking for
input. I have copied a section of the altered SQL for your reference:

UPDATE [Open with 12-14-04 Modifications] INNER JOIN [Open Non Comppay
011105 Updates] ON ([Open with 12-14-04 Modifications].FillDate = [Open Non
Comppay 011105 Updates].FILLDATE) AND ([Open with 12-14-04
Modifications].NCPDP = [Open Non Comppay 011105 Updates].NCPDP) AND ([Open
with 12-14-04 Modifications].RxNumber = [Open Non Comppay 011105
Updates].RXNUMBER) SET [Open Non Comppay 011105 Updates].CorrectClaim = [Open
with 12-14-04 Modifications].CorrectClaim, [Open Non Comppay 011105
Updates].NYSIFUnit = [Open with 12-14-04 Modifications].NYSIFUnit, [Open Non
Comppay 011105 Updates].[Non-COMPPAY] = [Open with 12-14-04
Modifications].Non-"Comppay", [Open Non Comppay 011105 Updates].COMPPAYApport
= [Open with 12-14-04 Modifications].ComppayApport,

Am I just brain dead? If the reverse of this works but just against the
wrong table, why won't this code update the correct table?

Any additional insights would be greatly appreciated.

Thanks,

Kerney

"John Vinson" wrote:

On Tue, 25 Jan 2005 13:31:10 -0800, Kerney
wrote:

Hi John,

Here is the SQL. The "Open with 12-14-04 Modifications" is the master table
and the "Open Non Comppay 011105 Updates" is the update table.

yeouch! wide table! Ok, let's parse this out.

AHA. There's a nonexistant table named [Updates] which has sneaked
into the query, and a lot of ! where you should have . marks. And you
have the query set so that you're updating the Updates table from the
master table instead of vice versa. Try:

UPDATE [Open with 12-14-04 Modifications]
INNER JOIN [Open Non Comppay 011105 Updates]
ON ([Open with 12-14-04 Modifications].FillDate =
[Open Non Comppay 011105 Updates].FILLDATE)
AND ([Open with 12-14-04 Modifications].NCPDP =
[Open Non Comppay 011105 Updates].NCPDP)
AND ([Open with 12-14-04 Modifications].RxNumber =
[Open Non Comppay 011105 Updates].RXNUMBER)
SET
[Open with 12-14-04 Modifications].[CorrectClaim] =
[Open Non Comppay 011105 Updates].CorrectClaim,
[Open with 12-14-04 Modifications].[NYSIFUnit] =
[Open Non Comppay 011105 Updates].NYSIFUnit,

etc. etc.

In the query grid, you should have only those fields that you want to
update in [Open with 12-14-04 Modifications] included in the Fields
row; the Update To row should contain

[Open Non Comppay 011105 Updates].[CorrectClaim]

or whatever the corresponding field might be. There should not be
anything named [Updates] anywhere since that was *my* example, not
your tablename.

John W. Vinson[MVP]