Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationship ? (A2k)

    I have a table with many thousands of records, which needs to be kept intact and read only.
    I need to add a field, ie a Yes/No selection or an extra text field !
    The only way I can think of is to create an extra table to hold the extra field but create a relationship binding the data together:

    tblEST
    EST_NO
    SUP_NO
    CLI
    VEH

    tblYesNo
    EST_NO
    SUP_NO
    Req = Yes/No

    I've made a couple of attempts at this and not getting anywhere, is this possible ?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Relationship ? (A2k)

    Another possibility would be to do a make-table query and create a temporary table with all of the existing fields that you can then add the extra field to that new table once you've run the query. The approach you've been attempting would also use a make-table query, but only create the necessary linking primary key. The bigger challenge it seems is how to set the value of the extra field. Can you calculate it from existing data in the existing table, or do you have to do some sort of manual inspection to set it?
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationship ? (A2k)

    Hi Dave,

    Once you have created the new table, you must create an append query to add the primary key of all existing records in tblEst to tblYesNo. From the structure of the tables I would guess that the combination of EST_NO and SUP_NO is the primary key, so:
    - Create a query based on tblEst.
    - Add EST_NO and SUP_NO to the query grid.
    - Select Query | Append Query.
    - Specify tblYesNo as target query.
    - Select Query | Run.

    Note: the fields in tblYesNo should not be AutoNumber fields even if the related fields in tblEst are.

    If it is possible to add records to tblEst, you will have to use code to add a related record in tblYesNo. Similar for deleting records.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship ? (A2k)

    Hmmmm

    I thought it would be simpler.
    I feel appending or making tables would seriously affect the speed.
    The data to add into tblYesNo would only be at form level, so perhaps I could link via subform ? Master/Child ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationship ? (A2k)

    I'm not sure I understand what you want. Is tblEst a "fixed" existing table, or is it being imported from another application all the time, or ...?

    If it is a more or less fixed table, you'd have to run the append query only once, to create records in tblYesNo corresponding to the existing records. After that, you'd only have to take care of new/deleted records.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship ? (A2k)

    Hans

    Don't I always set a challenge ?

    tblEst is a linked ODBC via DSN "MM"

    I use the data from that table as read only because the residing Dataflex database is filled in by the users.
    I only extract the data in a continuous form which has conditional formatting applied changing colours at different levels of criteria.
    I won't allow any editing of the data so as not to disturb the original data.

    So I want then to create an Access table which is linked via EST_NO & SUP_NO the two main fields, to add additional data to.
    By this, I can then extract information via a query.

    Lets say a table of parts contains 10 items, I want to then add a Yes/No field so I can send the true data via SendObject.

    Now you know why the two posts are together.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationship ? (A2k)

    Since the table structure for tblYesno is very simple, it shouldn't be problem to clear all records and repopulate it. I have used similar methods without any problems.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationship ? (A2k)

    You don't mention the primary keys in your tables, so I will give you a fresh example. PK's are vital to this technique because there is a one-to-one relationship between the two tables.

    tblEmployee
    EmployeeID autonumber, PK
    EmployeeName text

    tblEmployeeSelected
    EmployeeIDfk longInteger, PK
    EmpSelected yes/no


    qryEmp:

    SELECT tblEmployee.*, tblEmployeeSelected.selected
    FROM tblEmployee LEFT JOIN tblEmployeeSelected ON tblEmployee.EmployeeID = tblEmployeeSelected.EmployeeIDfk;


    When your tblEmployee is populated, and the tblEmployeeSelected is empty, this query will display a "grayed-out" checkbox for each employee's Selected field. However, the query will also allow you to check any of the boxes. Automagically, it will create a new record in tblEmployeeSelected for that employee. The LEFT JOIN is vital in this query so that all the records in the tblEmployee are displayed.

    Hopefully, you can adapt that concept to your tables.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •