Communication Log

[John]

I'm not sure if I'm posting this message in the correct area so if you read
this and you think that it should be posted somewhere else please let me know.

Before I ask my question I'm going to tell you a litle bit about what I'm
trying to do.
I am the Public Relations (PR) Officer for the Self Advocacy Council of
Northern Illinois (SACNI) and I am making a database to keep track of all
contact nformation (members & other people we've contacted). I want to set up
a communication log in the database so that it will store the Date, time, and
Contact Method ID for 1 year for each contact.

My form is set up as follows. (Note: LC = Label Caption & Txtbx = Tex tbox

LC Control Notes
Contact Method ID: Dropdown box (I can select a value to store in the
ContactMethodID field)
LC Txtbx1 Txtbx2
Last Contact Date & Time: Date Time

Notes: Txtbx1 is set to accept Date from the Date/Time option that I set for
the LastContactDate field in my table and Txtbx2 is used to insert the tme
into the LastContactTime field.

The above controls are set up in a page using te tab control. Underneath the
tab control I have two more controls set up and they are as follows:

LC Txtbx1 LC Txtbx2
Contact Updated on Date at Time

Note: Txtbx1 stores the date in the DateContact Updated field and Txtbx2
stores the time in the TimeContactUpdated field.

Now that I've explained how the form is set up, my questions are as follows:

I want to have the control for each field store the data in the proper
fields. When new dates and times are entered I want the database to move the
old valuses to the Communications log. When I view my records I want to be
able to view the communication log by clicking a link that says View
Communication log. and have it pull up the information for that contact. I
want it to display the newest or the most up-to-date information first
followed by the old er values. I also want to use only one log to store all
the contacts' information

1. Is there a way to set up my database so that it will save the information
stored in the ContactMethodID, LastContactDate & LastContactTime for the
length of 1 year?

 

[mscertified]

When you save a record, it's saved forever unless you specifically delete it.
So each time you open your database, you will need a special routine that
goes thru your records and deletes any over 12 months old. You will want this
routine to be as efficient as possible to minimize any delay in opening your
database.

Dorian

 

[Steve]

Helo John,

A good database begins with a set of well designed tables. Here's a
recommendation for your tables:
TblContact
ContactID
FirstName
MI
LastName
<<Fields of contact information>>

TblContactMethod
ContactMethodID
ContactMethod

TblContactWithContact
ContactWothContactID
ContactID
ContactMethodID
ContactDateTime
ContactNote

For entering contacts, contacts with contacts and viewing contacts with
contacts, you can use a form/subform. First create a query based on
TblContact and sort ascending on LastName. Use that query for the
recordsource of the main form. Then create a query based on
TblContactWithContact and sort descending on ContactDateTime. Use this query
for the recordsource of the subform. Make the subform a continuous form. Set
the LinkMaster and LinkChild properties of the subform control to ContactId.
If you do all this, the main form will display one contact at a time and the
contacts will be in alphabetical order. The subform will be a list and will
display all the contacts with the contact in the main form from last contact
to first contact.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John]

I understand that. What I want to do is have the database store the
information in a seperate document so that I can open it later and it will
have something like what I have bellow:
_______________________________________________________
Last Last
Contact
Contact Contact Contact Method
ID Contact Name Date Time ID
--------- ------------------- ---------- ------------ ---------
1 Cathy Christensen 7/25/07 7:10 PM PC
7/21/07 12:00 PM EM
2 Sharon Lipinski 7/26/07 9:00 AM PV
7/25/07 10:00 AM PC
7/01/07 9:45 AM PC
__________________________________________________

I want the database to save the information for all the contacts. Is there a
process that I can build that will cause this type of document to be made?

 

[John]

Hello Steve,

May I send you copy of the database that I made? It will have fake info in
it so that you can see how I have it set up so far. I'd like you to veiw it
and tell me if I'm on the right track. If you're willing to share your real
email address with me I'll give you mine beacuse I'd like to contact you
further about my database. Please reply ASAP.

Thank you,

 

[John W. Vinson]

I understand that. What I want to do is have the database store the
information in a seperate document so that I can open it later and it will
have something like what I have bellow:
_______________________________________________________
Last Last
Contact
Contact Contact Contact Method
ID Contact Name Date Time ID
--------- ------------------- ---------- ------------ ---------
1 Cathy Christensen 7/25/07 7:10 PM PC
7/21/07 12:00 PM EM
2 Sharon Lipinski 7/26/07 9:00 AM PV
7/25/07 10:00 AM PC
7/01/07 9:45 AM PC
__________________________________________________

I want the database to save the information for all the contacts. Is there a
process that I can build that will cause this type of document to be made?
--

Yes, a Report based on a query joining your people table to the table of
contacts with those people.

John W. Vinson [MVP]

 

[John Marshall, MVP]

Be careful, steve does have a reputation. Check his references first.

John... Visio MVP

 

[Steve]

How's this for a reference!

His (... i.e. Steve) technical answers in these forums are good.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Steve]

Hi John,

My email address is at the bottom of my sig line.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

You really like to take things out of context.

You seemed to have ignored the rest of his message that proceeded that line.

So if you consider that message as a "good" reference, then you really are
pathetic.

John... Visio MVP

 

[John]

Hi John Marshall.

I appreciate it that you are concerned about people having to pay for help.
I definately do not consider his word as a reference. To me, a good reference
would be if at least 5 other users sent mea a message stating that his help
is free and that his knowlege has of Access has helped them solve their
problems.
Therefore, with that being said, I'm going to take all the information that
I've gathered from this communication thread and try to set up a report as
you have suggessted.

 

[John]

Hi Steve,

I've decided to do the best that I can with the information provided. Thanks
anyway.