converting this questionnaire into a database ?

[saleemMSMS]

dear everyone
i am advised to create a database for the following form.
note that this is only part of the form and the actual form contains 20 questions (in similar format) whereas this contains only 2 questions.

i though of having the following table structure
city
cityID,cityName,districtName

villages
villageID,CityID, villageNAme

Q1point1VillageLevel
VillageID,GNID,preNative,preForiegn,PresentNative,PresentForiegn

Q1point2
VillageID,GNID, MaleLessThan5,Male5to10,...,Femaleabove60

Q1Total
GNID,totalPreNative,TotalPreForiegn,TotalPresentnative,TotalPresentForiegn

Q2
VillageID,GNID, FarmLabourNo, FarmLabourPercentage,....,EmployedPercentage


please advice whether i am going in the correct way. thanks in advance

 

[JamesMcS]

Can't see your pic but you've got a lot of tables there. Id have all answers in the one table to be honest, much easier to report on.

 

[vbaInet]

You could have:

City TABLE:
CityID, CityName, DistrictID, VillagesID

Villages TABLE:
Village ID, VillageName, VillageInfoID

VillagesInfo TABLE:
VillageInfoID,......

But then again, most people don't use Access for questionnaires and surveys. You're better off buying a proprietary software for this purpose.

 

[pinkdatabase]

You essentially have a bunch of Villages linked to Cities in a one to many relationship.

Each Village has a bunch of answers to your questions (ie FamilyNo_PreConflict_Native, FamilyNo_PostConflict_Native, etc)

So you will have two Tables tblCities (CityID, CityName, CityInformation, etc) and tblVillages (VillageID, VillageName, CityID, Q_Answer_1, Q_Answer_2, Q_Answer_3 etc)

You could do it with just the one table as suggested ie tblVillages (VillageID, VillageName, CityName, Q_Answer_1, Q_Answer_2, Q_Answer_3 etc) if you don't need to store any information about the Cities

You will be able to get totals for Cities by using Crosstab queries

Hope this helps

 

[pinkdatabase]

Also you would put the DistrictName in the tblCity table

You can have as many Villages in a city as you like rather than be restricted to the 5