Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Relationships (Access 2003 SP2)

    Would someone help me please.
    I have inherited this database and it has problems.
    I can input data via the form but cannot show the records via the query.

    My relationships are probably wrong, also a table may be incorrect.
    Do i need to create a new table to hold PO ID, Contact ID?
    Do i need to take VendorID and ContactID out of the PO table?

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

    Re: Relationships (Access 2003 SP2)

    The query refers to a non-existent field [Purchase Order Table].AmdtNo, and refers to the field V/Name incorrectly. Remove the latter and add it anew.

    In the query, Purchase Order Table and Vendor Contact List are joined on CONTACT ID vs ID, but the CONTACT ID field is empty in all records. Hence the query doesn't return anything.

    I don't understand the purpose of it all, so I cannot answer your questions.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships (Access 2003 SP2)

    The query is meant to return purchase order records whether there are connected contacts or not.

    Users may input these fields later, so i need the query to return purchase order records.

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

    Re: Relationships (Access 2003 SP2)

    Double click both joins and select option 3.

    You'll have to do something about the non-existing fields.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships (Access 2003 SP2)

    Sorry about the non-existant fields, i have deleted one and reinstated the other correctly.

    Thank you for that and your time. As you know it now works.

    I will change the query in the real database and try that.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships (Access 2003 SP2)

    It works well in the real database, thank you Hans.

    A question though, is that a good relational design for PO's that point to a Vendor and also a Contact for that vendor.

    A problem with it of course, is that when a user changes the Vendor on the form and not the Contact then the contact field is incorrect. I have put code in the BeforeUpdate of the Form to check if the Contact relates to the Vendor.

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

    Re: Relationships (Access 2003 SP2)

    I don't think you should have a Vendor ID field in the Purchase Order table. The Contact ID field determines the Vendor ID. If you wish, you can display the vendor in the dropdown list of the contact combo box on the form. Or you can place an unbound combo box on the form to select a vendor. The After Update event of this combo box would update the row source of the contact combo box.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships (Access 2003 SP2)

    Thanks Hans for your input.

Posting Permissions

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