Free Form Text Entry

[mcp6453]

Even though I used to work with databases years ago, I have never worked
with Access 2003. Presently, I need to create a very simple application
that is similar to one that a credit card company uses, as an example.
If you have ever called your credit card company, after you give them
your card number, they bring up a page that has all of your account
information and a page for comments. The "comments" function is what I
need. Here is a crude and grossly simplified representation:

===================================

Name: John Smith
Credit Card Number: 2342-2343-2343-2342
---------------------------------------

02-08-2008 Mr. Smith called to complain about us charging him an annual
fee. We agreed to waive it for this year.

02-08-2008 Mrs. Smith called to say that Mr. Smith has not been taking
his medication and that we should ignore his call.

02-09-2008 Mrs. Smith called again to say "never mind".

===================================

The field structure would look like this:

===================================

Name: <first name> <last name>
Credit Card Number: <card number>
---------------------------------------

<date_001> <comments_001>

<date_002> <comments_002>

<date_003> <comments_003>

===================================

Some records may have comments up to 99 comments (comments_099), while
most would only have a few comments (say, to comments_05). In the old
days, you had to create fields for the maximum number of entries, but I
understand that Access can add these fields dynamically. If that is
true, how?

Second question: What field type is <comments_###> such that free form
text multi-line text can be entered?

Thanks for the help.

 

[Albert D. Kallal]

You have for the most part laid things out correctly, except for:

<date_001> <comments_001>

<date_002> <comments_002>

<date_003> <comments_003>

Think of "MANY" applications you used in which repeating data is to appear
over and over. What we do is use the relational ability of the database to
*repeat* that data.

So, in fact, we get two tables:

tblCustoemr

fields: id (autonumber primay key) firstname, lastname, credit car etc.

You have:
Name: <first name> <last name>
Credit Card Number: <card number>

you then need a table with comments for each incident:

tblComments, it will have:

cust_id, IDate, Comments

Some records may have comments up to 99 comments (comments_099), while
most would only have a few comments (say, to comments_05). In the old
days, you had to create fields for the maximum number of entries, but I
understand that Access can add these fields dynamically. If that is true,
how?

*exclclent* question. Yes, you use a another table, and *relate* this table
back. That way, if you have 2 reocrds...you don't wind up with 99 blank
fields. It also makes reporting on this data VERY easy since you can build a
reprot that will show ONLY the cust name + the *last* incident date (if you
used 99 fields, this becomes next to impossbile to biulda reprot to figre
out which of hte 99 fields is emplory etc. Furhte, you acually would have
300 felds asusming a max of 100 coments.

Second question: What field type is <comments_###> such that free form
text multi-line text can be entered?

It is a called a memo field, and even hitting the "enter" key can be set to
create a new line in teh text....

So, we would create our main table (user name, credit card etc).

In practice, you'd have some means to search/find this customer. upon
finding this record, you would present the user with a form with the main
contact information, and then a continues sub-form would display the
"repeating" informant you have:

02-08-2008 Mr. Smith called to complain about us charging him an annual
fee. We agreed to waive it for this year.

02-08-2008 Mrs. Smith called to say that Mr. Smith has not been taking
his medication and that we should ignore his call.

02-09-2008 Mrs. Smith called again to say "never mind".


The above would thus be 3 new records. I give some screen shots of
"repeating" data forms here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

if you want to see some samples of applications, then check out the
northwind sample that ships with ms-access (in a2003...go help->sample
databases->northwind sample database).

As a another suggestion, as a general rule you find it easier if you avoid
spaces in field names (they are allowed, but I recommend you avoid this
practice).

 

[mcp6453]

You have for the most part laid things out correctly, except for:


Think of "MANY" applications you used in which repeating data is to appear
over and over. What we do is use the relational ability of the database to
*repeat* that data.

So, in fact, we get two tables:

tblCustoemr

fields: id (autonumber primay key) firstname, lastname, credit car etc.

You have:
Name: <first name> <last name>
Credit Card Number: <card number>

you then need a table with comments for each incident:

tblComments, it will have:

cust_id, IDate, Comments


*exclclent* question. Yes, you use a another table, and *relate* this table
back. That way, if you have 2 reocrds...you don't wind up with 99 blank
fields. It also makes reporting on this data VERY easy since you can build a
reprot that will show ONLY the cust name + the *last* incident date (if you
used 99 fields, this becomes next to impossbile to biulda reprot to figre
out which of hte 99 fields is emplory etc. Furhte, you acually would have
300 felds asusming a max of 100 coments.


It is a called a memo field, and even hitting the "enter" key can be set to
create a new line in teh text....

So, we would create our main table (user name, credit card etc).

In practice, you'd have some means to search/find this customer. upon
finding this record, you would present the user with a form with the main
contact information, and then a continues sub-form would display the
"repeating" informant you have:

02-08-2008 Mr. Smith called to complain about us charging him an annual
fee. We agreed to waive it for this year.

02-08-2008 Mrs. Smith called to say that Mr. Smith has not been taking
his medication and that we should ignore his call.

02-09-2008 Mrs. Smith called again to say "never mind".


The above would thus be 3 new records. I give some screen shots of
"repeating" data forms here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

if you want to see some samples of applications, then check out the
northwind sample that ships with ms-access (in a2003...go help->sample
databases->northwind sample database).

As a another suggestion, as a general rule you find it easier if you avoid
spaces in field names (they are allowed, but I recommend you avoid this
practice).

Albert, you and Rick have answered my questions. Thanks. The problem is,
I'm going to have to dig into a book or two. I don't know enough about
Access yet to know how to relate the comments table to the main
application. But, you have given me some insight to hopefully know where
to look. The goods news is it appears that Access will do what I want it
to do.

Thanks!

 

[John W. Vinson]

But, you have given me some insight to hopefully know where
to look.

Here are some other places:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[mcp6453]

Here are some other places:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Thanks for the links, John. Can you point me to one that relates one
table to another, as I have been told this application needs? I'm
reading tutorials and an Access 2000 book, but I'm not keyed in on it
yet. I know the solution is simple, but there is a lot to comprehend at
first blush.

 

[John W. Vinson]

Thanks for the links, John. Can you point me to one that relates one
table to another, as I have been told this application needs?

I'm pretty sure ALL of them would, at least somewhere; try Crystal's tutorial.

In a thumbnail - every table should have a "Primary Key", a field or
(sometimes) two or three or more fields, which uniquely identifies the record.
If there is a table related one-to-many to this table, it would have a field
of the same datatype as a "Foreign Key".

For example, if you have a table of Schools and a table of Teachers, and every
school has zero, one, or more teachers, and every teacher teaches at one and
only one school, you might have tables like:

Schools
SchoolID <Autonumber, Primary Key>
SchoolName
Address
City
State
Zip
<other info about the school itself>

Teachers
TeacherID <Text, unique school-administration supplied ID>
LastName
FirstName
<other biographical data>
SchoolID <Long Integer>

You would define a one to many relationship in the Relationships window by
adding these two tables, and dragging the SchoolID from Schools to the
SchoolID in Teachers. When you add a new teacher, you would need to specify a
valid SchoolID (Access forms give a variety of tools to do so painlessly, you
would never need to look up or type the autonumber, although that is certainly
one possible way). The relationship would ensure that you could not assign a
teacher to Hogwarts Acadamy, unless of course that esteemed institution did
exist in your table of schools.