Replace existing records in a table

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

Guest

Greetings from Pani. I would be very thankful if anyone can help me with my
question. I am trying to maintain a central database for a list of
responsibility centers that i have to maintain on a regular basis. For any
delete and addition, I populate it in an excel tabel first and use link
table and delete and append query to update my center table in Access.
However for any changes that should go in the center table, is there any way
I can still maintain an excel table, link it and use ????????(this is where i
need help)...to implement find and replace function with a click of a button
the way the delete and append query works. The replace would include many
different values in different rows with in the central table.
If there is a better way to address my concept of maintaining a central
table, please provide so.I appreciate your help.
I did all research, bougth a book in Access and finally am at your mercy.
 
Pani,

It may not be as difficult as it seems. I would suggest you try using an
Update Query. You would have have a unique value in your Excel sheet and
your Access table you can match on to make the replacements.

Good Luck.
 
Klatuu, Thank you for your response. I had sent a reply earlier and the
system did not take it so I am writting again. I think the update query would
only help to modify existing records with one particular value we put in the
update to field, right. However, my problem is, I have multiple fields that
needs to be updated in the 15000 records database. I maintain all the change
request sent to me by the customers in an excel format. and link it to Access
and would like to perform something like a lookup to find the unique value in
Access central db and find that in the excel file and if it is there then
change/update all the field to mirror the field in excel. As you said, I
might need a lot more good luck, before I get a solution.

Thank you, await the solution with the help of the forum participant.
 
Pani,

It may be more work than your deletes and appends, but it will be less work
than doing each change by hand.

Are you familiar with Update Queries? When you create an update query, you
identify the table and fields to be updated. You can choose all the fields
if needed. Then you identify the source (Update From), then you can identify
the criteria that determines whether a row in your table will be updated.
That is why I asked if there is a field or combination of fields that you can
compare to select the rows to be updated. It should be the same as the
primary key for the table.
 
Some how the Forum did not allow me to put more than two post yesterday.
Going back to my question. I do not have an option to select "Update From" in
my Update to query. I am using Access 2000. I will give it a try again and
research if I am missing anything. Thank you. Meanwhile, if you/anyone have
any other suggestion, please pass it to me. Thank you.
 
Pani,

Good Morning.
I also use Access 2000. Here is how you may create an Update Query using
the query builder:

1. Create a New query
2. First add the table you wish to update
3. Then add the table you wish to update from (your Excel sheet)
4. Join the two tables on the common field(s)
5. In the join properties for all joins, select Type 3. (This will include
all rows in the source table and only update rows that match)
6. For Query Type select Update Query (Now you will have 4+ rows in the
lower pane. Field, Table, Update To, Criteria, and or. The "or" is to allow
or criteria conditions.
7. In the Field column, enter the fields from the table to be updated. The
Table row should fill itself in if you drag and drop into the Field row
8. In the Update to column, enter the table name and field names of the
table to update from. (Here I usually use the Build wizard because it is
faster than typing the table and field names by hand.
9. Add any filtering criteria in the Criteria row that determines whether a
particular row should be updated.

And last, Make a test copy of the table to update and use it for testing :)
I hope this works for you.

Good Luck
 
Klatuu,
Thank you so so very much, yes it worked. Now, all I need to do is add a
column for last updated date in month format. So any time an update occurs
that last updated field gets auto filled. Is "CDate()" takes care of the auto
date. Your suggestion have been very valuable and I appreciate it a lot.

Thanks
 
Pani,

I am very glad to help. You can add a column to your query for the date
field to update your table to autfill. You would just put the date you want
in the Update To row in the column for the date. If the date field in your
table is a date type, then you will not need the CDate function. If you use
the current date, you could just use Date(). If it is another date, just
inclose it in # like #5/31/2005#. I just noticed you said Month format. I
am not sure what you mean. If you mean you only put the month in the column
and not a real date, the Month(Date()) will do it.

Good Luck
 
Klatuu,
Thank you so much. I used the Date() format. The Month(date()) was also good
however it only gave me month in period format e.g june = 6. I am good with
date(). Thank you. I am trying to create an application in access. So I look
forward to your valuable assistance with my future questions.

Regards,
Pani
 
Back
Top