Results 1 to 3 of 3

Thread: unequal joins

  1. #1
    Join Date
    Jul 2004
    Lorain, OH

    Unanswered: unequal joins


    I am trying to add a table of one to one join information that will add additional fields long after the original table has been used to add data. I tried to use the join type that says "use all the data from the first table and only the data from the second (new table) where the join key is equal. There is a foreign key in the join field in the new table that maps to the primary key of the original table. The relationship is forcing one-to-many, which should not create a problem, although there will only be a one-to-one relationship as the data fields are all entered in the same row of the subform i have created.

    the message I get when i hit the fields from the new table in the row in the subform is "can't add record; join key of table (newtable) not in recordset." how do i update this field of the join key in the newtable so this entry works?


    vincent deluca

  2. #2
    Join Date
    Jul 2004
    Southampton, UK
    Before we sort out the form setup, how have you created your tables and relationships ?

    Tables and relationships should be defined before you create your Access select queries. Relationships between tables are normally defined in the relationships window (click on the relationships icon - the one that looks like three table with two lines connecting them). Setting up relationships like this is fundamental to relational database theory. Are you doing this ? Joining tables in selected queries is not the same. Sorry if I'm making the wrong assumptions here.

    To make your one-to-one relationship, both tables must have a primary key defined (because for a one-to-one you must relate using the primary keys). Then, using the relationship window, when you drag one primary key onto the other, the relationships box will open. Click the "Enforce Referential Integrity" box and click create. A one-to-one relationship will be created signified by the 1's at either end of the line linking the tables.

    Once you have the relationship defined, you can create a query that references both tables. Assuming your are using the Access query builder, add both tables to the query. You will notice that your one-to-one relationship is already recognised and a join assumed. As you mentioned, you should change this join to be all records of one and matching from the other (double clicking on it). Test you query to ensure it's showing what you expect.

    Since you are joining the tables in a query and it's one-to-one, I don't see the need for a subform (anyone care to comment?). You can simply add all the required fields from your query to the form. The records that are missing in the second table will show as blank which can be entered as required.


  3. #3
    Join Date
    Sep 2003
    ... And as a follow on: Be aware of which table you relate to which ... The order is important. With a 1 to 1 relation (or any other) the "relating" table has to have it's record(s) written before the "related" table's record(s) are written ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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