2 Replies Latest reply on Feb 27, 2012 4:46 PM by leroalemli

    Lookups, Drop downs vs Queries

    leroalemli

      Hi Iam new to data base design, I have created a database in Access 2010.

      Due to the publishing constraints of Access I am evaluating File Manager Pro 11.

       

      I am having trouble with relationships and lookups that I was able to do in Access.

       

      I have an asset table of equipment which have three identifying codes, Barcode, Serial Number, CO Number.

      When inputting one of these codes in a drop down list I would like the remaining codes filled in.

      The available code may differ from each entry. ie if the barcode is missing off the equipment the serial number would only be known.

       

      I also have a table which requires several different names of people.

      I have created a contact table which has, First Name , Surname.

      When I use the drop down of the First Name I only want the Surnames which match to be available in the Surname drop down list.

      At the moment the lookup places the first occurence in the Surname but when I use the drop down all Surnames are there.

       

      Any help in these areas would be appreciated.

       

      Thanks Leighton.

        • 1. Re: Lookups, Drop downs vs Queries
          LyndsayHowarth

          The first problem probably needs to be solved by a combination of 3 relationships for each of the codes and rather than lookups you should probably use an auto-enter calcluation that relies on these relationships. Also investigate the Evaluate() function so that you can trigger the entry when the data has been entered,

          leroalemli wrote:

           

          I have an asset table of equipment which have three identifying codes, Barcode, Serial Number, CO Number.

          When inputting one of these codes in a drop down list I would like the remaining codes filled in.

          The available code may differ from each entry. ie if the barcode is missing off the equipment the serial number would only be known.

          The second problem requires a relationship based on the FIrst Name then a valuelist for Surname that only shows related values to the FIrst Name related records.

           

          I also have a table which requires several different names of people.

          I have created a contact table which has, First Name , Surname.

          When I use the drop down of the First Name I only want the Surnames which match to be available in the Surname drop down list.

          At the moment the lookup places the first occurence in the Surname but when I use the drop down all Surnames are there.

           


          HTH

           

          - Lyndsay

          • 2. Re: Lookups, Drop downs vs Queries
            leroalemli

            Hi Lyndsay, thanks for your input.

             

            I have decided to look into one area at a time.

             

            I have been able to use a valuelist to choose the surnames only applicable to the selected first name.

             

            However because I want to place 4 different people in the data when I try to set up the same conditions for the second persons data input it changes the first persons surname or the list only gives access to the first persons surname.

             

            In the valuelist I can restrict the list to a Table but I am not able to use a specific field in the table to determine the result.

             

            Do I need to use scripts to create individual conditions to create the valuelist I am after?