Question Is a database the right option for us?

[shaishav]

Hi all, I've been looking around the net for a few days now and playing around in Access 2010 to try and work out if creating a database is right for us and I'm stuck.

Long story short, I work for a small Indian NGO that works primarily with kids. We recently conducted a survey with several thousand respondants accross the city we work in, and now need to digitise that data so we can analyse it and draw some conclusions. Unfortuntely, nobody in our organisation really has the capacity to do this and as I am the closet thing they have to an IT guy the responsibility has fallen on me to work out what to do.

I have never used a database before, let alone created one. I made a couple of tables and a form but to be honest I feel out of my depth. For instance, each paper form filled in details one household, however within that household there can be several kids, who each answer the same set of questions. Would I make one table to detail each kids responses and a seperate one for the household info (address etc)? I also need to make the form as simple as possible so the data entrists can't screw up, but I can for the life of me work out how to get questions to appear/disappear depending on previous answers.

I have also been fiddling around in EPI Info if anyone knows of that. It would possibly be more suitable than access 2010 but the documentation and support is really terrible. Perhaps there is a similar solution that wouldn't require the level of training that Access might?

Thanks fo the help but be warned, if you tell me to use access then you're opening yourselves up for a world of pain in future questioning!

tldr: I have no experience making databases. Is trying to make one so some data entry monkeys can copy in a truckload of survey forms wishful thinking?

 

[KenHigg]

The first question is how do you plan on distributing the application. In other words how will the end users get access? Will the computer(s) be in a single office or will they need to input the data from computers in a field environment? If they use computers in a single office then Access is a good choice. If they need to be in multiple physical locations then it may not be the better option.

 

[shaishav]

In a nutshell, we'd be hiring some local students to sit in our office and copy in the data from the paper forms. Computers would be in a single office, very possibly not networked.

The making the database is more of an issue though!

 

[KenHigg]

Sounds like this is a one time deal and there is a finite number of surveys that you may already have in hand? How many do you think there will be?

 

[shaishav]

It is a one time deal. I just found out there were about 10,000 completed forms which is a bit more than I was expecting :/

 

[John Big Booty]

Access would certainly do the job for you. As a starter you should become familiar with the principals of Database normalisation. Another thing to consider is a naming protocol for you database objects and controls. Start by limiting yourself to alpha numeric characters and the underscore (_), and avoid spaces.

Given that you will need to have the database stored on a central server and accessed by a number of connected computer also read up on splitting your DB.

Good luck with your project

 

[KenHigg]

Access would be good for this but I would be carefull not to add too many features as it is a one time deal. The main concern is the complexity of your paper form. Is there any way you can post a sample (maybe a blank)?

 

[Guus2005]

If you're a first time Access user you might want to consider an online Access course or a book

Looking forward to other questions you might have.
You could refer to your first post to avoid explaining your database each time you have a question. http://www.access-programmers.co.uk/forums/showthread.php?t=199513

Enjoy!

 

[shaishav]

Ok I hope this makes sense. The form itself is pretty large and is written in Gujarati so this is the best I could do for now.

The Survey form 1 attachement shows the questions at the top of each paper form. The survey is done house to house so this part details the household in general.

Survey form 2 attachment is the questions we ask each child in the household. We are interested in levels of education and child labour. This means for every individual paper form there can be as many as 8 children.

A lot of the responses are coded and so will be selected from a drop down list upon data entry.

I hope this makes sense to you, it's the best I can come up with other than finding a large scanner at short notice!

 

[ghudson]

Too late now but it is unfortunate that nobody thought ahead and used an online web survey where you could have downloaded the results into a spreadsheet which would have avoided the manual data entry and there will surely be typos and other data entry errors that you will need to account for when your students retype the data.

 

[shaishav]

Too late now but it is unfortunate that nobody thought ahead and used an online web survey where you could have downloaded the results into a spreadsheet which would have avoided the manual data entry and there will surely be typos and other data entry errors that you will need to account for when your students retype the data.

If only life was so simple! Great idea, but as we are a grassroots organisation working in the slum areas of a small Indian city it's not really possible. The vast majority of our respondents and a lot of our staff are not computer literate and do not have access to computers. Also, around three quarters of the people we surveyed cannot read or write above a very basic level. The only option was to have staff go round knocking on peoples doors, questioning them and filling in the paper forms accordingly.

 

[KenHigg]

How much time do you have?

 

[shaishav]

How much time do you have?

Mid november for data analysis and reports to be completed and written up properly. I guess this means I'd want all the data entry done by the end October, but that kind of depends on the speed of the typing monkeys, assuming I can make the thing and get it working.

 

[KenHigg]

Do you see and understand the apparent need for two basic tables? (This is just a question to guage your database skills)

 

[shaishav]

Playing around as I have been, I've made one table for the household info, containing everything from the survey form 1 attachment, and another table for the resposnes of the individual kids.

Things I am stuck on are:

1: How to tie the kids to the individual household data without making the people entering the data enter the household info for each kid.

2. How to make certain questions only appear on the form if certain conditions are met. For example, if the child is 4 years old, the pre-primary education questions would be available while the school questions would not.

3. How to make the form so that the user can enter data for each child on a separate page or something. I've been looking at the navigation page for this, having the first tab being household info (1st attachment) the second for the first child, third for the second child etc. But if I have say 8 child tabs and only 3 are filled, how can I make the database know there are only three kids, not 5 missing kids?

I am find this stuff very hard to explain!

 

[KenHigg]

I don't know that I'll have the time to answer all of your questions. The answer to the first question is that you'll need to set the two tables up with a one to many relationship (or maybe a many to many). At some point I think it would be best if you bought a good MS Access book as trying to learn all of this soley via the forum is going to be frustrating for everyone. Bob may have some good tutorials he could share.

 

[DCrake]

When deciding on the design of your form in Access you need to consider the person(s) entering the data. Therefore the Access form should follow the paper form from top left to bottom right. You don't want the inputter to be scanning up and down the paper to get to the next question.

Next, as mentioned you need three tables.

Table 1 - The property
Table 2 - The occupants
Table 3 - Responses

Table 1 will contain information about the property and will have a primary key. (auto number) assigned to it.

Table 2 will be the occupants. This will have its own primary key, auto number) and a foreign key (the primary key from table 1)

Thus

TblProperties
FldPK :1
FldAddress : 1 Lonely Lane
FldOccupants : 5
Other fields

TblOccupants
FldPK : 1
FldFK : 1
FldOccupantName : Amin
FldAge: 4
FldGender : M

FldPK : 2
FldFK : 1
FldOccupantName : Salim
FldAge: 10
FldGender : M

TblResponses
FldPK : 1
FldPropertyFK : 1
FldOccupantFK : 1
FldResponse1
FldResponse2
etc

This way you are not duplicating data and you have built a relationship between the property and the occupant and the occupant and the responses