Hello,
I've been thrown in at the deep end and I am developing my first database in access (2010) (I've not designed a db before). The db is for a history project which attempts to record membership of eighteenth century clubs. One issue I am anticipating is that some of these clubs had different names, even though they are basically the same entity (for instance, after royal charter they now have the name 'royal'). I have a 'clubs' table with a 'main name' for the club, an autoincrement primary key, dates of foundation etc. I have a second 'aka_clubs' which links 'also known as' or 'aliases' to the main club by using a foreign key link to the 'main name' table. So far, I hope, so good.
This is where I start to get confused and unsure that I have taken the right route.
I wanted a place where there was a list of all the names of clubs, whether their main name or their aliases, linked by foreign key to the id of the club in the 'clubs table'.
So, if on the clubs table there was the "Test club", in the aka_clubs table this club would be identified as having two aliases "experiment club" and "trial club", I wanted to create a third place where Test club, experiment club, and trial club were all linked to the same id number on the clubs table. The idea was that if you came across data about "trial club" and wanted to enter it, you wouldn't have to know that "trial club" was also known as "Test club", you would lookup the name "trial club" and the correct id from the clubs table would be assigned to any data you entered (for instance on a transactions table recording a membership record).
I created this third place with a union query "qry_all_names" which took records from the clubs and the aka clubs table to create one long list of names, associated with ids from the clubs table.
This is where I got particularly stuck. When entering a membership record on the "membership_records" table (i'd like to create a form to do this, ultimately), I am unsure of the implications of using a lookup field linked to the union query. I want to use the lookup field so that you just look for the name in qry_all_names but actually create a link to the clubs table. I'm confused about whether that works and the implications of it.
I'd appreciate the advice of someone more experienced about this methodology / design, or alternative ways to achieve what I am trying to do. Detailed instructions would be much appreciated!
Thank you.
I