Data from a query into a table. RRS feed

  • Question

  • I am not real familiar with MSAccess, pretty good with VB .NET
    Creating an Access app using the FCC public data for Amateur Radio.
    I want to add a field to one table from the data in another field.

    I created a new field in table 'Hams' named 'OpClass', tried to create a lookup using query 'GetOpClass' - I get this message
    "No valid fields can be found in 'GetOpClass'. You may have selected a query that uses the table you're adding the lookup column to. Please select a new source."

    GetOpClass query (created by the Query Design):
    SELECT HamDat.Operator_Class
    FROM HamDat INNER JOIN Hams ON HamDat.UniqueID = Hams.UniqueID;

    Relevant Fields:
    Table 'Hams' - UniqueID, Number, Primary Key
    Table 'Hams' - OpClass, Text,255 Max - that's the field I added

    Table 'HamDat' - UniqueID, Number, Primary Key
    Table 'HamDat' - Operator_Class, Text, 255 Max - Typically 1 or 2 characters, that's the data I need

    All Fields:
    Table 'Hams' fields - UniqueID,Call Sign,Entity Type,First Name,MI,Last Name,Suffix,Street Address,City,State,Zip Code,PO Box,FCC Registration Number,Applicant Type Code,Status Code
    Table 'HamDat' fields - UniqueID,ULS_File_Number,EBF_Number,Call_Sign,Operator_Class,Group_Code,Region_Code,Trustee_Call_Sign,Trustee_Indicator,Physician_Certification,VE_Signature,Systematic_Call_Sign_Change,Vanity_Call_Sign_Change,Vanity_Relationship,Previous_Call_Sign,Previous_Operator_Class,Trustee_Name

    What am I doing wrong and how can I get the correct Operator_Class from 'HamDat' into 'Hams' ? Thanks in advance.

    Saturday, June 25, 2016 4:42 PM


  • ..................how can I get the correct Operator_Class from 'HamDat' into 'Hams' ?

    You don't need to, nor should you, as it would introduce redundancy into the table and leave it open to the risk of update anomalies.  Return the desired columns from both tables as a query's result table, e.g.

    SELECT HamDat.Operator_Class, Hams.*
    FROM HamDat INNER JOIN Hams ON HamDat.UniqueID = Hams.UniqueID;

    which will return all columns from hams, along with the Operator_Class column from HamDat.

    However, as you appear to have a one-to-one relationship type between the tables, you don't need two tables.  All the columns from both tables can be included in a single table.  One-to-one relationship types are relatively rare in relational databases.  They are used for modelling a type/subtype, but that is not the case here as the columns in both tables are attributes of a single entity type, not of separate entity types where one is subtype of the other, e.g. a type Employees might have subtypes SalesPersonnel and ProductionPersonnel.

    Ken Sheridan, Stafford, England

    Saturday, June 25, 2016 5:20 PM