Please help with Access setup

[JM]

Please see this link: home.midsouth.rr.com/themitchells5. You will see
scanned images of two forms I am working with.

The top form, "The Samaritans," is a hard copy form that the Samaritans
organization has been using for years for its clients (the people who come
to them for help). Today, the Samaritans have a file cabinet with several
hundred of these forms. They have never used computers for this task.

I would like to help them change that. I want them to be able to use a form
(in Word, Excel, or Access) for data entry and a database to hold all of
their info. I would like the interface to be similar to the hard copy form
(although it can be different if necessary), and I want them to be able to
recall data via the Case Number and Family Name records. At some point
reporting might be a consideration, but for now the main issue is creating a
form and a database and teaching the volunteers to use it.

Please give me input as to the best way to accomplish this. Originally I
had planned to use a form in Word (based on Microsoft's "online form" model)
and then later manipulate the data in Access, although I'm not exactly sure
how to accomplish that. Would it be better to use an Access table for the
data input? If so, how would I handle the need for the "Comments" section?

Thank you for any and all help.

jm

 

[Allen Browne]

JM, Access would be ideal for this kind of thing, but you will need to know
about making relationships between tables before you try to create these
forms.

Information is given about people, so you will need a Client table.
Each person is given a Case number. so that could be the primary key.
The various fields of your form would then be fields in this table.
For the comments field, use a Memo field.

The assistance given can happen many times for one person. These values need
to be stored in a related table, so that one person can have many
"assistances" over time. The actual structure of the table will depend on
how they use the columns there. If you are absolutely certain that any check
will be written out for ONE of those columns only, the fields of the
Assistance table would be:
AssistID AutoNumber primary key
ClientID Number relates back to Client.ClientID (the
case number)
AssistDate Date/Time when this assistance was given
CheckNum Number the check number
AssistTypeID Text (24) one of the values "electric", "rent",
"gas", etc.

AssistType table:
AssistTypeID Text (24) primary key (list of assistance types.)

You will then have a main form for the client, with a subform for the
assistance given.

For more information about how to design a database correctly, see:
How to find information about designing a database in Access 2000
at:
http://support.microsoft.com/kb/288947/en-us

 

[tina]

i'd say that Access is the best tool for the job. and if you're going to use
Access to store the data, you might as well use Access forms to
enter/edit/review the data (rather than Word forms), and Access reports to
extrapolate information from the data.

but the paper form is collecting quite a lot of related data. designing a
relational structure to house the data (read multiple related tables), and
the queries, forms, and reports to interact with it, will not be a trivial
task. i gather from your post that you have little or no experience with
data modeling, or with the Access software. i believe you can do this, if
you're willing to invest the time and effort it will take - which, from your
starting point, will be considerable. see
http://home.att.net/~california.db/tips.html for a series of tips that are
focused on getting you started off on the right foot. good luck, make
liberal use of the many resources on Access development, on the Internet,
and come back to the newsgroups for help with specific questions - we're
always here.

hth

 

[JM]

Thank you for the excellent reply, Allen. I will consider all your comments
and suggestions.

thanks again.

jm

 

[JM]

Great reply, thank you. You are correct; I have virtually no direct
experience with this type of thing. I am a telecom and computer
professional (specializing in voice and video systems, network deployment,
and desktop support), and I very much want to learn this. I am willing to
put in whatever time is required to get a working knowledge of Access. I
have wanted to do so for a long time, and it took this project for an
organization with which I am affiliated to push me into.

Please let me know of any other specific resources that you think might be
helpful to me.

thanks again,

jm

 

[tina]

all my standard suggestions and resource recommendations to "newbies" (term
used affectionately, never fear) are at the link i posted previously. i'm
confident that you'll do fine.