locked
Access 2003 Query problem RRS feed

  • Question

  • I have a look up field in a database that contains names in a last name, first name format that inserts an employee name when selected from the look up during data entry.  I need a query that will pull records for a specific employee from this look up field.  I have tried may different expressions but nothing is working. I either get an error message or the expression returns blank a blank query with no data.  What am i doing wrong?  Can someone help me with an expression that will do this?
    Friday, March 4, 2011 1:11 AM

Answers

  • Replace that lookup in the table with a new table for employee names.

    Use a primary key - I suggest an autonumber pk,  a foreign key which is the primary key from the table where you currently have the lookup, a field for last name and a field for first name.

    Creating the query you need will now be easy.


    Jeanette Cunningham
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:06 AM
    Friday, March 4, 2011 1:15 AM
  • Hi Alonzo
     
    That's the bad side of the Lookup fields and maybe the main reason as it's mentioned as sin here: http://access.mvps.org/tencommandments.htm
     
    If you want to query for an employee you have to use a join between your table and the employees
     
    SELECT YourTable.*
      FROM YourTable INNER JOIN
           Employees ON YourTable.EmpNo = Employees.EmpNo
     WHERE Employees.LastName LIKE [search for] & '*'
     
    The easiest way to do it is to create a new query, add your table and the table employees to the query (and join these if not done automatically over the employees number by drag&drop the primary key of employees to the corresponding field in your table). Then add the fields you want to see in your resultset and add the filter for the employees columns.
     
    Henry
    I have a look up field in a database that contains names in a last name, first name format that inserts an employee name when selected from the look up during data entry.  I need a query that will pull records for a specific employee from this look up field.  I have tried may different expressions but nothing is working. I either get an error message or the expression returns blank a blank query with no data.  What am i doing wrong?  Can someone help me with an expression that will do this?
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:06 AM
    Friday, March 4, 2011 7:20 AM

All replies

  • Replace that lookup in the table with a new table for employee names.

    Use a primary key - I suggest an autonumber pk,  a foreign key which is the primary key from the table where you currently have the lookup, a field for last name and a field for first name.

    Creating the query you need will now be easy.


    Jeanette Cunningham
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:06 AM
    Friday, March 4, 2011 1:15 AM
  • Hi Alonzo
     
    That's the bad side of the Lookup fields and maybe the main reason as it's mentioned as sin here: http://access.mvps.org/tencommandments.htm
     
    If you want to query for an employee you have to use a join between your table and the employees
     
    SELECT YourTable.*
      FROM YourTable INNER JOIN
           Employees ON YourTable.EmpNo = Employees.EmpNo
     WHERE Employees.LastName LIKE [search for] & '*'
     
    The easiest way to do it is to create a new query, add your table and the table employees to the query (and join these if not done automatically over the employees number by drag&drop the primary key of employees to the corresponding field in your table). Then add the fields you want to see in your resultset and add the filter for the employees columns.
     
    Henry
    I have a look up field in a database that contains names in a last name, first name format that inserts an employee name when selected from the look up during data entry.  I need a query that will pull records for a specific employee from this look up field.  I have tried may different expressions but nothing is working. I either get an error message or the expression returns blank a blank query with no data.  What am i doing wrong?  Can someone help me with an expression that will do this?
    • Marked as answer by Bruce Song Monday, March 21, 2011 6:06 AM
    Friday, March 4, 2011 7:20 AM
  • Thanks Jeanette. Was hoping for a solution that didn't require messing with the table and relationships.  Will try what you recommend.
    Monday, March 7, 2011 10:07 PM
  • Hello Henry,

    Will try what you recommended. Thanks.

    Alonzo

    Monday, March 7, 2011 10:08 PM
  • Most access developers avoid using lookups in a table for exactly the same reasons that we are suggesting you create a new table to replace the lookup.


    Jeanette Cunningham
    Monday, March 7, 2011 10:52 PM
  • Many use a combo box on a data entry form to select from an existing table instead of a lookup field within the table where the data is being stored.  In those cases they actually store a primary key rather that the actual name.

    So when the data is extracted for display it needs the list table joined so that the text of the names are shown instead of the key.

    Monday, March 7, 2011 11:42 PM