Results 1 to 4 of 4
  1. #1
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37

    Lookup Fields

    I'm confused about lookup fields in tables. I've read on this forum that they are bad and should not be used, but why would microsoft create a wizard to create them if they are that bad. I can understand the arguement against them but I don't know how to design my tables without them.



    Let's say I have one table of cars which has different fields. One of the fields is the owner. I have another table called owner that has information on the owners. How do I link these tables so each car entry has one owner? I can use the lookup wizard and have it reference the owners table. Or I can drag the primary key in the owners table into the cars table and create the relationship. Which way is better or, are there other, better ways to link simple tables like this without using lookup fields?

    One step further if I wanted to create a form, would it be best to link it to the car table, or a query that combines the tables?

    Sorry for the long post, just trying to learn something new.

    Mike

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    I understand what you are saying; I am ok with LookUp fields but only when what is inserted is the actual text from the source table; i.e. the 2 letter initials for each state i.e. NY NJ etc. so whenever I use a lookup field - my source table (which I refer to as static lists) are always single column tables.

    The messy thing about LookUps is when the source table has an ID column...and so you think you are entering text - - - and that is what displays - - - but what is actually stored in the table is the ID value. That situation is very messy down the road in making reports/queries and such....but one has to understand that MS is attempting to get newbies into dbs with Access and they figure anyone learned enough to get themselves into trouble can learn their way out of it....

    the alternative are combo/lists - so you can see alot of data (multi fields) and then in the AfterUpdate event you need to put the value you want into a textbox control that is perhaps not visible.

  3. #3
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    I see your point with the ID in the lookup table and not having the actual text in the table.

    But, I don't undertstand your last paragraph. Aren't combos/lists controls that go on a form? The tables still need to be linked in order for the lists to get the correct data, right? How do you do this? Can I just drag the primary key from one table to the "state" field in the other table? This creates the 1 to many relationship between the tables. Is there a better way to create this relationship? Or how do you do it?

    Thanks,
    Mike

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    yes, combo/list boxes go in forms; all user data input should be via forms - never directly into tables. that is a baseline assumption in Access - and actually all databases.

    in forms - when you put a combobox onto the form - the wizard set up will step you thru including a 'where do you want to store this value' option. So in situations where you do not want to deply a loopup field method - you can use this method.

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

Similar Threads

  1. Lookup Tables
    By corinthianw in forum Access
    Replies: 1
    Last Post: 02-17-2010, 01:31 PM
  2. Many To Many lookup
    By todavy in forum Forms
    Replies: 0
    Last Post: 12-15-2009, 09:27 AM
  3. Lookup Form
    By cav0227 in forum Forms
    Replies: 0
    Last Post: 04-16-2007, 09:23 AM
  4. Lookup
    By neon'00 in forum Forms
    Replies: 2
    Last Post: 04-14-2007, 01:19 PM
  5. Lookup Tables
    By JoeBio in forum Database Design
    Replies: 1
    Last Post: 09-01-2006, 07:34 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
  •  
Other Forums: Microsoft Office Forums