Notes and archiving

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I am new to access and need some help.

1. I am designing a database where employees will input
information into the "form" view. I would like to create a
field where they can enter notes regarding interactions
with clients. I can not figure out how to make an
organized field where I can list these separate notes, as
the memo box takes me to a new record when I press enter.
It's possible that there will be hundreds of entries per
record over time. Also, it would be great if I could find
a way to get Access to display the date of each note
automatically as well as the user. Is this possible?

2. I would like to know how to archive information. For
example, if a client changes her address, how can I keep
the old address without making new fields? Is there a way
that Access can automatically save that information for me?

Thanks!!!!!
 
1.

Setup 2 additional fields, one for the date, and one for the user name.

At the time of tabbing (or clicking) out of the note, have it record the
date and user name.

When the record gets update, both of these pieces of information are to be
updated along with it ( not sure if you are using bound or unbound forms,
but either is fine for this)

In design time, change the EnterKeyBehavior from Default to New Line in
Field.

2

Probably the best way to do this is to go ahead, setup 2 additional fields,
Start Date and End Date with that same table. This way, you can use all the
same fields without having to play much guess work. How this would work?

For Current addresses, there would be no End Date as those addresses are the
current addresses, sort of like employment dates for places where you work
at. Until you leave your current employment, there is no end date (unless
of course, it's something like a temp job with a known end date, but for
this purpose, we are assuming that we don't know when that end date will
be).

The way you would find the current addresses is to pull all records that
either has no end date (a good majority of them, like 99.9% of them), or
it's end date is equal to or later than today, and all start dates being eq
to or less than today.

Under this scenerio, note that each person may be listed in the DB more than
once, if they have lived at any other address prior to the address they
currently live at. However, if you are using some sort of an ID field as a
primary key (which would be advisable), you could just tie the various
reports/records to the ID that was used, such as on invoices, it would have
the ID # to the address record that contains the address and customer that
the invoice was sent to. This way, if a customer lived at one address, the
invoice lived at that address, but then the customer moved to another
address, you could easily pull up the customer address, then via change of
address, have it put in the effective date and new address all in the same g
o. This part may require a little DAO/ADO code to update the old record as
it creates a new record.

If you wanted to take this further, you could put in one or two more
additional date fields and have it automatically record the date that the
address was added/changed. By doing it this way (recording the 3 or 4
dates, preferably 4 dates), you can avoid a lot of the redundancy recording
as I have seen examples of how the redundant recordings has been done, thus
in return, can save you a lot of valuable disk space, cause archiving
addresses within various tables like invoices can be a thing of the past
just cause it would go by the ID number of the record that contains the
customer/address that the invoice was billed to. You would also have the
capacity to not only let the customer know what address it was billed/sent
to, but also when that address was effectively added/changed.
 
Jen, I can help you with #1 as I did something similar to
what you are discribing. What I have is a main form for
entering one time information on. Within the main form I
have a subform for entering the notes. The subform and
main form have a common link between them so when you
change the main form, the subform changes with it. I am
using this for Clients and have a client code field
linking the 2 together. The subform should be using a
separate table from the main form for storing the notes,
date entered or whatever else your notes might need to
track. The subform can also be using a query in which
case if you have a date entered field in the subform, you
can make it decending in the qry so the most recent note
is always listed first or top, depending how you
structure your subform. If, as you say, each record can
have 100's of note entries, you may need a "search"
method to find a specific note. I created a listbox on
the main form and used the bookmark method refering to
the subform. It has the client code, date entered, and
entered by in it. With the bookmark method attached to
the listbox, the listbox will take you to the note you
want. I found it easier then scrolling to thru the note
screens. The listbox should also be populated by qry
using the same link as the 2 forms plu add the subform
table ID for use with the bookmark. One thing that I
also did is to evaluate the different note screens and
the what is being kept. I found I was able to use one
table to hold the note screens from 7 forms/subforms as
they are holding the same info. Even if the client code
is the same, there is a topic line on each that provides
which form I'm in (ie, acctg, legal, finance, etc). Even
if the client code is in the table 7 times, the topic
line separates the client info by topic. I have 6 less
table to maintain and find for doing a report. It has
worked well this way for some time and there hasn't been
a reporting problem.
I hope this doesn't confuse you too much. But once you
get into it and begin developing the tables and the
form/subform, you will begin to see it take shape and do
what you want it to do.
Good luck.
*** John
 
Back
Top