Results 1 to 10 of 10

Relational issues

This is a discussion on Relational issues within the Microsoft Access forums, part of the Question Forums category; Im trying to join two tables with a one to many relation,Im trying to join supplier code from the tblSupplier ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    134

    Default

    Im trying to join two tables with a one to many relation,Im trying to join supplier code from the tblSupplier to supplier code on the tblinvoice.
    Whenever or whatever I try Access will not allow me to join these, what is my problem?
    Ive looked for hours but I cannot spot the error, apparently some data is different in one of the tables, it beats me which though.
    http://homepage.ntlworld.com/charlie...Invoices02.mdb

    Would someone put me out of my misery please.

    Thanks
    Charlie

    [ This Message was edited by: Charlie on 2003-02-15 11:00 ]

  2. #2
    New Member
    Join Date
    Feb 2003
    Posts
    32

    Default

    It worked Just fine when I downloaded the file and tried it.
    The data types are the same, both are text fields, only difference I can see is that on tblsupplier, the required property is set to no, it is set to yes on tblinvoice, but it still works on my system. AccessXP running on windowsXP

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    134

    Default

    Try it with enforce referencial integrity checked, silly me forgot to mention that important detail.

    Charlie

  4. #4
    New Member
    Join Date
    Feb 2003
    Posts
    32

    Default

    In the invoice table, record number 4 the supplier code is L312 it should be L321.
    Change that and it will work

    Regards,

    Dave

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    134

    Default

    What a fool I am, do you know of any tools that can analyse and find such faults with relationships etc in Access?
    One tool like that would come in very handy about now.

    Thanks

    Charlie

  6. #6
    New Member
    Join Date
    Feb 2003
    Posts
    32

    Default

    No, but if you set out the database before entering data, it would not let yopu enter anything that did not meet referential integrity. Also, set up correctly with subforms etc, the linked fields could be made to update automatically when you enter a new record. ie. if you made a data entry form based on your supplier table, then added a subform for creating an invoice, the subform would be linked on the supplier code field, this field in the new invoice record would be filled automatically.

    Hope this helps.

    Dave

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    134

    Default

    I would really like to know how to set up a database to not allow me to enter data not matching referential integerity, unfortunately I do not know how to do that. If anyone would point me to the settings needed for this I would be grateful.

    Charlie

  8. #8
    New Member
    Join Date
    Feb 2003
    Posts
    32

    Default

    Expect an email shortly

    Dave

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    134

    Default

    Appreciate it mate, that will save me a lot of time.

    Charlie

  10. #10
    New Member
    Join Date
    Feb 2003
    Posts
    32

    Default

    No Problem

    Dave

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com