History Report for Dales Contact database

[herddav]

Hi
I have created a database to record activity re sales but I need the Notes part of the data base to povide an ongoing history of contact with the relevant client.
I have a table with Client Name,address,tel no etc and a contact date and notes field but I need the last two to be recorded and to appear in a history report. What I have at the moment is all info appearing as desired but the contact date and notes only shows the most recent info and all previous info is replaced by the most recent entry.
I need to be able to view on the history Report the ongoing contact and notes oevr a period of time. I solved this some 12 years ago for a database that I built from scratch with no previous Access experience but cannot recall how I did it and I am having trouble finding an answer. Can anyone please help me?
Thanks in anticipation.
Herddav

 

[EternalMyrtle]

One suggestion would be to set up an audit system and just use it on the one field you need to keep track of (in the BeforeUpdate event of the control use code to call the function TrackChanges).

I used this one as a guideline. With a little customization it worked well for me: http://forums.devarticles.com/microsoft-access-development-49/create-audit-trail-on-ms-access-database-form---urgent-help-328012.html

You also need the fOSUserName function that is included before the Trackchanges function if you want to save the user name of the person who entered the data.

I hope this makes sense.

 

[EternalMyrtle]

Another thing I was just thinking is that part of the problem may be your structure. If your tables are set up well, then you can achieve something like this without code.

You could create a junction table (one to many) between the contacts table and the date and notes info that you want to keep track of.

So tblContactHistory
ContactHistory (PK)
ContactID (FK to contacts or customers)
ContactDate (date contacted)
Notes

 

[EternalMyrtle]

This is not really a report issue but a structure issue. You should probably disregard my first post and adopt the strategy outlined in the second post.