update the content of the field

[Frank Situmorang]

Hello,

In my church membership database I have a creaed a button for updating for
new records. Since I have a church clerck a user, and all other chruch board
members as other users I want to have a updating procedure that all other
users can import the data from the church clerck fo the most current one.

I will elaborate my problem

Chruch clerck has the following record

Member ID Name member status

1 James Manembu Aktif

Other users
1. Jannes Manembu Aktif

Found later that Jannes was mistyped, then the clerck correted and give the
flash disk to all other users.

What kind of query shall we make to handle this contents update. because it
will conflict the member_is if we make an update query.

Thanks for any help.

Frank

 

[Tom van Stiphout]

On Sun, 5 Jul 2009 23:58:01 -0700, Frank Situmorang

No it will not, if you have a correct db design.
I am assuming you have a design like this:
tblMembers
MemberID Autonumber PK
MemberName text required
MemberStatusID int required (FK to tblMemberStatus)

Then you can write:
update tblMembers
set MemberName = "James Manembu"
where MemberID = 1

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

Hello,

In my church membership database I have a creaed a button for updating for
new records. Since I have a church clerck a user, and all other chruch board
members as other users I want to have a updating procedure that all other
users can import the data from the church clerck fo the most current one.

I will elaborate my problem

Chruch clerck has the following record

Member ID Name member status

1 James Manembu Aktif

Other users
1. Jannes Manembu Aktif

Found later that Jannes was mistyped, then the clerck correted and give the
flash disk to all other users.

What kind of query shall we make to handle this contents update. because it
will conflict the member_is if we make an update query.

Thanks for any help.

Frank

OWWWW....

Does *EACH USER* have their own private copy of the entire database, with all
the data?

Is there a network? Can other users connect to the church clerk's computer, or
are they separate standalone computers?

 

[Frank Situmorang]

John,

By the help from this news group, I have desingned a button to copy the
table to a flash disk, and another button to receive the table into the other
users directory, say C:\Churchdata\Conso

And I have desingned a button for an append query, for anytime the church
clereck have new/additional record/members willl be appended to the other
users table.

Now I want it to have for the same records can overwrited by the incoming
ones, because those are the latest revised contents of the fields. Like my
illustration, the secretrary/church clrerk mingt found later the
typhographical error, or the member changed his/her name.

Thanks for any idea

Frank

 

[John W. Vinson]

John,

By the help from this news group, I have desingned a button to copy the
table to a flash disk, and another button to receive the table into the other
users directory, say C:\Churchdata\Conso

And I have desingned a button for an append query, for anytime the church
clereck have new/additional record/members willl be appended to the other
users table.

Now I want it to have for the same records can overwrited by the incoming
ones, because those are the latest revised contents of the fields. Like my
illustration, the secretrary/church clrerk mingt found later the
typhographical error, or the member changed his/her name.

Thanks for any idea

That's going to be VERY, VERY COMPLICATED.

Having every user a) able to make arbitrary changes to the data and b)
requiring that those changes be replicated to every other user is *a big
mess*.

Replication is one tool to allow this to be done. It's a complex tool; there
is an entire newsgroup, microsoft.public.access.replication, for just that
purpose. It's so complicated that Microsoft took it OUT of A2007 in the new
format.

Redoing the features of replication in your own code and queries will be ten
times worse. You'll need to deal with questions like:

- What if a user deletes a record and the clerk sends an update for that
record?
- What if the clerk changes the name from John to Johann, and somebody ELSE
changes the record from John to Joan?
- What should be done if three users all add a record for the same person?
- What if they spell that person's name differently?

You haven't said in this thread whether the users are on a network. I know you
have databases at different churches (which presumably aren't networked), but
is that what you're talking about here? or are you dealing with different
users within a single church (who might or might not be on a network)?

 

[Frank Situmorang]

John,

My case is, let's say only in my church, but as soon as the system works for
my case it can also work for the other churches within my denomination (
Sevent Day Adventist). I already made it capable for consolidation at the
higher church office.

All users are stand alone, not using net work. We will make a policy that
the one with the church clreck is the one which is more updated and valid, so
every body has to ask the data on the flash disk to be imported into their
laptops.

Is it still a mess, if we make a policy like that?. The problem is I do not
know what kind of query that can update/overwrite all the fields in the table
except Primary Key.

Thanks for your help.

Frank

 

[John W. Vinson]

All users are stand alone, not using net work. We will make a policy that
the one with the church clreck is the one which is more updated and valid, so
every body has to ask the data on the flash disk to be imported into their
laptops.

Is it still a mess, if we make a policy like that?. The problem is I do not
know what kind of query that can update/overwrite all the fields in the table
except Primary Key.

If the clerk's copy is the definitive one, and the users do not edit or add
data to any table, then I'd suggest simply deleting the user's database and
replacing it with the clerk's master copy.

 

[Frank Situmorang]

John,

The problem is that not all tables to be replaced. Let's say the notes is
the subform for member's form with the Member's ID as a link, Pastor can make
day to day's note when he made visitation to the members, so it will remain
there.

So just several tables that need to be replaced,

Any way out?

Thanks in advance,

Frank

 

[John W. Vinson]

John,

The problem is that not all tables to be replaced. Let's say the notes is
the subform for member's form with the Member's ID as a link, Pastor can make
day to day's note when he made visitation to the members, so it will remain
there.

So just several tables that need to be replaced,

Any way out?

No easy ones.

Use File... Get External Data... Link to link to the table you want to update,
and run an Update query. You'll need some way to identify which records need
to be updated and which don't - a DateChanged field would be one possibility.

Or, delete the tables and use File... Get External Data... Import to import
the clerk's copy. You'll need to drop and recreate relationships.

Or, give each user not one but TWO backends - one for their own personal
tables, and a separate backend of the data maintained by the clerk.

 

[Amy E. Baggott]

Our database updates from an association-wide database twice a day. I have
code in my update procedure that compares the two tables (the one in my
database vs. the data exported from the main database) and uses the Seek
function to see if each record in the new table already exists in the current
table. If it does, I use rs.edit to update the field values to the ones from
the exported table. If it doesn't, I use rs.Add to add a new record with all
fields. I can send you a sample of the code if this sounds useful to you.

 

[John W. Vinson]

Our database updates from an association-wide database twice a day. I have
code in my update procedure that compares the two tables (the one in my
database vs. the data exported from the main database) and uses the Seek
function to see if each record in the new table already exists in the current
table. If it does, I use rs.edit to update the field values to the ones from
the exported table. If it doesn't, I use rs.Add to add a new record with all
fields. I can send you a sample of the code if this sounds useful to you.

A much simpler approach (than using VBA and recordsets and Seek) would be to
use an Append query with a Right Join clause to find duplicates:

UPDATE yourtable
RIGHT JOIN importtable
ON yourtable.ID = importtable.ID
SET yourtable.ID = importtable.ID,
yourtable.thisfield = importtable.thisfield,
yourtable.thatfield = importtable.thatfield,
<etc through all the fields>


This will update matching records *and* insert nonmatching records in one go.