Need help database concept (Access 2000)

[nwinchel]

Can someone help me conceptualize what I’m trying to do here? Our organization keeps track (for grant funding purposes) of statistical information. In this particular instance we track the number of telephone calls we receive requesting assistance that we are unable to provide. These calls need to be tracked by the following: date of call, caller’s sex and county of origin (one of five), which agency(s) the caller was referred to (22 different possibilities) and what services the caller requested. This information is compiled at the end of each quarter, grouped by month and totaled by number of callers per sex per each county and by the total number of calls to any particular referral agency (can be one of 22).

This is currently being tracked on a large Excel spreadsheet. The receptionist completes information by hand on a tracking sheet and I am expected to compile and enter the data on the spreadsheet each month. At the end of the quarter, results are totaled and the spreadsheet is forwarded to the requesting grant provider.

I would like to set this up in a little Access database so the receptionist could enter the information (once) on a pop-up form. At the end of the quarter, I want to be able to produce a report that provides the same results we’ve been getting by using the spreadsheet. Initially, this seemed pretty simple until I realized that each call could be referred to as few as none or as many as 22 different agencies! (Typically, callers are referred to an average of 2-3 different agencies.) Am I trying to reinvent the wheel or would I be better off continuing to do what we are doing with the Excel spreadsheet?

Please keep in mind that I am not a programmer and my skills with Access 2000 are intermediate, at best.

Thanks!

 

[Rockn]

Database is a far better solution and it wouldn't be that hard. One table for the caller and any info pertaining to them. One table with the agencies, and one for services. Then one last table to track the activity for each client. The one thing you need to try to avoid is duplicating client records by setting up a search page where you can verify the client being in the db or whether they are a new client.

 

[nwinchel]

Originally posted by Rockn
Database is a far better solution and it wouldn't be that hard. One table for the caller and any info pertaining to them. One table with the agencies, and one for services. Then one last table to track the activity for each client. The one thing you need to try to avoid is duplicating client records by setting up a search page where you can verify the client being in the db or whether they are a new client.

Thanks for your reply but the client doesn't have a name or any other distinguishing information other than their sex and county of origin. Here is some typical data would be that recorded:

1/1/02 - Female, Smith County, Referred to (1.) Legal Services, Inc. (2.) Dept. of Social Services and (3.) Project Help
Reason: needs legal counsel

We may get several similar calls each day with requests for, say, housing or transportation - services we do not provide. At the end of the month (and each quarter) I need to show in a concise way how many females from Smith County called requesting services we do not provide. I also need to show how many times we referred these individuals to each organization listed. (For example: 10 callers referred to Legal Services).

I apologize for not being more clear in my original request.

 

[Rockn]

Shouldn't be a problem then...just omit the one for the client and use an autonumbered primary key for each entry.