In database terms, adding data (records/rows) is cheap, quick, and efficient.
Adding new fields is cumbersome, and could involve you having to update a bunch of different things.
So in your example, you may only have 20 records, but you have 52 fields!!!
What happens when you need to add more countries?
You would then need to add more fields, and then probably rewrite all your queries, reports, forms, and extracts.
If you choose the normalized model I suggested, if you have to add more countries, you do not need to add any new fields to your table. Simply add more rows of data (adding data instead of fields is always preferrable).
On top of that, in order to get the information out that you want, you would need to create a Union query like Alan showed up in post 3. Only, it will be much longer than that.
If you look at the structure:
Rich (BB code):
SELECT table3.Object, "Diso**** 1" AS Type, table3.[Diso**** 1] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 2" AS Type, table3.[Diso**** 2] AS [Percent]
FROM table3;
UNION
SELECT table3.Object, "Diso**** 3" AS Type, table3.[Diso**** 3] AS [Percent]
FROM table3;
notice how the pattern repeats (I show with different colors).
You would need one section for each of your Countries. So while this query has 3 sections, yours would have 50!
And anytime you added more Countries, you would need to add additional sections (there is that ongoing maintenance I mentioned earlier).
And quite frankly, I am not sure how fast a Union query of 50 different Countries will run. It could be a slow query.
I was very much in the same place you are in right now when I had to create my first database (I was very Excel proficient, but new to Access at the time). I had a similar situation where I had 20 investment options, so at first I created 20 fields. It was an absolute nightmare to work with! Even seemingly simple tasks became cumbersome and difficult. Then someone helped me out and told me I had to re-design the tables to follow the rules of normalization. It was a big undertaking (because I had already created many queries, forms, and reports around it), but once I did, everything fell into place, and I was now able to do everything I wanted without pulling out the rest of my hair.
A key thing to understand for those new to Access and relational databases is that it is very different from Excel. You really cannot approach it with an Excel spreadsheet state-of-mind.
You need to learn about relational databases, and how to design them (especially the Rules of Normalization). If you don't, it will probably cause you nothing but continued aggravation.
Trust me, I have been there.
