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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
Click to expand...

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 said:
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.
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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 said:
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.
Click to expand...

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]
Click to expand...
 
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 said:
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.
Click to expand...

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]
Click to expand...
 
John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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 said:
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.
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

:-{( You had a backup I hope!
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?
Click to expand...

One problem is that you have nonstandard characters - hyphens and
(even worse) quotemarks - in some of your fieldnames: Non-"Comppay"
for example. Try putting ALL your fieldnames in square brackets.

If that doesn't help, I've heard of bugs with table and fieldnames
containing hyphens. I hate to suggest it, but maybe you could simplify
your table names (storing a date in a tablename is Bad Design in any
case).

John W. Vinson[MVP]
 
Hi John,

I cleared the query and started clean using your method. IT WORKED! ! ! ! !

Maybe a night's sleep help.

Thanks for your patients and help! ! ! ! ! !

John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
I cleared the query and started clean using your method. IT WORKED! ! ! ! !

Maybe a night's sleep help.

Thanks for your patients and help! ! ! ! ! !
Click to expand...

You're welcome - but my doctorate is in chemistry, not medicine, and I
don't HAVE any patients! <bg>

John W. Vinson[MVP]
 
Back
Top