The title may seem odd, but I need to create a database which essentially consists of statistical data of various items for a whole state/province, including data for weather/geo, population, agriculture, etc. I estimated approx 80k+ individual records from various sources, consolidated into approx 400 tables of data which is published yearly. I am essentially replicating a Bureau of Statistics for a small nation. Currently the office is managing with Excel and paper and it is getting out of hand, as you can imagine.
I'm sure you're wondering why I'm choosing Access? Because I need to hand this over to people who are fairly frightened of anything technical. I also have a limited time to design and deploy this (when I say deploy, I mean hand over and hold their hand). I have been working with them long enough to figure out their needs and abilities.
So anyway, I am stuck in the planning/design stage trying to figure out how to store the data. Should I have one table for each 'table' (I will call them 'figures' to avoid confusion), normalizing wherever possible. Each field in each figure is more or less unique, but are generally by region: 'toddler death', 'black tin', 'Cocoa (Tonnes)', etc.
Each figure itself has information, ie. metadata, such as, figure number, title, publish date, data collection date and source. To top it all off, all the metadata needs to be in two languages.
I was considering the option of using Excel as a forms and reporting tool which they can use to fit in with their current statistics processes. This would still require me to create a truck load of fields in some table - how do you normally store hundreds of field's headings?
So, as the title suggests, I'm trying to create a miniature Bureau of Statistics. Has anyone had experience creating such a database or have any advice/examples?
PM me if you would like to discretely review a sanitized sample of the Excel sheets I am working with so you might get a better idea.