Look I'm an absolute noob when it comes to designing databases, yet I am running a program at work that requires collection of data and excel is just not cutting the mustard.
A short overview of what I do.
I coordinate about a dozen volunteers who go out into the community and talk to families with young children about the benefits of early education. After every contact the volunteers fill out a form that collects information such as :
Date of contact
Suburb of contact
Family identify as (nationality)
Number of children under 5
Currently accessing early education - Yes / No
If No, why? (small list of reasons)
Can we offer any other help - Yes / No
If Yes, how (small list of options)
If No, why? (small list of options)
I want to track monthly, quarterly and yearly statistics based upon either suburb, nationality, currently accessing early education and also against individual volunteers. For example I might want to know how many children under 5 are accessing early education in a particular suburb, or again, how many families were contacted in a quarterly period by a certain volunteer.
I am using Access 2007 and have tried to set up tables in the program but it isn't working for me. I'm not quite sure how to go about setting up the tables, like would I set up just a table of volunteers, and link these to other tables containing the data? or perhaps set up one big table containing all data? or perhaps each piece of information goes into a separate table?
As I said I don't quite understand and if anyone out here can help me get started, or point me in the right direction I would much appreciate it.
Well bradlitchy, it is all up to you. As a developer you need to figure out the relationships to your data and things that would be repeated put them on say a refrence table. so like for example:
your volenteers would be a table with thier pertinate infor(hours,name,ID,etc...)
Locations of contact Descriptor of that data
Then you could have one table that links all that date together as in,
Rep Location Number of Children date visit happened
1 1 6 1/1/11
2 1 2 1/1/11
3 4 1 1/2/11
Of course this is a very basic example but should get you to the results you need.
Mostly, Try not to repeat big values as in you would only want to store a numeric single digit repeatedly instead of The comunity Room 15 time. It has to do with the size of the DB when all said and done. Each data type uses more or less room on the database, so a single integer will take up less room than a text field(column) that stores say 200 characters in each row.