Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables (Win XP Access 2003)

    In the attached data base, there is a form, frmRepuestosInvUpdate based on a query qryRepuestosInvUpdate. This query uses two tables, tblRepuestos and tblUnidadDeMedida. The form has a command button called Open Form, which calls the form GoToRecordDialog. You can select a form from the list box, and click the Show Record button to return to frmRepuestosInvUpdate with the selected record shown.

    Everything works fine until I add another table to the query. When I add tblRepuestosInv to the query, the Show Record no longer functions. I need a couple of fields from the new table.

    Why doesn

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

    Re: Linking tables (Win XP Access 2003)

    It appears to me to be an issue with the new table not having records that match the three records that are normally returned. It might work if you made the join to tblRepustosInv a right join - in other words, return all of the records from tblRepustos and any in tblRepustosInv that match that record. Let us know if that does or doesn't solve your problem.
    Wendell

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

    Re: Linking tables (Win XP Access 2003)

    Add tblRepuestosInv to the query, and link it to tblRepuestos on IDRepuestos.
    Double click the join line, and select the option to return ALL records from tblRepuestos and only related records from tblRepuestosInv.
    Add UnidadMedida from tblUnidadDeMedida, and Fecha, SaldoInicial and ValorInicial from tblRepuestosInv to the query grid.
    'Open Form' and ' Show Record' then work ok for me.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linking tables (Win XP Access 2003)

    The problem is that most of the entries in tblRepuestos do not have a matching entry in tblRepuestosInv. When you add this table to the query the query only returns records that match across all three tables.

    In the case of the data suppied with your sample, the query returns only 1 record instead of three.

    Should each record in tblRepuestos have exactly one record in tblRepuestosInv, or many perhaps?

    You can double click the join line (between tblRepuestos and tblRepuestosInv) in the query as ask the query to return all records in tblRepuestos.
    then the from will show null values where data is missing in tblRepuestosInv.
    Regards
    John



Posting Permissions

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