How to make Excel a potent relational data source for manual entry?
I am using Excel to store data kind of as a relational database for data that will be entered manually (see my other question here : https://stackoverflow.com/questions/9416263/possible-solutions-for-simple-data-persistence-and-manual-entry)
However my biggest problem so far is making the RELATIONSHIPS easier (in a manual entry perspective). As of now, I reference other tables by using the incremental ID, but when you look at such a foreign key, the user has no idea what the Object #42 is, the user has to go and seek the #42 in the other table in order to know what it is.
Let me give an example, let's say we only have two tables and 1 one-to-many relationship between Cities and Countries.
The problem is I do the relationships using the articial ID key, but it has no meaning and when adding cities, I have to check the ID for the referenced country in the other tab.
Is there any way I could select the country using dynamic combo-boxes that uses the data from the other table? If so, can it show the country name, but enter the ID (because that's how the relations are made).
Do I HAVE to change the primary keys to be the country name and get rid of the ID fields where necessary? Because some tables are the result of many-to-many relationships and are defined by multiple foreign keys, so they have no unique value except the combined foreign keys OR an artificial ID.
I hope I was clear enough, and I must admit that I am an Excel newbie.
Answer
As some comments pointed it out, I am trying to use the wrong tools for my needs. I should be using Access instead of Excel, especially since I have resolved my ODBC drivers problem