Combine data without the PK

[Frank Situmorang]

Hello,

My church database is allready go live...but I still want to make
developement as follows.

I have memberdata input form, that have several Tabs. One of this is Notes
on Members which table is:

NotesID Auto
Member ID TExt
Date of Note Date/Time
Time of Note Date/Time
Subject Text
Note Memo

Since our method is the database is kept and handled by a church secretary
as a base, then each church board member can have a copy then use the each
members NOte as their visitation records.

Then secreatry will collect the Notes data from them and recombined to the
Notes table maintained by the secretary.

The problem is since the same database kept by other users ( chruchboard
members), there will be possibilty of duplication of the Primary Key when
combined.

Cn anyone help me how can I combine the addtional Notes made by the
churchboard members.

How can I append the data without the PK (Notes ID). To diffrentiate that
there is the new notes, I just want to compare if the Content of the Notes
field is not the same it will be accepted, if not it will be rejected ( not
combined)

Please help me how can I combined it. What is the VBA for this.

Thanks very much.

Frank

 

[Jeff Boyce]

Why are you using two fields (both Date/Time) to store the date and time of
the Note? Access Date/Time data type stores both in a single field.

If more than one person is using (a copy) of the database and making entries
in this table, what difference does it make what NotesID values might be
involved? I'm guessing that the MemberID and other fields contain the
relevant information, and that NotesID is simply a row identifier... unless
you left out the part of the description that says that the NotesID value is
used in a different table as a foreign key value!

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marshall Barton]

My church database is allready go live...but I still want to make
developement as follows.

I have memberdata input form, that have several Tabs. One of this is Notes
on Members which table is:

NotesID Auto
Member ID TExt
Date of Note Date/Time
Time of Note Date/Time
Subject Text
Note Memo

Since our method is the database is kept and handled by a church secretary
as a base, then each church board member can have a copy then use the each
members NOte as their visitation records.

Then secreatry will collect the Notes data from them and recombined to the
Notes table maintained by the secretary.

The problem is since the same database kept by other users ( chruchboard
members), there will be possibilty of duplication of the Primary Key when
combined.

Cn anyone help me how can I combine the addtional Notes made by the
churchboard members.

How can I append the data without the PK (Notes ID). To diffrentiate that
there is the new notes, I just want to compare if the Content of the Notes
field is not the same it will be accepted, if not it will be rejected ( not
combined)

It sounds like the church board members are adding their
notes to their own copy of the database tables instead of
linking to a single database that resides on one machine.
If that's the problem, you have a lot more issues to deal
with than just merging a few records with the same
autonumber values.

If it's at all feasible, the easiest thing would be to split
the database and have everyone connected to the backend data
tables whenever they use your program.

If they must use the data when not connected, then you could
try changing the NotesID field to a GUID type, but I have
heard reports of other issues doing that. Another approach
would be to derive a different systemID value for each
machine and adding that value in another field in the Notes
table. Then the systemID and NotesID field could be used as
a compound primary key.

 

[Frank Situmorang]

Yes..Jeff.. the field that I want to collect from them to be combined with the secreatary's table is just:

Member ID
Date of Note
Time of Note
Subject
Note

But I wnt it when combined created a new Notes ID. Wilt it do it for the Auto number?, Crrently it is an auto number.

The important thing is we want it to have a check if Note = Imported Note, it will be rejected. Coz we just want the new one.

If we make an append query to update but we do not incleded the Notes ID Field. Can it automatically create the new Notes ID in the Secretary's table?


Thanks again for your help.

Frank



Jeff Boyce wrote:

Why are you using two fields (both Date/Time) to store the date and time ofthe
03-Jan-10

Why are you using two fields (both Date/Time) to store the date and time o
the Note? Access Date/Time data type stores both in a single field

If more than one person is using (a copy) of the database and making entrie
in this table, what difference does it make what NotesID values might b
involved? I am guessing that the MemberID and other fields contain th
relevant information, and that NotesID is simply a row identifier... unles
you left out the part of the description that says that the NotesID value i
used in a different table as a foreign key value

More info, please..

-

Regard

Jeff Boyc
Microsoft Access MV

Disclaimer: This author may have received products and services mentioned i
this post. Mention and/or description of a product or service herein doe
not constitute endorsement thereof

Any code or pseudocode included in this post is offered "as is", with n
guarantee as to suitability

You can thank the FTC of the USA for making this disclaime
possible/necessary.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Share Outlook Express Message Store
http://www.eggheadcafe.com/tutorial...e-14b1c7348dc9/share-outlook-express-mes.aspx

 

[Jeff Boyce]

If you append records to the "main" table (?Secretary's copy), but leave out
the ID, Access will add it (you are using Autonumber, right?).

This will NOT work for you if you also use the NoteID value in another table
as a foreign key.

 

[Frank Situmorang]

Thanks Marsh for your response. Yes...since each churchboard member will have a responsibilty to nurture the member, so I as secretary want to combine all the data to my database. This is already go live, so I can not change the design of my table or forms any more. the only target now is how can I combine their table with me, since there will be a possibility of duplicate NoteID, how can I combine it by leaving the PK NoteID and only the other field will be appended and the new NoteID wil be regenerated for the imports. There is a filter that only we want to add if Memo filed content is the same, it will not be included in the imports.

Thanks for your help.

Frank



Marshall Barton wrote:

Frank Situmorang wrote:It sounds like the church board members are adding
03-Jan-10

Frank Situmorang wrote

It sounds like the church board members are adding thei
notes to their own copy of the database tables instead o
linking to a single database that resides on one machine
If that is the problem, you have a lot more issues to dea
with than just merging a few records with the sam
autonumber values

If it is at all feasible, the easiest thing would be to spli
the database and have everyone connected to the backend dat
tables whenever they use your program

If they must use the data when not connected, then you coul
try changing the NotesID field to a GUID type, but I hav
heard reports of other issues doing that. Another approac
would be to derive a different systemID value for eac
machine and adding that value in another field in the Note
table. Then the systemID and NotesID field could be used a
a compound primary key

-
Mars
MVP [MS Access]

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
RSVP (Rapid Sequential Visual Presentation)
http://www.eggheadcafe.com/tutorial...99-c13e46a9ad26/rsvp-rapid-sequential-vi.aspx

 

[Marshall Barton]

I seriously doubt that a notes field is an adequate record
key, but it's your program (and your headache). Maybe you
should try using an Unmatched Query as the source for the
append query:

INSERT INTO yourtable ([Date of Note], [Time of Note],
Subject, Note)
SELECT M.[Date of Note], M.[Time of Note], M.Subject, M.Note
FROM membertable As M LEFT JOIN yourtable As T
ON M.Note = T.Note
WHERE T.Note Is Null

Now what are you going to do when some member decides to
edit a previously imported note??