Results 1 to 9 of 9
  1. #1
    Tara1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4

    Using 'aliases' table as a lookup table to find the primary key 4 'main name' table

    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

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,601
    Based on your post I have attached a jpg of a possible model.
    I hope it's useful to you.
    I'm not really clear on what exactly you want to do. But this model may be a start.
    Attached Thumbnails Attached Thumbnails ClubsAndAliases.jpg  

  3. #3
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just wondering, why not have the Main Name in the alias table also.

    tblClubs
    ClubID - PK
    ClubMainName

    tblaka_Clubs
    ClubAliasID
    Club_ID - FK
    ClubAliasName
    WhetherAlias - Yes / No

    tblClubs
    1 TestClub1
    2 TestClub2

    tblaka_Clubs
    1001 1 TestClub1 No
    1002 1 TestClub1Alias1 Yes
    1003 1 TestClub1Alias2 Yes
    1004 2 TestClub2 No
    1005 2 TestClub2Alias1 Yes
    1006 2 TestClub2Alias2 Yes

    Thanks

  4. #4
    Tara1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Yeah...I was thinking that and not sure why I hadn't thought of it earlier...I think that might be what I'll do. Thank you for your help.

    Quote Originally Posted by recyan View Post
    Just wondering, why not have the Main Name in the alias table also.

    tblClubs
    ClubID - PK
    ClubMainName

    tblaka_Clubs
    ClubAliasID
    Club_ID - FK
    ClubAliasName
    WhetherAlias - Yes / No

    tblClubs
    1 TestClub1
    2 TestClub2

    tblaka_Clubs
    1001 1 TestClub1 No
    1002 1 TestClub1Alias1 Yes
    1003 1 TestClub1Alias2 Yes
    1004 2 TestClub2 No
    1005 2 TestClub2Alias1 Yes
    1006 2 TestClub2Alias2 Yes

    Thanks

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Well it's kind of cheating.
    Be careful. Just take a look at the implications at your end, before doing it that way ( even though at first sight, it appears to save us some work like the union query, etc).

    Thanks

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,601

  7. #7
    Tara1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4

    I have implemented this 'solution', there are a few glitches I could do with help wit

    Hello everyone,I have changed things a bit, not really how suggested but similar I think. Now I have a clubs table with an ID number and some of the info about the club, and I don't store the main name here. Then I have a club_names table with 4 columns, the Foreign key for the clubs_table, a club name, and a true/false column for whether it is the main name or not (and a notes column).Then my form for editing data is based on a query: SELECT Club_names.names, Club_names.Main_name, Clubs.club_ID, Clubs.Ed_Exactness, Clubs.Earliest_Date, Clubs.Ld_Exactness, Clubs.Latest_Date, Clubs.Lookup_Region, Clubs.Type, Clubs.Bibliography, Clubs.NotesFROM Clubs INNER JOIN Club_names ON Clubs.club_ID = Club_names.club_FKWHERE (((Club_names.Main_name)=True));In the resultant form I have a subform which is the club_names table, where you can add names. I think this might have been a really bad idea...is it all circular or something!? It works fine for editing existing records, and when adding a new record, it works ok if I edit something other than the subform first. But these are the issues...2 problems: 1. How do I (Can I?) make it so that in the club_names table, the combination of the club_FK and the Main_name fields have to be unique, i.e. only one clubname for every club can be the main name.2. When making a new record on the form, how do I (Can I?) get it to create a new record in the clubs_table and assign the PK by autointeger before anything has changed on the form...otherwise the subform doesn't work, as the id that makes up the club_FK in the subform seems to be only assigned once a control in the main form is changed. Below are images of my clubs table, my club_names table and my form. Thanks for any help! Sorry for the massive extra attachment I managed to include, I can't work out how to delete it... Click image for larger version. 

Name:	clubs table.jpg 
Views:	6 
Size:	32.1 KB 
ID:	6835Click image for larger version. 

Name:	club names table.jpg 
Views:	4 
Size:	26.0 KB 
ID:	6836Click image for larger version. 

Name:	form.jpg 
Views:	4 
Size:	43.4 KB 
ID:	6837
    Quote Originally Posted by orange View Post
    Can you give us 5 or 10 sample records?
    Attached Thumbnails Attached Thumbnails clubs table.jpg  

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,601
    I think if you could give a few sample records for your tables -even dummy records - you wll get more meaningful responses.
    As I see it, you have not "nailed down exactly what you're trying to do", but have invented a few ways of using tables and forms for HOW to do it.
    Get the What solved, then work on HOW.

    Just my $.02

  9. #9
    Tara1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Sorry I thought the images would be sufficient as sources for dummy records -totally new to this. Thanks for the tip. I have to be away from my computer for a bit but will think about your advice. I'm learning a lot in trying to figure all this out!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-12-2012, 02:58 PM
  2. Linking tables to show primary key from main table
    By Mark_435 in forum Database Design
    Replies: 5
    Last Post: 01-12-2012, 09:20 PM
  3. Finding Table Aliases
    By hfile in forum Access
    Replies: 2
    Last Post: 12-26-2011, 02:38 PM
  4. Replies: 2
    Last Post: 08-01-2011, 11:35 PM
  5. Using lookup data for table 1 mutible times in table 2
    By mbjazz in forum Database Design
    Replies: 5
    Last Post: 04-26-2011, 01:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums